Create dynamic validation list

Question

Amongst the links I found, this and this appear to be similar to my question. Trying to implement the answers turned out futile!

I get an application defined error with the following code.

I'm looking to create a dynamic range with this code:

Dim strFormula1 As String
Dim d As Range
strFormula1 = "control!:$B$3:" & Sheet5.Range("b2").End(xlDown).Address()
Set d = Sheet1.Range("K3:K20000")
With d.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Formula1:="='" & strFormula1 & "'"
End With

user6585761 · Accepted Answer · 2019-08-06 16:40:35Z

0

First, the string being assigned to strFormula1 is incorrect. It includes an extra colon between the sheet name and the range reference.

Secondly, the Formula1 argument is also incorrect. In addition to having the colon in the wrong place, it has the apostrophe (') in the wrong pace as well.

Try the following instead...

Dim strFormula1 As String
Dim d As Range

With Sheet5
    strFormula1 = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Address(External:=True)
End With

Set d = Sheet1.Range("K3:K20000")

With d.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=" & strFormula1
End With

answered Aug 6, 2019 at 16:40

user6585761

Sign up to request clarification or add additional context in comments.

1 Comment

marv Over a year ago

thanks a ton! that works now. Also, thank you for providing an explanation as to where I went wrong.

Collectives™ on Stack Overflow

Create dynamic validation list

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related