We help IT Professionals succeed at work.

Excel dynamic combobox

339 Views
Last Modified: 2012-05-11
I have a userform in Excel that would like to populate items based on another hidden worksheet in the workbook, table structure like:

Team               Agent
1                      Leo
1                      Mike
2                       Tim
2                      Eric
2                       John

The first combox box can link up to the values in column "Team" (i.e. 1,2,..)
and when i select Team 1 in first combobox, The names of agents for respective teams will pop up in 2nd combobox (i.e. Leo, Mike)

Thanks!
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
You would need something like

private sub combobox1_change()
set ws=thisworkbook.sheets("sheetname")
combobox2.clear
for each cel in ws.range("A:A")
if cel.value=combobox1.value then combobox2.add cel.offset(0,1).value
next cel
end sub
Simple example in attachment. Change the first combobox and see what happens. It used named ranges for your teams.
Book5.xls

Author

Commented:
Hi StephenJR, ur sample is pretty simple. but I don't know why when I incorporate into my work, I was promopted with "could not set rowsource property"??

i attached my sample....pls help testing.xls

Author

Commented:
and actually i would like to hide the raw data worksheet and only shows "result" worksheet

but when i put:

sheets("result").activate

at the end of sub userform_initialize()....it doesn't work 2..
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Very timely and helpful
Glad it worked.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.