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
---------------------------------------------------------------------------------------------------

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
---------------------------------------------------------------------------------------------------