Excel 2007 : Translate worksheet dropdown list actions to VBA for a userform

[BigGPL]

Morning

On the attached sheet, the idea is to select a state from the first dropdown list. then the second dropdown list is populated with the cities of the state selected. The third dropdown list is populated with the airports associated with the city selected.

Works fine.

Instead of the dropdown lists being on a worksheet, I would like to have a userform with comboboxes for the State, City, and Airport. Then on the worksheet D11:D13 would be filled with the selections from the userform.

The problem is that I have no idea as to how to code in VBA the actions of the worksheet dropdown lists.

Data validation for the dropdown lists:

The state: = a range named States
The city: =INDIRECT(B3)
The airport: = INDEX(Sheet1!$P:$P,MATCH(Sheet1!$C$3&"|"&Sheet1!$D$5,Sheet1!$N:$N,0),1):INDEX(Sheet1!$P:$P,MATCH(Sheet1!$C$3&"|"&Sheet1!$D$5,Sheet1!$N:$N,0)+COUNTIF(Sheet1!$N:$N,Sheet1!$C$3&"|"&Sheet1!$D$5)-1,1)

I would appreciate any help with the coding.

George

[BigGPL]

The attached has some of the kinks kinda worked out.

Still need a bit of help on the VBA syntax for INDEX MATCH

I need to convert:

Please Login or Register  to view this content.

to VBA.

I tried this:

Please Login or Register  to view this content.

but am getting compile error Sub or Function not defined.

Been struggling with this since 6am. I have no idea of what I am doing. HELP!

George

[royUK]

Not sure if this what you mean.

I've tidied up the code, but you can't use worksheet functions like that

In future post links to all forums with cross posts!!!