Two Combo boxes on one UserForm

[NonSira]

Hi! I'm new here, please forgive me if I post this thread at the wrong place.

I'm working on a UserForm with 2 combo boxes which are named "Room Type" and "Room No". What I want to do is when I select something in "Room Type", it limits the choices to be displayed in "Room No.".

EXE (Executive Suites) = 1, 2, 8, 9
JUN (Junior Suites) = 3, 5, 7
DLX (Deluxe Suites) = 4, 6

I created a table in a worksheet called "ROOM" with 2 columns, column A is for room types. Column B from B2 to B100 are room no. for 901 to 909, 1001 to 1009, 9 rooms each floor up to 2009.. So, if column B = 901, column A = EXE

For example;

Combo box "Room Type" = DLX
Combo box "Room No." displays 904, 906, 1004, 1006, 1104,1106,... up to 2006

If you get my point, please give me some clue. Thank you

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[NonSira]

Sorry for not attaching any file, I will create a file and attach next time.
Nontpakorn

[royUK]

Use the post reply button, then go advanced. Scroll down the page & you will find a button labelled manage attachments,click it & browse for your folder

It depends how you are adding the lists to the Comboboxes for the best way to do this

[NonSira]

Hi!

I have done a very simple file and I think you could understand it perfectly!

You can see that I put 2 combo boxes on the UserForm and they show data from the selected sources. When I select Room Type "EXE", I want the Room No. combo box to show only the numbers that belong to "EXE" category.

Appreciated every assistance

Nontpakorn

[romperstomper]

There's no file attached.

[NonSira]

Ah! I missed again.

It should be good this time.

[romperstomper]

The code for your form becomes something like this:

Please Login or Register  to view this content.

[snb]

For this task you only need 1 combobox. See the attachment.

[NonSira]

Thank you for all your kind assistance.

@romperstomper

An error displayed after I tried selecting the Room Type

"Run-time error '-2147467259 (80004005)':
Unspecified error

What does it mean?

[NonSira]

snb,

I prefer to have two separated controls. But thank you so much for your effort.

[romperstomper]

Don't know (it's unspecified! )
It worked fine in my test - are you using it in a different workbook than the one you posted? If so, can you test it in the one you posted, please?

[NonSira]

I tried it on the same workbook. I thought it was because Windows 7, but after I tried it on XP PC I got the same problem.

[snb]

I think our users will prefer 1 combobox.
If you choose EXE you will see all EXE rooms. If you want to change to JUN, you select the first item EXE, then you will see the original list with EXE DLX and JUN. If you choose JUN you'll get the list with JUN rooms.

[NonSira]

@snb

When we make the hotel reservation, we have to choose the room type but we don't know yet what room no. That's why I want them to be separated

[snb]

There's no obligation to choose a roomnumber

In the code for the commandbutton:

Please Login or Register  to view this content.

That means: in columnB there will be the room type; in column C the room number

[romperstomper]

Sorry, I just twigged the problem. I forgot to mention that you need to clear the RowSource property of the cmbROOM control.

[NonSira]

Thank you for both of you, both of your suggestions work perfectly!