Populating Combo Box2 based on a selection made from Combo Box1

**newqueen** · 09-02-2014, 12:47 PM

Dear all

Please, I need a VBA code that will populate Combo Box2 based on a selection made from Combo Box1.

I have attached a sample file.

My Combo Boxes 1 and 2, respectively, are not on a userform. They are just on a Worksheet.

Looking at the file attached, The Combo Boxes are on Sheet1 and the Product Listings are on a different sheet named ‘Products’ (with the main Product Categories in column G and the Categpru/Description of the products in columns A and B).

I wish to use the setup eventually for the purposes of a quick look-up of prices of products.

I am aware that Data Validation would work OK. However, I wish to use Combo Boxes so I’ll be able to format/manipulate the lists in each combo box, hence I need a VBA code that will drive the combo boxes.

As I searched through forums, I came across a vba code written by royUK for a purpose that is slightly identical to mine (although used on a userform). Here is the link:

http://www.ozgrid.com/forum/showthre...t=55961&page=2

And here is the code:

Please Login or Register  to view this content.

I use Excel 2007. I tried to adapt the code for my purpose. Unfortunately, I can’t get it to work for me. I should be very grateful for help with a vba code that will link the two combo boxes.

Alternatively, if royUK’s code above could be tweaked to drive my two combo boxes on sheet 1, that will be great and I would also be grateful.

Thanks.

Newqueen

**jaslake** · 09-02-2014, 02:10 PM

Hi newqueen

Create a Named Range called "Category" for Column A of "Products"'

Please Login or Register  to view this content.

Place this Code in Sheet1 Module...the Code is RoyUK Code adapted

Please Login or Register  to view this content.

**newqueen** · 09-02-2014, 03:39 PM

Hi John,

Thanks very much for your prompt and kind response.

I followed your instructions/steps above, but unfortunately, I could not get it to work.

I created the Named Range in column A of 'Products' sheet and assigned the offset formula as you instructed. I also copied and pasted the code into the Sheet1 Module. But nothing shows up in any of the combo boxes. I don't know what I am doing wrong.

Is it possible you could test it all for me, please, using a copy of the sample file that I uploaded. If you get it to work, I'd be happy to use your working copy uploaded back.

Thanks very much for your help.

Newqueen

**jaslake** · 09-02-2014, 03:44 PM

Hi newqueen

Attached is the sample File with the Code embedded.

**jaslake** · 09-02-2014, 06:29 PM

Hi newqueen

Ha!!! I just realized why this is not working for you...I forgot to tell you the MOST important part!!!

In the Developer Tab, Click on Design Mode...Right Click on Combobox1...Click on Properties...go down to ListFillRange...type in Main...see Image attached...Exit Design Mode.

Sorry about that.

**newqueen** · 09-02-2014, 07:33 PM

Hi John,

That was brilliant! Thank you so very much. You are very kind and helpful. I am truly grateful.

Newqueen

**jaslake** · 09-02-2014, 08:17 PM

Hi newqueen

You're welcome...glad I could help. Thanks for the Rep.

**newqueen** · 09-03-2014, 08:57 PM

Hi John,

Re my subsequent request, I should be grateful if you would, please, help me with the steps I should take to successfuly place a vlookup formula in Sheet1 Cell K5 so as to be able to pick up a price from the 'Products' sheet, column D, as earlier mentioned to you in my PM.

Thank you for your continued support.

Newqueen

**jaslake** · 09-03-2014, 09:50 PM

Hi Clare

Please note the Code in General Module 1. This line of Code can be in any General Module but it must be at the very top of that Module

Please Login or Register  to view this content.

Please note the changes in this Code

Please Login or Register  to view this content.

And the addition of this Code

Please Login or Register  to view this content.

Let me know of issues.

**newqueen** · 09-03-2014, 11:11 PM

Hi John,

Your VBA solution was spot on! Thanks heaps.

Just a few questions, please: Do you think that a vba code would be better than a vlookup formula for that purpose?

Also, if in future, I add more items to the 'Products' sheet both in the main 'Categories' list i.e. column G, and sort the list, and also add entries to columns A to D, and also sort (based on column A), will the sorting of those lists affect the outcome in Sheet1 in any way?

Grateful for you kind clarification, please.

newqueen

**jaslake** · 09-04-2014, 10:28 AM

Hi Clare

I have no opinion regarding this

Do you think that a vba code would be better than a vlookup formula for that purpose

Regarding this...all the Named ranges are Dynamic so this should not be an issue

I add more items to the 'Products' sheet both in the main 'Categories' list i.e. column G, and sort the list, and also add entries to columns A to D

Sorting should also not be an issue...try adding items...and sorting...let me know if you have a problem.

**newqueen** · 09-04-2014, 02:51 PM

Thanks John for your kind response. Not that using a vba code in place of vlookup matters that much. I only wanted to seek opinion on which option might be trouble-free, easier to work with and, therefore better.

Meanwhile, I'll try the sorting. I am not looking forward to any issues with that.

Thank you for everything.

Newqueen

**jaslake** · 09-04-2014, 02:57 PM

Hi Clare

I'd suggest you pose this question in the Formulas Forum...you'll get a much better educated response from the Formula Gurus.

**newqueen** · 09-04-2014, 06:40 PM

Hi John,

Pleeeaaasee, just one final look at this for me. Sorry for bothering you.

For reasons, I am still looking at the possibility of using a VLOOKUP formula, sometime, for the look-up cell. In consideration of that, I have used your first solution and tried to apply a Vlookup formula for the price output cell (F5) - (See attached), but I keep getting an error message. Please kindly have a look at it fpr me.

