Drop-down Lists with Conditional Data

Question

i have three ranges of cells (from another sheet in my workbook) that store the values that i want to appear on the drop-down (Form Control) list. i need the macro code to reset a cell range, or a named range to define the options in a drop-down list based on other cell value. . i've tried the following code with no success so far:

Sub DropDown11_Change()

If Range("A1") = 1 Then
(input range from sheet1 a1:a50)

ElseIf Range("A1") = 2 Then
(input range from sheet2 a1:a50)

ElseIf Range("A1") = 3 Then
(input range from sheet3 a1:a50)

End If
End Sub

Any suggestions?

Thanks

Answer 1

This should help you out, place either of these in a standard Module (e.g., Module1).

Sub DropDown1_Change()

    Dim ddFillRange As String

    If Sheet1.Range("A1") = 1 Then
        ddFillRange = "Sheet1!A1:A50"
    ElseIf Sheet1.Range("A1") = 2 Then
        ddFillRange = "Sheet2!A1:A50"
    ElseIf Sheet1.Range("A1") = 3 Then
        ddFillRange = "Sheet3!A1:A50"
    End If

    Sheet1.Shapes("Drop Down 1").ControlFormat.ListFillRange = ddFillRange

End Sub

If you want to use named ranges, you can use:

Sub DropDown1_Change()

    Dim ddFillRange As String

    If Sheet1.Range("A1") = 1 Then
        ddFillRange = Range("NamedRange1").Name
    ElseIf Sheet1.Range("A1") = 2 Then
        ddFillRange = Range("NamedRange2").Name
    ElseIf Sheet1.Range("A1") = 3 Then
        ddFillRange = Range("NamedRange3").Name
    End If

    Sheet1.Shapes("Drop Down 1").ControlFormat.ListFillRange = ddFillRange

End Sub

Then, to have these fire upon cell A1 changing, you will need to place the following in Sheet1's module (when you right+click Sheet1 in the VBA Editor Window, select view code...see picture below):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then DropDown1_Change
End Sub

Answer 2

I have done similar things utilizing named ranges. Here is an example that should help as well.

Sub DropDown11_Change()

Dim dropdown As String

If Range("A1") = 1 Then
    '(input range from sheet1 a1:a50)
    dropdown = "=Sheet1!R1C1:R50C1"
ElseIf Range("A1") = 2 Then
    '(input range from sheet2 a1:a50)
    dropdown = "=Sheet2!R1C1:R50C1"
ElseIf Range("A1") = 3 Then
    '(input range from sheet3 a1:a50)
    dropdown = "=Sheet3!R1C1:R50C1"

End If

ActiveWorkbook.Names.Add Name:="DynamicList", RefersToR1C1:=dropdown

End Sub