[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VBA Excel -

Posted on 2017-10-12
10
Low Priority
?
32 Views
Last Modified: 2017-10-13
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
Comment
Question by:Wilder1626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 60

Expert Comment

by:HainKurt
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
 
LVL 11

Author Comment

by:Wilder1626
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
 
LVL 60

Expert Comment

by:HainKurt
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 11

Author Comment

by:Wilder1626
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
 
LVL 60

Expert Comment

by:HainKurt
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
 
LVL 11

Author Comment

by:Wilder1626
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
 
LVL 60

Expert Comment

by:HainKurt
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
 
LVL 11

Author Comment

by:Wilder1626
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
 
LVL 60

Accepted Solution

by:
HainKurt earned 1000 total points
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
 
LVL 11

Author Closing Comment

by:Wilder1626
This is exactly what i was looking for.

Thank you for your help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question