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.

Range("L1").Validation.Modify Type:=3, Formula1:="=$J$1:$J$5"

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:

Range("B6").Validation.Modify Type:=3, Formula1:="=$L$2")
Range("B6").Validation.Modify Type:=3, Formula1:="=$L$2:$L$7")
Range("B6").Validation.Modify Type:=3, Formula1:="=$M$2:$M$4")

Would I use;

If(Worksheets("Product Database").Range ("B6")="Choose Category"

or...

If(Worksheets("Product Database").Range ("B6")="Choose Category"

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