Inter-relating Combo Boxes in Userforms based on data in tables

Hi Everyone,

I was wondering if any of you lovely lovely people could help me at all?

I have worked out (through reading and what not) how to link a combo box to a range in a worksheet.

What I need to do is relate what is selected in that combo box, to another combo box which picks from another range.

I'll explain.

Basically I have a data spreadsheet called "Offices"

It's set up as follows

(Sorry, it's really frustrating trying to format it to a table type thing but you get the gist)

ROW NUM______COLUMN A____________COLUMN B
---------------------------------------------------------------
[email protected]
---------------------------------------------------------------
2_________________________________OFFICE 1
3_________________________________OFFICE 2
4_________________________________OFFICE 3
5_________________________________OFFICE 4
---------------------------------------------------------------
[email protected]
---------------------------------------------------------------
7_________________________________OFFICE 2
8_________________________________OFFICE 3
9_________________________________OFFICE 4


The dashed lines just show borders around cells so you can split them up.
The solid lines i've had to put in to space it out...
Column B rows 2 to 5 are named as range "DistrictOffice1_List" and Column B rows 7 to 10 are named as range "DistrictOffice2_List"

Now, in my userform, lets say its called "InputScreen".
I have a combo box called "DistrictOffice" and below that I have "Office"

I want the DistrictOffice combo to show all the district offices to pick from column A excluding any cells that aren't filled in i.e. rows 2-5 and 7-9.

Then I want the Offices combo box to automatically populate with all the offices for that DistrictOffice sourced from column B i.e. based on DistrictOffice selected so that if "DistrictOffice1" were selected in the "DistrictOffice" combo, the "Offices" combo only shows "OFFICE5, OFFICE6, OFFICE7".

If anyone can help that would be amazingly helpful!!! Thanks in advance :-)

HI, and welcome to the forum. If you could attach a copy of you workbook. It probably would be easier for the members to see what you are trying to accomplish.

Hello Smurlos,

Welcome to the Forum!

I have added the code below to the attached workbook. I changed you listing on the "Offices" worksheet by separating each Office Group with a blank line. This is not only provides better eye train on the form, it is also makes programming easier.

User Form InputScreen Code

Please Login or Register  to view this content.