I certainly am VERY pleased with the vba code version for the price lookup. I want to be sure that I'm able to use a vlookup for it if and when the need arises. I also want to learn from you regarding this.

I am aware that in order to use a vlookup for an ActiveX control combobox, there has to be a linked cell. I have, for this purpose, used Cell I2 as the linked cell. But when I click to choose from Combobox 2, I get a debug message and the 'me.combobox2.clear' line of the vba code gets highlighted yellow - see attached file.

Thanks for everything.

Newqueen

**mehmetcik** · 09-04-2014, 08:39 PM

Hi Clare.

Damn you pose difficult problems.

I am useless with sheet based Comboboxes

So I have spent three days [ Three Days ] trying to get my head around this.

There are three types of sheet based comboboxs [ I didn't know that ]. All with different pros and cons.

So I created a couple of macros to help you.

PS I didn't like your layout.

Ok my first routine runs when you activate sheet one. It finds how many rows are used in column A.
And uses that number to populate the categories combobox.
This means that you can add categories by simply typing in column A.

I didn't write it but you could use a sheet change macro to create the table for any new category.

The second macro runs when you change the categories combobox
It counts the number of products in the selected category and populates the second list box accordingly.
This means that you can add categories by simply typing in the data column.

Please Login or Register  to view this content.

**newqueen** · 09-05-2014, 08:04 AM

Hi John,

Thanks very much. I am truly sorry that my request (my Post #14) has caused you difficulties. It wasn't intended. I am appreciative of all the time and effort that you have given to this.

I have looked at the new approach you adopted (re your post #15 above). While it is good, I believe that it would be too complicated for me. My knowledge of vba is very much at the baby stage or, at best, non-existent.

Infact, your solution (re your Post #9 above) is very neat and, in my view, simple enough for me to work with. Moreso, if there is the need for new categories and their related items, they can be added easily without going through a macro.

Everything about your solution in your post #9 above is very good. My only additional desire was to explore how, if it is possible, I can use a VLOOKUP formula (instead of a vba code) for Cell K5 ONLY so as to output the product price into that cell. If that was possible, that is the only change that I was requesting for.

It would be great if I can get and use a workable VLOOKUP formula for Cell K5. I would still like to explore that option. If I can get help with that, that will be fine. The fact that you used a vba code for that purpose does not, in any way, diminish the brilliant work you did. And I remain very grateful to you .

Kind Regards.

Newqueen

**newqueen** · 09-06-2014, 07:34 AM

Could someone please help with the VLOOKUP part of this (See post #14 above).

Thanks.

**jaslake** · 09-06-2014, 11:07 AM

Hi Clare

Please note, Post #15 is NOT my input

Attached are two samples of how you may accomplish the VLOOKUP.

In BOTH Sample Files this Dynamic Named Range myTable has been added

Formula:

Please Login or Register  to view this content.

In SAMPLE File:

Combobox2, Properties, Linked Cell has been set to L5
Cell K5 has this Formula
Formula:
```
Please Login or Register  to view this content.
```
The Sheet Code has been modified

In SAMPLE v1 File only the Sheet Code has been modified.

Pick your poison.

**newqueen** · 09-06-2014, 05:43 PM

Dear John,

I'm so very sorry for the mix-up! Pardon me, please.

Thanks too for coming back to me with the two additional options. After trying the two new options ('poisons' according to you), and noting the problems with each of them, I think I might just stick with your all-vba and flawless solution (re your post #9).

In my view, it appears that the VLookup formula cannot be successfully used with two dependent ActiveX Control Comboboxes.

Just for information - The issues that I found with the two options are as follows:

Option 1: This option incorporates the Vlookup formula that I desired. However, when you choose a category from combobox1, the category name appears on the box. That's fine. But when you choose its related item from combobox 2, NOTHING appears on combobox2. Rather the item appears only in the linked cell (which is the source of the vlookup). Given that the linked cell would normally be hidden, it would have been nice if the item chosen from combobox2 would appear both in combobox2 and in the linked cell.

With combobox2 remaining blank when an item is chosen from it doesn't look nice. With this option, the ideal situation would be that both combobox2 and the linked cell (L5) should only go blank when a new category is chosen from combobox1.

Option 2: This option is all vba as in #9. When a new category choice is made from combobox1, the contents of combobox2 are cleared (which is good)! But K5 is not cleared until a choice is made from combobox2. This was very well taken care of in your post #9 where combobox2 and K5 automatically become blank upon choosing a new category from combobox1.

Therefore, in the absence of any method of nicely linking a Vlookup formula to two such comboboxes, I'll stay with your original all-vba option (#9) which I found very good, and it did the job neatly.

Again, thanks very much for all your help. You've been very wonderful.

I also wish to thank my good friend Mehmetcik for his input. Grateful.

newqueen

**mehmetcik** · 09-06-2014, 06:30 PM

You don't need a vlookup.

This is all you need:

Please Login or Register  to view this content.

Populating Combo Box2 based on a selection made from Combo Box1

LinkBack

Thread Tools

Rate This Thread

Display

Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Re: Populating Combo Box2 based on a selection made from Combo Box1

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Combo Box2 selections available based on Combo box 1 selection

combo box input range based on the other combo box selection

Excel - populating data (checkboxes) based on combo box selection

Populate a Combo Box based on the selection of another Combo Box

[SOLVED] Select Row in List Box with 2 columns - Col 1 populates form 1 box1,Col 2 pop box2

Bookmarks

Bookmarks

Posting Permissions