I have a Cell that gets a drop down list from a range. The range of data
will change, so I'm trying to dynamically set up the drop down from a macro.
I will use cell KB1 (excel 2007) to provide the range that the drop down
will use. As new vendors are added KB1 will be modified. At this point in
the testing, I'm just trying to get the value in KB1 to be used in the macro.
The macro is shown below. I commented out the original code and also my
failed attempts to get this to work. I believe I need in cell KB1 the exact
string:

"=$KB$2:$KB$118"

with the quotes as well. So my immediate question is how can I put the
string shown above into cell KB1 with the quotes as needed. Just coding
="$KB$2:$KB$118" does not give me the quote marks that I believe I need.
Thanks for any advice on this.




Sub test333()
'
' test333 Macro
'

'
Dim VendorRange As String
'VendorRange = Range("KB1").Value
VendorRange = "=$KB$2:$KB$118"
Range("C2").Select
With Selection.Validation
.Delete
'.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
'xlBetween, Formula1:="=$KB$2:$KB$118"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=VendorRange
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub