Data Validation - List - Setting Range from a macro

[dhstein]

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

 

[joel]

You can't put the equal sign into the worksheet, but can add it into the
code. I fyou want the dollar signs added you can use Address with
RowAbsolute and ColumnAbsolute set to true.

for example

validationRange = Range("K1:K5").Address _
(RowAbsolute:=true,ColumnAbsolute:=true)
With Range("H1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & validationRange
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[dhstein]

Joel

Great job! That worked - thanks.

You can't put the equal sign into the worksheet, but can add it into the
code. I fyou want the dollar signs added you can use Address with
RowAbsolute and ColumnAbsolute set to true.

for example

validationRange = Range("K1:K5").Address _
(RowAbsolute:=true,ColumnAbsolute:=true)
With Range("H1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & validationRange
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[Jacob Skaria]

Is there a error you are encountering with..and to have the lookup populated
do you really need the quote marks around..Can you try the below ....its a
different range..as KB2:KB118 is not a valid range in Excel 2003....

Sub Test333()
Dim strRange As String
strRange = "=$A$1:$A$8"
Range("B2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strRange
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub

If this post helps click Yes

 

[OM]

Maybe I'm reading the problem wrong, but why don't you create a
dynamic name for the range, and use that for the list? If you type
=OFFSET($KB$1,0,0,COUNTA($KB:$KB),1)
as a new name, and call it (for instance) DDList, then if you use
DDList as the source for your drop down list, it will automatically
change as new items are added to the bottom of KB. Done this many
times, and seems to work well for me (if, as I say, I'm reading the
problem right....)

OM

 

[dhstein]

OM,

Thanks for your response. As I worked through this problem, I was
thinking there must be a better way. Although I have it working now with
Joel's help, I will definitely try your method. Thanks.

David