Dependent List in Data Validation: Runtime Error

[cLiffordiL]

Hi!
I'm coding list-type data validation in VBA depending on what I select in
the type cell. Hence I have a function like this:

Public Sub ChoiceTypeBox_Change()
Const MakeModelCell As String = "G39"

' Update the lists under suspension when type changes
If (CStr(SuspensionTypeBox.Value) = "USA") Then
Call Range(MakeModelCell).Validation.Add(xlValidateList,
xlValidAlertStop, xlBetween, "=USAStateList", "")
ElseIf (CStr(SuspensionTypeBox.Value) = "Australia") Then
Call Range(MakeModelCell).Validation.Add(xlValidateList,
xlValidAlertStop, xlBetween, "=AustraliaStateList", "")
End If
End Sub

This is suppose to update the cell with the correct list of the selected
country to choose the states from. However, upon running, it always gives me
a run-time error of "'1004' - Application-defined or object-defined error".
I thought it may have something to do with my range so I've been trying
alternatives like replacing the named list with their actual range like
"=USAState!A1:A49", "=USAState!$A$1:$A$49", "='USAState'!A1:A49", etc but
it's always the same.
Great appreciate any suggestions or help!
Thanx!

 

[Tom Ogilvy]

Try moving your code to a general module.

Qualify your SuspensionTypeBox and Range references

Call the code from your Change event.

 

[cLiffordiL]

Hmm.. tried. But doesn't really work. I've re-encapsulated the code into a
Sub in my global module so that now it looks like this:

' Inside worksheet module
Private Sub SuspensionTypeBox_Change()
Const DestCell As String = "F41"

Call UpdateValidationList(SuspensionTypeBox.Value, Range(DestCell),
"=USAStateList", "=AustraliaStateList")
End Sub

' Inside global module
Public Sub UpdateValidationList(Country As String, Cell As Range, Table1 As
String, Table2 As String)

If (Country = "USA") Then
Call Cell.Validation.Add(xlValidateList, xlValidAlertStop,
xlBetween, Table1, "")
ElseIf (Country = "AUSTRALIA") Then
Call Cell.Validation.Add(xlValidateList, xlValidAlertStop,
xlBetween, Table2, "")
End If
End Sub

1004 runtime error still triggers when it goes to either of the lines that
calls

Call Cell.Validation.Add...
_________
cLiffordiL

 

[Tom Ogilvy]

the original code worked fine for me in a general module when I qualified
suspensiontypebox and created the named ranges.

 

[cLiffordiL]

Hmm.. perhaps there's something I did wrong somewhere.. what do you mean
by "qualified suspensiontypebox"?
Cheers!
_______
cLiffordiL

 

[cLiffordiL]

I might have figured out the problem. My (dynamic) named ranges were using
worksheet functions inside, which generates an error for Validation's Add
method as it only accepts comma delimited ranges.
_________
cLiffordiL