PDA

View Full Version : Scroll List help



Mykasd
03-05-2008, 01:13 PM
I am trying to populate a scroll list with variable that can change in number every time i run the program. That is, sometimes I want to populate the scroll list with 5 variables, sometimes with 10. Right now this is the code I have and is set up for 29 variables. The problem is, there are blanks in the scroll list if there are less than 29 variables even though the IgnoreBlank is set to True:
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BA$2:$BA$30"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

But I was hoping to modify it so that it can adapt to the Integer DMCount sort of like this:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BA$2:$BA$"DMCount""
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Where DMCount can be any number. Please help. Thanks!

Bob Phillips
03-05-2008, 02:10 PM
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BA$2:$BA$" & DMCount
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Mykasd
03-06-2008, 08:59 AM
wow, that was easy. thank you xld, thats awesome