Problems with cascading combo box, appears to only be 1 column's numeric values (zipcodes)

I've gone over this countless times trying to debug, modify, test..
and searched for answers on the NGs.

The combobox (exists on a UserForm) that's causing all the trouble
is to be filled/updated with numeric data (ZipCodes) where as all the
other ones are filled with text strings (City,State,Etc). I found this
code
on a French language XL forum and have tried to adapt it to my case.

Dim'd Item As Variant?

---------------------------------------------------------------------------=
----------------------
Private Sub cboLimitEstadoDes_Change()
Dim i As Integer
Dim ColBase1 As New Collection
Dim Item As Variant
Dim X As Byte

For X =3D 1 To 4
Me.Controls("ComboBox" & X).Style =3D fmStyleDropDownList
Next

' The following is a workaround to combobox update latencyBS
If cboLimitEstadoDes.Value =3D "A - H" Then
' Pulls values from worksheet LocMX
With LocMX
Lista =3D .Range("A1:D" & .Range("A32767").End(xlUp).Row)
End With

ElseIf cboLimitEstadoDes.Value =3D "J - Q" Then
' Pulls values from worksheet LocMX2
With LocMX2
Lista =3D .Range("A1:D" & .Range("A32767").End(xlUp).Row)
End With

ElseIf cboLimitEstadoDes.Value =3D "S - Z" Then
' Pulls values from worksheet LocMX2
With LocMX3
Lista =3D .Range("A1:D" & .Range("A32767").End(xlUp).Row)
End With

' Else 'could put msgbox w/response later...
End If

On Error Resume Next
For i =3D 1 To UBound(Lista)
ColBase1.Add Lista(i, 1), Lista(i, 1)
Next
On Error GoTo 0

For Each Item In ColBase1
Me.ComboBox1.AddItem Item
Next
End Sub
-----------------------------------------------------------------
Private Sub ComboBox1_Change()
ComboUpdates 2
End Sub
-----------------------------------------------------------------
Private Sub ComboBox2_Change()
ComboUpdates 3
End Sub
-----------------------------------------------------------------
Private Sub ComboBox3_Change()
ComboUpdates 4
End Sub
-----------------------------------------------------------------
Private Sub ComboUpdates(Num As Byte)
Dim i As Integer
Dim ColBaseX As New Collection
Dim Item As Variant
Dim X As Byte

For X =3D Num To 4
Me.Controls("ComboBox" & X).Clear
Next

On Error Resume Next
For i =3D 1 To UBound(Lista)
If Lista(i, Num - 1) =3D Me.Controls("ComboBox" & Num - 1) Then
ColBaseX.Add Lista(i, Num), Lista(i, Num)
End If
Next
On Error GoTo 0

For Each Item In ColBaseX
Me.Controls("ComboBox" & Num).AddItem Item
Next
End Sub
---------------------------------------------------------------------------=
------------------------
Private Sub UserForm_Initialize()
txtNombreDes.Value =3D ""
txtApellidoDes.Value =3D ""
With cboTituloDes
.AddItem "Se=F1or"
.AddItem "Se=F1ora"
End With
cboTituloDes.Value =3D ""
txtClaveTUDes.Value =3D ""
txtTelCelDes.Value =3D ""
txtTelCasaDes.Value =3D ""
txtDomicilio1Des.Value =3D ""
txtDomicilio2Des.Value =3D ""
With cboLimitEstadoDes
.AddItem "A - H"
.AddItem "J - Q"
.AddItem "S - Z"
End With
cboLimitEstadoDes.Value =3D ""
ComboBox1.Value =3D ""
ComboBox2.Value =3D ""
ComboBox3.Value =3D ""
ComboBox4.Value =3D ""
optIndividualDes =3D True
txtInformacionDes.Value =3D ""
cboTituloDes.SetFocus
End Sub
---------------------------------------------------------------------------=
------------------------

Robert,
I have a solution that I could send you if you wish -- it is too complicated
to easily explain.
I haven't found a way to post a file to this forum so I would need
and email or other method of sending it to you.

