Edit or export drop down lists in Excel

Question

Working on an existing spreadsheet which contains two dropdown lists. Is it possible to export or edit data from these drop lists?

Answer 1

In a Data Validation list, you can have these 3 scenarios.

A) A formula which refers to a range in the same sheet

B) A List which is directly typed using commas

C) A formula which refers to a named range from same/another sheet

Now we need to cater to all the three scenarios in case we want to retrieve that list.

To access the list of a Data Validation Cell, you have to use the Rng.Validation.Formula1

See this code.

Sub Sample()
    Dim ws As Worksheet
    Dim dvRng As Range, rng As Range
    Dim strList As String
    Dim MyAr() As String

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dvRng = ws.Range("A1") ' Case A
    'Set dvRng = ws.Range("C1") ' Case B
    'Set dvRng = ws.Range("E1") ' Case C

    '~~> Get the formula in the data validation
    strList = dvRng.Validation.Formula1

    '~~> Check if it has an = sign for Case A and Case C
    If InStr(1, strList, "=") Then
        strList = Replace(strList, "=", "")
        Set rng = Range(strList)
        rng.Copy Sheet2.Range("A1")
    Else
    '~~> Case B
        If InStr(1, strList, ",") Then
            MyAr = Split(strList, ",")
            Sheet2.Range("A1:A" & UBound(MyAr) + 1).Resize.Value = Application.Transpose(MyAr)
        Else
            Sheet2.Range("A1").Value = strList
        End If
    End If
End Sub

I have commented the code so you shouldn't face any problems. Still if you do then simply ask :)