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!

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!

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" wrote in message
...
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!

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

--
Regards,
Tom Ogilvy


"cLiffordiL" wrote:

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" wrote in message
...
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!

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

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

--
Regards,
Tom Ogilvy


"cLiffordiL" wrote:

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" wrote in message
...
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!

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

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

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

--
Regards,
Tom Ogilvy


"cLiffordiL" wrote:

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" wrote in message
...
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!