OR
you can post your question on
Woody's lounge
http://www.wopr.com/cgi-bin/w3t/post...?Cat=&Board=xl
and I could post it there.


"tHeRoBeRtMiTcHeLL" wrote:

> I've gone over this countless times trying to debug, modify, test..
> and searched for answers on the NGs.
>
> The combobox (exists on a UserForm) that's causing all the trouble
> is to be filled/updated with numeric data (ZipCodes) where as all the
> other ones are filled with text strings (City,State,Etc). I found this
> code
> on a French language XL forum and have tried to adapt it to my case.
>
> Dim'd Item As Variant?
>
> -------------------------------------------------------------------------------------------------
> Private Sub cboLimitEstadoDes_Change()
> Dim i As Integer
> Dim ColBase1 As New Collection
> Dim Item As Variant
> Dim X As Byte
>
> For X = 1 To 4
> Me.Controls("ComboBox" & X).Style = fmStyleDropDownList
> Next
>
> ' The following is a workaround to combobox update latencyBS
> If cboLimitEstadoDes.Value = "A - H" Then
> ' Pulls values from worksheet LocMX
> With LocMX
> Lista = .Range("A1:D" & .Range("A32767").End(xlUp).Row)
> End With
>
> ElseIf cboLimitEstadoDes.Value = "J - Q" Then
> ' Pulls values from worksheet LocMX2
> With LocMX2
> Lista = .Range("A1:D" & .Range("A32767").End(xlUp).Row)
> End With
>
> ElseIf cboLimitEstadoDes.Value = "S - Z" Then
> ' Pulls values from worksheet LocMX2
> With LocMX3
> Lista = .Range("A1:D" & .Range("A32767").End(xlUp).Row)
> End With
>
> ' Else 'could put msgbox w/response later...
> End If
>
> On Error Resume Next
> For i = 1 To UBound(Lista)
> ColBase1.Add Lista(i, 1), Lista(i, 1)
> Next
> On Error GoTo 0
>
> For Each Item In ColBase1
> Me.ComboBox1.AddItem Item
> Next
> End Sub
> -----------------------------------------------------------------
> Private Sub ComboBox1_Change()
> ComboUpdates 2
> End Sub
> -----------------------------------------------------------------
> Private Sub ComboBox2_Change()
> ComboUpdates 3
> End Sub
> -----------------------------------------------------------------
> Private Sub ComboBox3_Change()
> ComboUpdates 4
> End Sub
> -----------------------------------------------------------------
> Private Sub ComboUpdates(Num As Byte)
> Dim i As Integer
> Dim ColBaseX As New Collection
> Dim Item As Variant
> Dim X As Byte
>
> For X = Num To 4
> Me.Controls("ComboBox" & X).Clear
> Next
>
> On Error Resume Next
> For i = 1 To UBound(Lista)
> If Lista(i, Num - 1) = Me.Controls("ComboBox" & Num - 1) Then
> ColBaseX.Add Lista(i, Num), Lista(i, Num)
> End If
> Next
> On Error GoTo 0
>
> For Each Item In ColBaseX
> Me.Controls("ComboBox" & Num).AddItem Item
> Next
> End Sub
> ---------------------------------------------------------------------------------------------------
> Private Sub UserForm_Initialize()
> txtNombreDes.Value = ""
> txtApellidoDes.Value = ""
> With cboTituloDes
> .AddItem "Señor"
> .AddItem "Señora"
> End With
> cboTituloDes.Value = ""
> txtClaveTUDes.Value = ""
> txtTelCelDes.Value = ""
> txtTelCasaDes.Value = ""
> txtDomicilio1Des.Value = ""
> txtDomicilio2Des.Value = ""
> With cboLimitEstadoDes
> .AddItem "A - H"
> .AddItem "J - Q"
> .AddItem "S - Z"
> End With
> cboLimitEstadoDes.Value = ""
> ComboBox1.Value = ""
> ComboBox2.Value = ""
> ComboBox3.Value = ""
> ComboBox4.Value = ""
> optIndividualDes = True
> txtInformacionDes.Value = ""
> cboTituloDes.SetFocus
> End Sub
> ---------------------------------------------------------------------------------------------------
>
>