Combo box with dynamic list using VBA

[hsejar]

Hi,
I'm relatively new at VBA programming in excel. hopefully someone could help.I have 2 fields in sheet1 supplier number and name , using them i have to go to sheet2 which has the list of suppliers and materials. from this list i have to select the list of materials associated to this supplier and show them in the combo box.

i'm attaching the file for reference.

thanks

[mohd9876]

try this, Place it in Sheet1 Code module:

Please Login or Register  to view this content.

[tigeravatar]

hsejar,

Attached is a modified version of your workbook. First I turned the supplier number and name input cells in sheet1 cells C4 and C5 into drop-down lists using data validation. The lists refer to named ranges that are specified on Sheet2. In order to fill them, I used advanced filter -> copy to range -> unique only. I then set the combobox properties ColumnCount to 2 and TextColumn to 2 so that the combobox displays both the Material Number and Material Name and when an item is selected the combobox shows the Material Name. The rest is done via the Sheet1 Worksheet_Change event in VBA:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[hsejar]

tigeravatar,
Thanks for the great help. It exactly does what i need. Just one thing, wheni try to copy this logic into the actual sheet, when i change the vendor number in the sheet1, the code in the workbooksheet_change doesnt seem to start running. I tried putting brekpoints and tried running the code, but the flow doesnt seem to go into the code.

Is there any other settings i'm missing other than what you have mentioned.


Thanks
hsejar

[tigeravatar]

hsejar,

Are you changing the address to the correct cells for the Supplier Number and Supplier Name?

Please Login or Register  to view this content.

~tigeravatar

[hsejar]

Yes.
i tried doing it in a new .xlsm file and it works, with the same code.
but when i try to do it in my actual file for use.... it doesnt, i change the cell addresses c4,c5 to the relevant ones and the sheet names as well in the code....

and i copy the code in the sheet1 equivalent in my workbook. i have sheet1 as supplier_scorecard and sheet2 as supplier_material_translation. would it matter if sheet1 and sheet2 are not adjacent... in my workbook, i have 2 more worksheets in between.

because the actual file is too big, i cant attach them.

I will try to check. Thanks for all the help.

hsejar.

[tigeravatar]

It doesn't matter where the sheets are within the workbook. Code likely just needs to be adjusted to your actual dataset, including the Index/Match functions in the code.

[hsejar]

Ok Fine... wil check the index and the match functions once more. thanks for all the help.