VBA drop-down box

[callum91]

Hello, been musing around the forums a little asking questions and giving advice - now I have a new query which I've been stuck on this afternoon.

I have 10 cells in a row going down, each one of those 10 cells have the same data validation in - a drop-down list with values in. Below those drop down boxes is a blank table with three columns (item code, item description and quantity). On a separate sheet, I have each of the options in the drop-down boxes as a separate table - each showing the different item, description and quantity.

Essentially, I need a macro that will take the value from one, a few or all of the drop-down boxes and return the relevant data for the chosen option in the blank table at the bottom.

Is this something which is possible? I nearly make break-through ideas with it, but then something lets it down last minute.

Callum

[6StringJazzer]

Can you attach your file? Include sample data, and show an example of the result you want (you can fake the results). Explain how you would get that result from a logic standpoint, and we can explain how to implement that logic using Excel features.

There are a few confusing things about your description:

You have 10 dropdown lists, but only three item attributes. How do you use the 10 lists to identify criteria to look for?

You want to "return the relevant data for the chosen option in the blank table". How do you return anything from a blank table?

[callum91]

Hello and thanks for the reply. Perhaps I should have explained my query in a more coherant manner!

When I said that there are 10 drop down lists, this means that the user can choose up to ten separate choices. The data that is returned has only three attributes. the hope would be that either formula or a macro in the blank table would recognise the choice and return values from in the information on the separate sheet.

When I said "return the relevant data for the chosen option in the blank table" I meant to say that once a user has made a selection in a drop down box, data for that option (kept on a separate sheet) is then copied into the blank table at the bottom of the sheet.

I have attaced the spreadsheet to this e-mail. It does contain a macro but no need to enable anything as it's not required to show my problem. I've written labels on the front page to help take you through what I need to do.

Many thanks, Callum.

[meabrams]

I think that this will do what you want it to… Mind you it is not the prettiest coding but it works with the sample sheet you posted

Please Login or Register  to view this content.

If anyone can clean up coding feel free to post a better way

[callum91]

That is excellent and it does exactly the job I need, so thank you!

I can't really comment on cleaning up the coding as when I write VBA code, I tend to write everything out in full so that I can break down each step and insert comments so I know which bit does what. It looks messier and perhaps more complext than it needs to be, but still fully usable and good for a novice to intermediate user.

New to the site but I have given you a reputation point.