#1
|
|||
|
|||
Userform with cascade ComboBox
Hi All,
First of all, thank to them who take the time to look at this. I am currently making a database in Excel where I want to list several companies. To add a new company to the list, I want to use a userform (As given in the example file attached). Three of the fields that have to be filled out are "Super Category", "Major Category" and "Sub Category". In order not to make thousands of different names or designations, I would like to give the people choices using ComboBoxes. The purpose is to link them all three to each other. The content of Sub Category has to be based on the content of the Major Category and Major Category will, on its turn, be based on the choices made in the Super Category field. The data with the different categories used for the ComboBoxes is located on sheet 1 in the example file attached. For example: if a person chooses "Baby" as Super category, the only items that may appear as Major categories should be: baby food, diapers, baby wipes, formula, beby health, baby accessories and petroleum jelly. If baby food is chosen as major category, only 'baby food jar', 'baby food dry, cereal', 'baby food toddler snacks', baby food juices' and 'O&N baby food' can appear. The problem I have right now is with the code to link the Comboboxes and provide them with the right content. Can anyone help me on this code and tell me how I can connect the boxes and make sure that they contain the values based on each other value? Thanks in Advance, Thomas |
#2
|
|||
|
|||
Very basic, step-by-step instructions. You can always expand from this example:
http://www.excel-easy.com/vba/exampl...mbo-boxes.html |
#3
|
|||
|
|||
Quote:
The only disantvantage I have is that I have more than 1000 items that I have to put in the combobox... I am looking for more like a general code, so I don't have to do iT manually line boy line. |
#4
|
|||
|
|||
It's late here now. Going to get some sleep.
You might try searching for POPULATE COMBOBOX WITH ARRAY and see what that turns up. Not certain if it can be done (I haven't had an occasion to try yet), but if you can reference an array (a list of the terms you want to be displayed in the combobox) that would reduce some of the typing. The array list could be placed in a hidden sheet for the combobox to reference. One way or the other, there is going to be some typing involved. I'll check tomorrow to see what I can find also. |
#5
|
|||
|
|||
Quote:
Certainly can, here's the goto page I have bookmarked. @ thovdael Have a look at this. The first combo is populated from column A at the time of Userform_Initialization. The second and third combos are populated by filtering based on the selections in the combos. Hopefully this does what you're after and is commented enough that you can follow and adapt to your project. |
#6
|
|||
|
|||
Thanks!!, this works perfectly
|
#7
|
|||
|
|||
Thanks NoSparks !
|
#8
|
|||
|
|||
You're welcome, glad I could help.
Be aware this solution is not a one size fits all. It works fine here because the data is sorted and all the filtered rows are contiguous. Also, the filtRng.Columns(x).Offset(1).SpecialCells(xlCellTy peVisible) always includes one extra row beyond the bottom of the filtRng which gets loaded into the drop down lists, hence the blank at the bottom. This prevents issues when there are no filter results but it may not always be desirable. |
Tags |
cascade, combobox, userforms |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Populate Combobox from Excel into a Word UserForm | ferguson4848 | Word VBA | 3 | 10-28-2016 09:05 PM |
Cannot tile or cascade presentations in PP2013 | dave3point0 | PowerPoint | 0 | 01-07-2016 03:20 AM |
Using combobox to pass data from an Excel spread sheet to text boxes on userform | Stewie840 | Word VBA | 14 | 12-17-2015 10:13 PM |
Value of Combobox in other userform | Vibov | Excel Programming | 4 | 11-19-2015 04:12 AM |
Create UserForm ComboBox or ContentControl | ptmuldoon | Word VBA | 11 | 01-17-2015 05:58 PM |