Combobox list based on choice in previous combobox

S

Slim Slender

I think I may be on to something here. I'm just not getting the code
right.

Num Comment
1 this is the comment for the first example of number 1.
1 This is the second comment for number one.
2 This is the only comment for nymber 2.
3 Comment for number 3
5 First comment for number 5
5 Second comment for number 5.
5 Third comment for number 5.
6 Only comment for number 6.
7 One of two comments for number 7.
7 The other comment for number 7.

I have a table or range that looks like above. Notice repeated numbers
in first column.
I use a little procedure to get only the unique values in Num column
to populate a combobox.
The next thing would be to select one of those numbers, 5 for example,
and populate a combobox
with only the three comments associated with that number. It occurred
to me that I could use the same
approach of defining and then adding items selectively based on a
certain criterion as in the first combobox. Below is my attempt but it
doesn't quite work.

Private Sub ComboBox5_Change()

Dim rng As Range, i As Long
Dim SelectedComments As New Collection, Item

For i = 1 To
Worksheets("Sheet2").Range("ReferenceGuide").Rows.Count
Set rng = Worksheets("Sheet2").Cells(i, 2)
If rng.Offset(0, -1).Value = ComboBox5.Value Then
SelectedComments.Add rng.Value, CStr(rng.Value)
End If
Next

For Each Item In SelectedComments
UserForm1.ComboBox6.AddItem Item
Next Item
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top