List could not be retrieved
 

I'm doing something like a dependent list in reverse: I wanna fill in the
country automatically based on the state the user has input.
I've written a function to be called at the formula bar (I've placed this
code in the general module).
However, it always gives me #VALUE! as result. Upon stepping through it, I
found out that the code always breaks at the List assignment line (indicated
by ). Somehow Excel refuses to assign List to a dynamic named range I've
defined in the workbook.
Would appreciate any suggestions. Thanks heaps!

' Choose the country based on state
Public Function Country_Change(State As Range) As String
Dim List As Range

If (Not CStr(State.Value) = "") Then
Application.EnableEvents = False
' Choose the right list
List = Application.Names.Item("CountryList").RefersToRang e
Country_Change = MyVLookUp(State, List, 1)
Application.EnableEvents = True
End If
End Function
______
cLiffordiL

List could not be retrieved
 

Hi,

set List.

Regards,
Ivan