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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
This is exactly what i was looking for.

Thank you for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.