VBA Excel -

Hi

I need your help with a macro that will update combobox's based on name and count from the dbase sheet.

If i select a specific name, it would adjust the second combobox with the count assigned to the name and then adjust the items based on the name and count.

ComboBox feed
How can i do that?

Here is a sample of the file.

Thank you for your help.
combobox-feed.xlsm
LVL 11
Wilder1626Asked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
you should use the same structure that I used to populate others...

use dictionary...

Private Sub CommandButton1_Click()
Dim db As Worksheet
Set db = Worksheets("dbase")
UserForm1.Label1.Caption = db.Cells(1, 1).Value
UserForm1.Label2.Caption = db.Cells(1, 2).Value
UserForm1.Label3.Caption = db.Cells(1, 3).Value

  With CreateObject("scripting.dictionary")
  
  For i = 2 To db.UsedRange.Rows.Count
    If Not .exists(db.Cells(i, 1).Text) Then
      UserForm1.ComboBox1.AddItem (db.Cells(i, 1))
      .Add db.Cells(i, 1).Text, Nothing
    End If
  Next
  
  End With

  UserForm1.Show
End Sub

Open in new window

29062277.xlsm
0
 
HainKurtSr. System AnalystCommented:
here

Private Sub CommandButton1_Click()
Dim db As Worksheet
Set db = Worksheets("dbase")
UserForm1.Label1.Caption = db.Cells(1, 1).Value
UserForm1.Label2.Caption = db.Cells(1, 2).Value
UserForm1.Label3.Caption = db.Cells(1, 3).Value

For i = 2 To db.UsedRange.Rows.Count
    UserForm1.ComboBox1.AddItem (db.Cells(i, 1))
    UserForm1.ComboBox2.AddItem (db.Cells(i, 2))
    UserForm1.ComboBox3.AddItem (db.Cells(i, 3))
Next

UserForm1.Show
End Sub

Open in new window

29062277.xlsm
0
 
Wilder1626Author Commented:
Hi HainKurt

When i select an item from the ComboBox1, the ComboBox2 doesnt adjust  based on the ComboBox1 value and so on...

I really need them to adjust the items.

Is there a way to only populate the combo boxes with unique values, without duplications?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HainKurtSr. System AnalystCommented:
When i select an item from the ComboBox1, the ComboBox2 doesnt adjust  based on the ComboBox1 value and so on...

I dont get the requirement here...

then why are you suing 3 comboboxes?
do you want to autoselect other 2 when a combobox is changed?
0
 
Wilder1626Author Commented:
Let's take this picture in example:
example
If i select the Name "Steve" in Combobox1, when i will select an item in Combobox2, i should only see the counts for that name. In this case, "2".

Then, when i select an Item in ComboBox3, based on the items "Steve" and "2", i should only see the items "Chair" and "Window".

Hope this answers your question.
0
 
HainKurtSr. System AnalystCommented:
add these codes to form

Private Sub ComboBox1_Change()
  Dim ix As Integer
  ix = ComboBox1.ListIndex
  
  ComboBox2.Value = ComboBox2.List(ix)
  ComboBox3.Value = ComboBox3.List(ix)
End Sub

Private Sub ComboBox2_Change()
  Dim ix As Integer
  ix = ComboBox2.ListIndex
  
  ComboBox1.Value = ComboBox1.List(ix)
  ComboBox3.Value = ComboBox3.List(ix)
End Sub

Private Sub ComboBox3_Change()
  Dim ix As Integer
  ix = ComboBox3.ListIndex
  
  ComboBox1.Value = ComboBox1.List(ix)
  ComboBox2.Value = ComboBox2.List(ix)
End Sub

Open in new window

29062277.xlsm
0
 
Wilder1626Author Commented:
I still see in ComboBox 2 and ComboBox3 Items that doesnt belong to the name in ComboBox1.
issue
I really need the ComboBox Control to dependent on the item chosen in the others.
0
 
HainKurtSr. System AnalystCommented:
ok, look at this

Private Sub ComboBox1_Change()
  Dim db As Worksheet
  Set db = Worksheets("dbase")

  ComboBox2.Clear
  ComboBox3.Clear
  
  With CreateObject("scripting.dictionary")

  For i = 2 To db.UsedRange.Rows.Count
    If db.Cells(i, 1).Text = ComboBox1.Value Then
      If Not .exists(db.Cells(i, 2).Text) Then
        UserForm1.ComboBox2.AddItem (db.Cells(i, 2).Text)
        .Add db.Cells(i, 2).Text, Nothing
      End If
    End If
  Next

  End With
End Sub

Private Sub ComboBox2_Change()
  Dim db As Worksheet
  Set db = Worksheets("dbase")
  
  ComboBox3.Clear
  
  With CreateObject("scripting.dictionary")
  
  For i = 2 To db.UsedRange.Rows.Count
    If db.Cells(i, 1).Text = ComboBox1.Value And db.Cells(i, 2).Text = ComboBox2.Value Then
      If Not .exists(db.Cells(i, 3).Text) Then
        UserForm1.ComboBox3.AddItem (db.Cells(i, 3).Text)
        .Add db.Cells(i, 3).Text, Nothing
      End If
    End If
  Next
  
  End With
End Sub

Open in new window

29062277.xlsm
0
 
Wilder1626Author Commented:
wow, This looks very good. One last detail.

How can i remove the duplication from ComboBox1?

I assume i need to adjust the below code.

Dim db As Worksheet
Set db = Worksheets("dbase")
UserForm1.Label1.Caption = db.Cells(1, 1).Value
UserForm1.Label2.Caption = db.Cells(1, 2).Value
UserForm1.Label3.Caption = db.Cells(1, 3).Value

For i = 2 To db.UsedRange.Rows.Count
    UserForm1.ComboBox1.AddItem (db.Cells(i, 1))

Next

UserForm1.Show

Open in new window

0
 
Wilder1626Author Commented:
This is exactly what i was looking for.

Thank you for your help
0
All Courses

From novice to tech pro — start learning today.