asked on

Excel dynamic combobox

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!

Saqib Husain

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

StephenJR

Simple example in attachment. Change the first combobox and see what happens. It used named ranges for your teams.
Book5.xls

hkgal

ASKER

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

hkgal

ASKER

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..

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hkgal

ASKER

Very timely and helpful

StephenJR

Glad it worked.