Linked ComboBoxes in UserForm

[Steve DB]

How do I get ComboBox2 to update based on the selection in ComboBox1 in a
UserForm. I have a list of counties and the state they are located in. I
need ComboBox2 to give my all the counties located in the State that is
selected in ComboBox1.

 

[Dave Peterson]

I put two comboboxes on a userform.

I put a table of states/counties in sheet1 A1:Bxx.

Then I used this behind the form:

Option Explicit
Private Sub ComboBox1_Change()

Dim myCell As Range

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
Else
Me.ComboBox2.Clear
With Worksheets("sheet1")
For Each myCell In .Range("A1", _
.Cells(.Rows.Count, "A").End(xlUp)).Cells
If myCell.Value = Me.ComboBox1.Value Then
Me.ComboBox2.AddItem myCell.Offset(0, 1).Value
End If
Next myCell
End With
End If

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myCollection As Collection
Dim iCtr As Long

Me.ComboBox1.Clear
Me.ComboBox2.Clear

Set myCollection = New Collection

On Error Resume Next
With Worksheets("sheet1")
For Each myCell In .Range("A1", _
.Cells(.Rows.Count, "A").End(xlUp)).Cells
myCollection.Add myCell.Value, CStr(myCell.Value)
Next myCell
End With
On Error GoTo 0

For iCtr = 1 To myCollection.Count
Me.ComboBox1.AddItem myCollection.Item(iCtr)
Next iCtr

End Sub

 

[Steve DB]

works pretty good but what if my county ComboBox2 has 2 columns? I would
like the 1st and bound column to be a uniques value and the 2nd column to be
what the user sees. How would I do this? This way is new to me. I am used
to using the .RowSource property. Thanks.

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Private Sub ComboBox1_Change()

Dim myCell As Range

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
Else
Me.ComboBox2.Clear
With Worksheets("sheet1")
For Each myCell In .Range("A1", _
.Cells(.Rows.Count, "A").End(xlUp)).Cells

If myCell.Value = Me.ComboBox1.Value Then
With Me.ComboBox2
.AddItem myCell.Offset(0, 1).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
End With
End If
Next myCell
End With
End If

End Sub
Private Sub ComboBox2_Change()
'just to show how to retrieve those values
With Me.ComboBox2
If .ListIndex < 0 Then
Exit Sub
End If
MsgBox .List(.ListIndex, 0) & "--" & .List(.ListIndex, 1)
End With
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myCollection As Collection
Dim iCtr As Long

Me.ComboBox1.Clear
With Me.ComboBox2
.Clear
.ColumnCount = 2
.ColumnWidths = "0;15"
End With

Set myCollection = New Collection

On Error Resume Next
With Worksheets("sheet1")
For Each myCell In .Range("A1", _
.Cells(.Rows.Count, "A").End(xlUp)).Cells
myCollection.Add myCell.Value, CStr(myCell.Value)
Next myCell
End With
On Error GoTo 0

For iCtr = 1 To myCollection.Count
Me.ComboBox1.AddItem myCollection.Item(iCtr)
Next iCtr

End Sub

 

[Steve DB]

Thanks Dave. Is there any way that when I load the UserForm again, I can
get that second column to show in the ComboBox so it's not just a number.

What I mean is: I have each county linked to a unique number identifying
it. So the ComboBox is actually bound to the number column. So when I
reload the UserForm to change something, the number shows in the ComboBox,
not the County Name. Any way around this??

 

[Dave Peterson]

I don't understand.

I saw what was in the cells--not a number.

I put the data in A1:Cxx.

Then when I looped through column A, I used:

With Me.ComboBox2
.AddItem myCell.Offset(0, 1).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
End With

To populate combobox2 with what was in column B and column C. (But the first
column of the combobox was hidden so only the value in column C would appear
(.ColumnWidths = "0;15").

So I had the states in column A, the number in column B and the county in column
C.

You could change the code to match your data.

Thanks Dave. Is there any way that when I load the UserForm again, I can
get that second column to show in the ComboBox so it's not just a number.

What I mean is: I have each county linked to a unique number identifying
it. So the ComboBox is actually bound to the number column. So when I
reload the UserForm to change something, the number shows in the ComboBox,
not the County Name. Any way around this??

 

[Steve DB]

I should clarify........I have ComboBox2 with the ControlSource property =
"G1". So G1 will get the value of the combobox, not the text. Therefore
when I reload the UserForm, I get the number that was in the bound column of
ComboBox2.

 

[Dave Peterson]

I don't quite get it. When I reloaded that form using that code, combobox2 was
empty.

I should clarify........I have ComboBox2 with the ControlSource property =
"G1". So G1 will get the value of the combobox, not the text. Therefore
when I reload the UserForm, I get the number that was in the bound column of
ComboBox2.

 

[Steve DB]

you're right. I forgot I removed the .Clear code first to see what would
happen and that's what happened. Forget I did that.

So why does ComboBox2 clear when I reload the form? I would like it to have
the same County displayed when I reload the form, as when I closed it. Is
there any way?

 

[Dave Peterson]

Maybe...

But since all these numbers are dependent on the state, wouldn't you have to
have that state combobox loaded first?

And if that indicator is used for more than one item, it would mean you'd want
to save the choices somewhere (hidden sheet??).

Any chance you could just hide the form, then reshow it when you need it?

you're right. I forgot I removed the .Clear code first to see what would
happen and that's what happened. Forget I did that.

So why does ComboBox2 clear when I reload the form? I would like it to have
the same County displayed when I reload the form, as when I closed it. Is
there any way?

 

[Steve DB]

To answer your first question, I would say yes, the state combo would have
to be loaded first.

I tried Hiding the form and then Showing it again when needed like you said
but it did the same thing. The linked cell clears as soon as the Form is
shown. I can get this to work with 2 comboboxes on a worksheet, just not on
a UserForm. That is strange to me. Thanks for your help Dave. I really
appreciated it.

 

[Dave Peterson]

I'd remove the linked cells and just populate them with the ok button.

This code in a general module didn't disturb the values in the form:

Option Explicit
Public FormIsLoaded As Boolean
Sub testme()

FormIsLoaded = True
UserForm1.Show

If FormIsLoaded Then
MsgBox UserForm1.ComboBox1.Value & vbLf & UserForm1.ComboBox2.Value
Else
MsgBox "user hit quit"
End If

'do lots of other stuff

If FormIsLoaded Then
UserForm1.Show
End If

End Sub


This was the code behind my Ok and quit buttons:

Private Sub CommandButton1_Click()
FormIsLoaded = False
Unload Me
End Sub
Private Sub CommandButton2_Click()
Me.Hide
End Sub

To answer your first question, I would say yes, the state combo would have
to be loaded first.

I tried Hiding the form and then Showing it again when needed like you said
but it did the same thing. The linked cell clears as soon as the Form is
shown. I can get this to work with 2 comboboxes on a worksheet, just not on
a UserForm. That is strange to me. Thanks for your help Dave. I really
appreciated it.