Cell value as named range for validation list
 

I have the following code to define a validation list based on the
named range A_CASE_BACK_TYPE
How can I refer to a cell value as opposed to typing the named range.
In short I want to refer to cell "K3" (which has the named range name)
to define the validation list.

Range("E3").Select
With Selection.Validation
.delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning,
Operator _
:=xlBetween, Formula1:="=A_CASE_BACK_TYPE"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "NOT LISTED"
.InputMessage = ""
.ErrorMessage = "YOUR CHOICE IS NOT ON THE LIST"
.ShowInput = True
.ShowError = True
End With

Cell value as named range for validation list
 

With the defined name entered in K3, try =INDIRECT($K$3) as your list source.

" wrote:

I have the following code to define a validation list based on the
named range A_CASE_BACK_TYPE
How can I refer to a cell value as opposed to typing the named range.
In short I want to refer to cell "K3" (which has the named range name)
to define the validation list.

Range("E3").Select
With Selection.Validation
.delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning,
Operator _
:=xlBetween, Formula1:="=A_CASE_BACK_TYPE"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "NOT LISTED"
.InputMessage = ""
.ErrorMessage = "YOUR CHOICE IS NOT ON THE LIST"
.ShowInput = True
.ShowError = True
End With

Cell value as named range for validation list
 

Thank you very much for the reply...I had a problem with your tip and
came up with the following that appears to work. Your use of the
INDIRECT function greatly helped another issue that I was having.
Thanks again,
Bill

Range("E3").Select
With Selection.Validation
.delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning,
Operator _
:=xlBetween, Formula1:=Range("K3").Value
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "NOT LISTED"
.InputMessage = ""
.ErrorMessage = "YOUR CHOICE IS NOT ON THE LIST"
.ShowInput = True
.ShowError = True
End With