excel vba 2 combobox's cascading

[bahiapt]

Hi all,

I have searched through the forum but couldn't find anything that would really do, i am trying to populate a combobox (first) with categorie data and after would like after a selection on this combobox the second one would give unique results for that selection which are customer type.

The raw data has repeated data (categorie) and columns are side by side for sources of the combobox.

The search results i have been finding don't suit me because they are normally for userforms or drop down lists.

Appreciate if you guys take in consideration that i am a complete noob in vba, need this for work and that's it, let me know if you need other information.

Best Regards,

Daniel

 

[frabulator]

Hi all,
i am trying to populate a combobox (first) with categorie data and after would like after a selection on this combobox the second one would give unique results for that selection which are customer type.

From what I am understanding of your post, you are in need of a way to populate ComboBox2 with data that is dependent on ComboBox1's selected data? If you know what the data is then you can hard-code it in (like the code below). The code listed below can also be modified to utilize an excel range/sheet/ etc.

To run the code please start a new userform with it two ComboBoxes and drop in this code. After that run the userform. You will see that when you select a number with ComboBox1 the same number will be spelled out in ComboBox2.

Private Sub UserForm_Initialize()
    'using "Private Sub UserForm_Initialize()" will make this code run everytime
    'the userform is opened
    
    
    With ComboBox1  'this adds the item to combobox1
        .AddItem ("1")
        .AddItem ("2")
        .AddItem ("3")
    End With
    
End Sub


Private Sub ComboBox1_Change()
    'this is used for the text change in combobox1
    
    ComboBox2.Clear     'this clears out the combobox incase anything is left over
    
    If ComboBox1.Text = "1" Then
        ComboBox2.AddItem ("One")   'tells combobox2 to add an item named "One"
        ComboBox2.Text = "One"      'tells combobox2 to have "One" as text
    ElseIf ComboBox1.Text = "2" Then
        ComboBox2.AddItem ("Two")
        ComboBox2.Text = "Two"
    ElseIf ComboBox1.Text = "3" Then
        ComboBox2.AddItem ("Three")
        ComboBox2.Text = "Three"
    Else
        ComboBox2.Clear
    End If
    
    Me.Repaint  'refreshes the userform with updated data incase it didnt update properly before
    
End Sub

Hope that helps!
~Frab

 

[bahiapt]

Hi Frabulator many thanks for this code, if it wasn't asking too much is there a version of it without using userform?

Best Regards,
Daniel

 

[frabulator]

is there a version of it without using userform?

I personally only use userforms as they tend to be easier on me. However you should be able to use the same code in a module and assign the "ComboBox1_Change()" macro to the combobox on the worksheet.

A better explanation on how to do that can be found here.

---EDIT---
When you copy the code the 'UserForm Initialize' would not be needed as you are not opening up a userform.

 

[bahiapt]

Many thanks Frabulator i will try it later in the day, dont have much time now.

Regards,

Daniel

 

[bahiapt]

what would be the result of that query the string in combobox 1 or the customer_type (btw is not customer type is customer) data? Also will it bring unique results?

So sorry for not testing but only later can do it but would appreciate if you could take this doubts of my mind.

Best Regards,

Daniel