[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 46
  • Last Modified:

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
0
Wilder1626
Asked:
Wilder1626
  • 5
  • 5
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
Wilder1626Author Commented:
This is exactly what i was looking for.

Thank you for your help
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now