How do I setup a cascading combo boxes?

B

Brian

I have a user form with 3 combo boxes that I would like to set up to narrow
down information on a worksheet in the same workbook as the User Form.

Can anyone help me or guide me on how to do it?

Data in Worksheet
--------------------------------
GEORGIA OFFICES
CLLI NAME ADDRESS 1 ADDRESS 2 CITY STATE ZIP GLC Code T-Base

Data is layed out across the worksheet.

Data in Worksheet
--------------------------------
Clli: ACWOGAMA
Add 1: 4745 Logan Road
City: Acworth
St: GA
Zip: 30101
GLC: F5341

Clli: AGSTGAAU
Add 1: 3523 Washington Street
City: Augusta
St: GA
Zip: 30907
GLC: R3547

Clli: AGSTGABM
Add 1: 1490 Ellis Street
City: Augusta
St: GA
Zip: 30902
GLC: R6341
 
J

JLatham

On second thought, that page at Contextures may not be what you want. I
realized after posting you're speaking of combo boxes on a UserForm, not data
validation lists on the worksheet.

To really help, it would be useful to know what you intend to use each combo
box for, and what a change in one of them would do for the next one in the
sequence.

You'll (probably) want to set them up initially in the Form's Initialize
event, then alter the source rows for the 'downstream' combo boxes based on a
change in the upstream boxes.
 
B

Brian

I have a Workbook "Master User Form" Worksheet (Legacy B) that has all the
Data on it.

My combo boxes are as follows:
Combo box 1 = Customer_11 (Approx 8 customers)
Combo box 2 = State_11 (Approx 9 States)
Combo box 3 = CLLI_Code_1 (400 Offices per State)

My goal is
The user to Select from Combo Box 1 (Customer_11)

That narrows the selection down to what state for that customer
which is Combo box 2 (State_11)

When combo Box 2 is selected that narrows down the selection to only the CLLI
codes Combo Box 3 for that customer in that state.

Now on my Worksheet all the data is in columns as shown below

Data in Worksheet
--------------------------------
GEORGIA OFFICES
CLLI NAME ADDRESS 1 ADDRESS 2 CITY STATE ZIP GLC Code T-Base

Each Customers Info is in the rows. Each row contains 1 customer.

If the Data needs to be rearanged another way tell me and I will change it.

What I planned was for each Customer to have there own Worksheet broke down
by State.

Example: Customer 1 That customer is in 9-States

States = AL, Fl, GA, KY, LA, MS, NC, SC, TN

CLLI = ATLNGACS Each state has approx 400 sites for that customer.

I want to do this the easiest way possible, so if that data has to be moved
around then the girl at the office has job security. LOL

Any help or sugestions would be greatly apprieciated.
 
J

JLatham

Brian,
I'm thinking that having all of the information on a single sheet, at least
the following:
Customer Name | State | CLLI
and having that list sorted by Customer name with second field sort on the
State.

Need another list of just unique customer names also, to populate
Customer_11 with.

With that setup, you can 'react' to a change in Customer_11 by then going
through the full list to find the Customer Name and rapidly fill State_11
with just a few .AddItem statements in a loop. Same for getting the CLLI
once the Customer and State have been chosen.

If you want to take this off-line and maybe send me a copy of the workbook
as you have it now, you can reach me at (remove spaces)
HelpFrom @JLatham Site. com

(Side note: CLLI makes me think of Telco network engineering - and I have
some experience with that from some years ago of doing custom code for SW
Bell --> SBC Corp and Ameritech. Support ended before name change to AT&T.
Of course you may be in a different industry and CLLI may mean something
totally different).
 
O

OssieMac

Hi Brian,

I have posted code on your other thread for this. However, I see that you
have been offered direct help by JLatham via email so go for it; afterall he
is an MVP so he could well provide a better answer than I have.
 
J

JLatham

"afterall he is an MVP so he could well provide a better answer than I have"
Ain't necessarily true!

I didn't see your code - might just be the ticket. And if you're that far
along with it, I think you've probably got a better grasp of his
worksheet/workbook layout than I do at the moment.

Can you post link to that other thread? I don't recall where it was.
 
J

JLatham

Nevermind, I found the thread.

OssieMac said:
Hi Brian,

I have posted code on your other thread for this. However, I see that you
have been offered direct help by JLatham via email so go for it; afterall he
is an MVP so he could well provide a better answer than I have.
Click to expand...
 
B

Brian

I sent you the user Form ith the Data sheets in it.

I think that 2 fresh eyes are definatlly better than mine.

OssieMac has been a huge help in my progression of this project. I am not
sure how you guys know all this stuff like you do. It absolutly amazes me of
the knowledge base you guys have. I can only hope to learn half of what you
guys know.
 
Top