G
gregl
In Excel 2003, how do I get the Formula1 property of the Validation
object to refer to a function name?
This gives me error 1004:
Sub Macro1()
With Selection.Validation
.Delete
'.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$A$15:$A$19" 'recorded my macro
recorder, works fine.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=GetList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Public Function GetList()
Dim arrayList(1 To 5) As String
arrayList(1) = "aaa"
arrayList(2) = "ccc"
arrayList(3) = "bbb"
arrayList(4) = "ddd"
arrayList(5) = "eee"
GetList = arrayList
End Function
object to refer to a function name?
This gives me error 1004:
Sub Macro1()
With Selection.Validation
.Delete
'.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$A$15:$A$19" 'recorded my macro
recorder, works fine.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=GetList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Public Function GetList()
Dim arrayList(1 To 5) As String
arrayList(1) = "aaa"
arrayList(2) = "ccc"
arrayList(3) = "bbb"
arrayList(4) = "ddd"
arrayList(5) = "eee"
GetList = arrayList
End Function