Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation - List - Setting Range from a macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation - List - Setting Range from a macro
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation - List - Setting Range from a macro
Joel
Great job! That worked - thanks. "joel" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation - List - Setting Range from a macro
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 --------------- Jacob Skaria "dhstein" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation - List - Setting Range from a macro
"dhstein" wrote: 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" 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation - List - Setting Range from a macro
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 "OM" wrote: "dhstein" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation List Macro | Excel Discussion (Misc queries) | |||
Can the data validation list range of one cell be driven by theco | Excel Discussion (Misc queries) | |||
Data Validation "List" - Setting length of list shown | Excel Discussion (Misc queries) | |||
Setting up a validation of data listbox to provide the unique items within a range | Excel Worksheet Functions | |||
data validation list selected from a range | Excel Discussion (Misc queries) |