Cascading Country Region Comboboxes on a Userform

**jnet** · 08-17-2011, 06:38 AM

Hello all, this is my first post so apologees for any lack of succinct description.

This has been running around my head for two days now and its about time I sought help.
In an attempt to find similar threads I attempted to edit the workbook posted in this thread
http://www.excelforum.com/excel-prog...x-vb-help.html
but found that the code crashed excel perhaps du to the size of the list i am using to import (to the order of 4990 rows)

I would like my userform comboboxes to cascade so that the second filters the data from the first to only display regions from the country selected in the first.

I am aware there are many available questions on this topic, but I have found none that contain as many rows of data as mine, the code for this needs to be streamlined to this particular application as I believe. and it is far beyond the scope of the VBA and Macros for Excel book I have on my desk!

Any help would be appreciated

see workbook as attached, Post #5!

**Richard Buttrey** · 08-17-2011, 06:40 AM

Hi,

Many of us are reluctant to visit other sites to download files. Please upload your file to this forum.

Regards

**jnet** · 08-17-2011, 07:11 AM

Sorry but i did just try another 10 or so times and keep getting a 'database error'. the upload link isnt dodgy honest

I'll keep trying to upload but am unsure of what this problem is.

**Richard Buttrey** · 08-17-2011, 07:33 AM

Hi,

It worked for me a few moments ago. How big is the file? We only need a small subset of your data so delete most of the rows but leave as many as are necessary to show all permutations.

Regards

**jnet** · 08-17-2011, 07:49 AM

it was happy to upload it as long at it had the s*** zipped out of it! sorry!

**Richard Buttrey** · 08-17-2011, 08:01 AM

Hi,

I can't test that since your country list and maybe others are not present.

Are you sure you really need VBA Userforms to do this? Why not just use standard Excel drop down lists laid out in a similar manner to your UserForm?

Have you checked out Debra Dalgleish's web site http://www.contextures.com/xlDataVal02.html which will tell you anything you need to know about dependent drop down lists.

Regards

**jnet** · 08-17-2011, 09:50 AM

See A73 you have to scroll down a bit
I would like to use userforms for this, I feel as though the people using this spreadsheet when its finished would appreciate the professional layout and ease of use.

I will have a read of that website, but it seems orientated towards non-userform applications. I am unsure how to cross-implement that although I have heard it is easy.

**Richard Buttrey** · 08-17-2011, 10:16 AM

Hi,

I realise you have data starting at A73 but I can see no lists of unique countries or locations nor indeed any code which would generate such lists.

Before you can use the .RowSource property (or indeed .List or .AddItem methods) to fill your Comboboxes with drop down values, you need to tell them where the data is that they should use.

That's why I suggested using Debra's site to understand how dependent lists are created and managed. One way or another you will first need to understand how to generate these lists, either manually if they are static or by macro if they change.

There's no reason why data entry on a sheet can't be equally as professional looking as a UserForm and in my experience it's much quicker to develop.

One other consideration is whether users may use Excel for Mac. If they do then be aware that syntax like .RowSource is unavailable to the Mac version of Excel and workbooks that use it will crash. There are one or two other areas where the Mac version is different to the Windows version and means that your workbooks may not be fully portable.

Regards