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.
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.