How do I use VBA to choose the source cells for a list with Data Validation?

Normally in Excel, when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells.

For example:

There will be 3 lists with drop-down menus using Data Validation.

List 1:
Choose first option in list -> List 2 uses source A
Choose second option in list -> List 2 uses Source B
etc...

List 2:
Choose first option in list -> List 3 uses source C
Choose second option in list -> List 3 uses source D

... as so on...

I hope this makes sense.

Thank you for your time.

Hello baconcow,

Here is the code to change a Validation List's range. You will need to change the cell addresses to match yours.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thanks Leith Ross,

I will go a little more into depth on what I want to do.

Worksheet name: "Product Database"

List 1: "Choose Category"
Cell location: B6
Original source: J2:J12

List 2: "Choose Product"
Cell location: E6
Original Source: L11

This is what I want to do when the the ActiveX button "Update" is pressed, but I do not know how to translate it all into VBA code:

- If(data in cell location B6 for list 1 is = cell J2 or "Choose Category", the validation source will be modified to cell L2)
- If(data in cell location B6 for list 1 is = cell J3 or "Bulk Products", the validation source will be modified to cell L2:L7)
- If(data in cell location B6 for list 1 is = cell J3 or "Bulk Products", the validation source will be modified to cell M2:M4)

etc...

I am not sure how I would integrate the code you gave me into everything:

Please Login or Register  to view this content.

Would I use;

Please Login or Register  to view this content.

or...

Please Login or Register  to view this content.

or something completely different?

I really appreciate your prompt help, Leith!

Thanks again,
Shawn

Hello Shawn,

I think it would help to see your worksheet as I am a little confused about the results you want. You will need to zip the workbook before you can attach it to your post.

Thanks,
Leith Ross

Hey Leith,

Right now, the workbook is pretty barebones. The rest of it is on another workbook as it is not needed at this point. What I want to be able to do is use the pulldown columns to narrow to a specific product located in the sub-product lists. Given this, there will be information displayed about the currently-chosen product. I have colour-coded the columns to show you where I want the source lists to go.

All of the information that I have kept in this database is publicly open and can be found at our website (www.dynonobel.com), if you're wondering what it is.

I hope that this helps you with figuring out what piece of code will work in my situation. Thank you for all your help!

Shawn

Hello Shawn,

I can't connect to the website for some reason and I don't have Excel 2007 so I can't open the workbook. Could you zip a copy in Excel 2003 format for me?

Thanks,
Leith Ross

Hey Leith,

Here is the file in .xls format.

Shawn

Hello Shawn,

Thanks for posting back with the file in 2003 format. After looking at your worksheet I see you have groups and sub-groups. I would like to know more about how you would like this interface to work. I feel you may need more capability than what a Data Validation drop down can provide.

Sincerely,
Leith Ross

Hey Leith,

For right now, I got the following code to work...

Please Login or Register  to view this content.

In the end... I want the final product that shows up in the "Sub Product" category to be that whose stats are output for the user. I will have a listing of all of the products main parameters (explosive quantity, weight, size, etc...) so they can be looked up easily. This would save a lot of time from having to look through the hundreds of product sheets and MSDS's all of the time which are multiple pages each.

Hello Shawn,

Here is an easier way to code all that information...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Nice optimization. Can I somehow equate the cells where the words are located instead of saying "product_name"? For example, saying:

Case Is = J2

etc...

Thanks again. This is one of the most helpful forums I have ever been part of. I hope that I can learn enough to be helpful to others.

For those curious, I found that this worked:

Please Login or Register  to view this content.

It is very useful if you decide to modify the text in the brackets and do not want to update the code. Changing the cell reference in the code is much quicker and makes things easier to track, I find.