PDA

View Full Version : Drop down validation using array



shankar
05-03-2009, 05:08 AM
If I have a string array called array1, how do I make its values a drop down in a validation? I have no problem with named ranges, but if I assign a name to the array and use the formula1 property of the validation, I get an error.

For example,
Names.Add Name:="arrayname", RefersTo:=array1
With ActiveWorkSheet.Cells(1, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=arrayname"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

However if arrayname were to refer to a range I have no problems. I tried directly using array1 for the Formula1 parameter, but even that doesn't work. Thanks.

Bob Phillips
05-03-2009, 08:26 AM
Dim ary As Variant

ary = Array("Value1", "Value2", "Value3")

With ActiveSheet.Cells(1, 1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ary, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

shankar
05-03-2009, 08:53 AM
Thanks. That worked.