• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic comboboxes in Useform

H

Hugo huijer

Member
Hi Chandoo,

I want to make an userform which suggests the value of a combobox based on the input of another combobox within the userform.

For example, the source of my combobox is a column in a table. This table has 2 columns. If my first columns contains the values 'vegetables' and 'fruits', and my second column 'apple', 'banana', 'spinach' and 'beans'. The source of my first combobox is the first column of my table containing the data. The source of my second combobox is the second column.

How can I make the source of the second combobox dynamic, in the way that when a user selects the 'fruits' in combobox 1 the source of combobox 2 will automatically adjust to the values 'apple' and 'banana'.

Is this even possible? Any help will be greatly appreciated!
 
Oh, and if it's possible, could it also filter just the unique values of the second column. Like if there were multiple cells containing the value 'apple', it would only show it once in the combobox? Thanks a lot.
 
Hi Hugo ,

If your table's first column contains , let us say 3 items , Item 1 , Item 2 , Item 3 , and the second column contains , let us say 11 items , which are Sub-Item 1 , Sub-Item 2 , Sub-Item 3 ,.... , then don't you have to have the Item names repeated for those Sub-Items which belong to one Item ? Thus , your table has to look like this :

Code: 
Item Name    Sub-Item Name
    Item 1            Sub-Item 3
    Item 2            Sub-Item 6
    Item 2            Sub-Item 1
    Item 2            Sub-Item 3
    Item 1            Sub-Item 1
    Item 3            Sub-Item 5
    Item 3            Sub-Item 2
    Item 1            Sub-Item 2
    Item 3            Sub-Item 4
    Item 3            Sub-Item 3
    Item 1            Sub-Item 4
In such a case , there are two ways to get the unique list of items / sub-items viz. through formulae , or using pivot tables. If you use formulae they are dynamic , and reflect the input data at all times ; if you use pivot tables , when ever the input data changes , the pivot tables need to be refreshed , either manually or through a macro.

Which would you like ?

Narayan
 
I don't quite understand. If I'm correct I already have my table values like you explained here, right? I would like the source of the combobox to be dynamic, so I'd like to use formulae.

thanks.
 
Hi Hugo ,

There were two parts to your original question :

1. How to get only unique values within the comboboxes

2. How to make the second combobox dynamic , so as to reflect items based on the selection made in the first combobox

These two parts are unrelated.

I wanted your opinion on the first ; getting unique values into the combobox can be done in two ways :

a. Generate a list of unique items in the two columns by using formulae

b. Insert a pivot table , which will list only unique values from the two columns.

Once this is done , we can go on to the second part.

Narayan
 
Aha, allright, thanks for making this clear.

I'd like to generate a list of unique values by using formulae. But can a formulae in a sheet be updated realtime by values that a user types in a userform? I don't know how, but if this is possible, I would see how this could work this way.
 
Hi Hugo ,

Depending on what kind of controls you have on your userform , you can have LinkedCells pointing to worksheet cells , and data entered on the userform automatically goes into designated cells , which can then be used in formulae.

Narayan
 
Great, my userform consists of textboxes, comboboxes and commandbuttons. A rather usual userform, actually. I want data in combobox 1 to automatically influence the source (table in sheet) of combobox 2 (like in the example in the OP).

Thanks for your replies Narayan
 
Hi Hugo ,

Is it possible for you to upload your workbook ? Given the complexity of your requirements , I doubt that anyone can do the coding without your data layout and your userforms.

Narayan
 
Hi guys, thanks for responding!

I've seen the file you, and it looks somewhat like the thing that I want! It's not quite what I want yet though ;-)

I've uploaded an example, this shows what I want in my spreadsheet. If you can direct me with the example, I should be able to apply this to my own sheet.
 

Attachments

  • Dependent combobox example.xlsm
    21.5 KB · Views: 34
Hi HUgo,

Check the attached.. you can follow this approach for the same..

Code: 
Dim Deb
Private Sub ComboBox1_Change()
Me.ComboBox2.ListIndex = -1
Deb = Sheets("Sheet1").Range("A2").CurrentRegion
For j = 2 To UBound(Deb)
  If InStr(Roy & ",", "," & Deb(j, 2) & ",") = 0 And Deb(j, 1) = ComboBox1 _
  Then Roy = Roy & "," & Deb(j, 2)
Next
Me.ComboBox2.List = Split(Mid(Roy, 2), ",")
End Sub

Private Sub UserForm_Initialize()
Deb = Sheets("Sheet1").Range("A2").CurrentRegion
For j = 2 To UBound(Deb)
  If InStr(Raj & ",", "," & Deb(j, 1) & ",") = 0 Then Raj = Raj & "," & Deb(j, 1)
Next
Me.ComboBox1.List = Split(Mid(Raj, 2), ",")
End Sub

Courtesy : SNB.. :)
 

Attachments

  • Dependent combobox example.xlsm
    22.4 KB · Views: 94
Thanks a lot Debraj!!!!!!

That's exactly what I want. I will now try to use the code in my own spreadsheet (allthough I don't quite understand what you did there lol)

Thanks heaps!
 
Hmm, I've tried to insert the code in my spreadsheet, changing all the ranges and names properly (I think?). When I now try to run the macro opening the userform, is gives me: Run-time error '70': Permission denied
 
Debugging the error leads me to the line where the userform is showed, the macro that runs after the button is clicked. So I think it has something to do with the private sub Userform_Initialize?
 
Pff I'm still a noobie on the VBA front. I can not figure out why it's not working.. What are the 'Raj', 'Roy', and 'j' doing in this code? Is my error maybe caused by one of them?
 
Here is my file.

What I want:

A user first inputs the contract budget. The budget is catagorized in 5 different type of works: earthworks, siteworks... etc. The contract budget will be automatically inputted in the table in the sheet 'BACKPAGE REGULAR DATA'.

When the user then wants to input the variation budget, they must choose one of the previously entered type of works, upon which the corresponding values will be filtered.

It's just like the previous example. The variation input is just like the example userform, where the 2 comboboxes will get their values out of the table. This table is automatically filled in by the Contract budget userform. I hope this is clear now. Thanks a lot!
 

Attachments

  • example chandoo.xlsm
    40.7 KB · Views: 47
Hi Hugo,
Sorry for late response.. somehow I missed the post..

Please check the attached..
BTW, I have not checked other code or flowchart.. just updated the Variation Form..
 

Attachments

  • example chandoo(1).xlsm
    41.1 KB · Views: 85
That's awesome! Thanks. It seems to work, though I still have no clue on what is going on there lol. I can not find any info on some of the statements you used, like 'Raj', 'Roy' and 'j'.
 
Hi Hugo,

Glad that it works for you..

They are nothing but Implicit Variable's .. which store the value provided range's in an array..
 
Hi again,

Hope I haven't stepped on anyones nerves already, but I'm back with a new issue.

So I got the functions working in my sheet with your help. I still don't really know what's going on in there, but hey ;) (Debraj, is your forumname based on Excel statements 'deb' and 'raj', or did you just hide your own name into the code??? lol I just noticed it)

So you showed me how to make a unique list of values for combobox 1 and make combobox 2 select its datalist dynamically depending on which value is selected in combobox 1. So far so good! I now want to add a combobox 3! Combobox 2 & 3 should basically do the same as 1 & 2. Combobox 2 should contain a list with unique values, and combobox 3 should show a dynamic list based on the value in combobox 2.

I know the mechanisms are basically the same, but as I still don't really understand what's going on in there, I'd like some advise here. Thanks heaps!
 
You must log in or register to reply here.
Back
Top