CODEWITHSUNDEEP
vbaexcel
S.Katiyar
S.Katiyar

Reputation: 33

VBA Code for drop-down list with dynamic range

I am trying to write a macro for multiple drop-downs in "n" cells (let's say 100) in a column. The ranges (drop-down values) for these drop-downs have to be picked from a table with the same number of rows (100 in our case).

I am unable to run the for loop for the formula part (highlighted below). I want the macro to pick D2:H2 range for i=2, D3:H3 for i=3, and so on. How do I do it? Is there any alternative to this?

Sub S_Dropdown3()
    
    Dim wks As Worksheet: Set wks = Sheets("Sheet1")
    
    wks.Select
    
    Dim i As Integer
    
    For i = 2 To 101
        With Range("B" & i).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, **Formula1:="=Sheet2!D2:H2"**
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Next i
    
End Sub

Upvotes: 3

Views: 12261

Answers (1)

Ralph
Ralph

Reputation: 9444

The following code should work:

Option Explicit

Sub S_Dropdown3()

Dim wks As Worksheet
Dim i As Integer

Set wks = ThisWorkbook.Worksheets("Sheet1")
wks.Activate

For i = 2 To 101
    With wks.Range("B" & i).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=Sheet2!D" & i & ":H" & i
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Next i

End Sub

Implemented changes:

  1. Code formatting / indentation
  2. Implementing full qualification to ensure that Sheet1 refers to Sheet1 in the workbook from which the macro is run (in case that more than one Excel file is open).
  3. Sheets cannot be .Selected only ranges get selected. Sheets can only be .Activated. Earlier versions of Excel don't mind. Never versions of Excel will throw an error with that line.
  4. Fully qualifying .Range("B" & i).
  5. Finally, making the formula modular as requested in the initial post.

Upvotes: 3

Related Questions