Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-13-2017, 12:35 PM
thovdael thovdael is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Apr 2017
Posts: 3
thovdael is on a distinguished road
Lightbulb 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
Attached Files
File Type: xlsx example.xlsx (302.0 KB, 1 views)
Reply With Quote
  #2  
Old 04-13-2017, 05:56 PM
Logit Logit is offline Windows 10 Office 2007
Competent Performer
 
Join Date: Jan 2017
Posts: 120
Logit is on a distinguished road
Default

Very basic, step-by-step instructions. You can always expand from this example:

http://www.excel-easy.com/vba/exampl...mbo-boxes.html
Reply With Quote
  #3  
Old 04-13-2017, 07:06 PM
thovdael thovdael is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Apr 2017
Posts: 3
thovdael is on a distinguished road
Default

Quote:
Originally Posted by Logit View Post
Very basic, step-by-step instructions. You can always expand from this example:

http://www.excel-easy.com/vba/exampl...mbo-boxes.html
Thank you for your response
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.
Reply With Quote
  #4  
Old 04-13-2017, 07:53 PM
Logit Logit is offline Windows 10 Office 2007
Competent Performer
 
Join Date: Jan 2017
Posts: 120
Logit is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 04-13-2017, 11:06 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 350
NoSparks is on a distinguished road
Default

Quote:
You might try searching for POPULATE COMBOBOX WITH ARRAY and see what that turns up. Not certain if it can be done
@ Logit
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.
Attached Files
File Type: xlsm example_thovdael.xlsm (322.9 KB, 4 views)
Reply With Quote
  #6  
Old 04-19-2017, 10:41 AM
thovdael thovdael is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Apr 2017
Posts: 3
thovdael is on a distinguished road
Default

Thanks!!, this works perfectly
Reply With Quote
  #7  
Old 04-19-2017, 11:07 AM
Logit Logit is offline Windows 10 Office 2007
Competent Performer
 
Join Date: Jan 2017
Posts: 120
Logit is on a distinguished road
Default

Thanks NoSparks !
Reply With Quote
  #8  
Old 04-19-2017, 08:01 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 350
NoSparks is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
cascade, combobox, userforms
Please reply to this thread with any new information or opinions.

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


All times are GMT -7. The time now is 02:41 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft