Dependent List in Data Validation: Runtime Error
Try moving your code to a general module.
Qualify your SuspensionTypeBox and Range references
Call the code from your Change event.
--
Regards,
Tom Ogilvy
"cLiffordiL" wrote in message
...
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!
|