The intent here is simple. On sheet1, Column A1-A100, each cell is allowed a certain entry as determined by a list in B1-B6. Very simple.
Here is the VBA code that works.
sub test()
dim rlist as string
rList = "$B$1:$B$6"
With Range("A1:A100")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
End Sub
If I add the following variable
secstr = "$B$6" and substitute the following for rlist:
rlist=Chr(34) & "$B$1:" & secstr & Chr(34), it results in rlist being exactly "$B$1:$B$6" as before, but the procedure fails, the dreaded Run time error 1004 that hangs on the 2 lines starting with .ADD.
Here is the modified code.
sub test()
dim rlist as string
dim secstr as string
secstr="$B$6"
rlist=Chr(34) & "$B$1:" & secstr & Chr(34)
With Range("A1:A100")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
End Sub
Any ideas?
Bookmarks