Dynamic range acoording to sheet and Material Name

[Mamud]

How can I make a dynamic Range,
I have each material in a separated sheet, I need when I select the Material (in combobox "cmbName") make the supplier name (in combobox "cmbSupp") goes to its range automatically according to its sheet

ex. If I select from "cmbName", the value "Leucovorin Calcium", I want "cmbSupp" goes to the range in sheet2 "rngSuppLec"
and If I select another value like "Paclitaxel", "cmbSupp" goes to the range in sheet3 "rngSuppPac", and so on


any help will be appreciated

Thanks...

[Mamud]

Hello

Any Help

[Yraen]

This will go in the Userform2 code module:

Please Login or Register  to view this content.

[Mamud]

Thanks Yraen

but If I have alot of materials in my list, It will be more difficult to add its ranges in your code.

Is it a way to make the code recognize automatically the ranges according to the material selected

Thanks Again

[Yraen]

I'll get you something up in a few hours that will take care of any amount of items.

[Mamud]

waiting you, yraen

thanks

[Yraen]

Here's the final sheet. I've added a sheet called "Settings". On this sheet you will add each new product into Column A and give it a column all to itself with the product on row 1 of this column. On each product column you can add whatever you want to appear in the drop down when it is selected. Run Userform2 to see it in action.

Copy of WH.xlsm

[Mamud]

Wow, amazing Yraen

if you don't mind, Can I make that post open, coz sure I'll need your help in that project?

Thanks ...

[Yraen]

You're welcome. I'm glad it's working for you. I'm not sure what you mean by "Can I make that post open" though. If it's keeping the post open instead of marking it solved, I'd mark it solved and any new problems just open a new post. If for some reason you can't get an answer and I don't see it (I tend to only look at the first couple of pages for posts with 0 replies) you can shoot me a message and see if I can solve the problem.