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
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
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