drop down for two columns at same time

The code below will use one list to lookup values in another list.

The first code is manual the second is automatic.

Sub BuildList()
'By Joe Was
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Automatically correct list for additions!

If Not Intersect(Target, Range("B1")) Is Nothing Then
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
Else
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End If
End Sub

To use the code!
Select an item in the dropdown in B1 (Like the: "State Name")
H column contains the list names for the lists in columns I, J & K, H ... would be the "State list range name" for the "Cities list" in column I, J & K ... or more columns as needed for each of your sorted lists?
The list selected in the B1 dropdown will set the D1 dropdown values: (Like the "Cities [D1]" in your selected State [B1]")

So you have one dropdown in B1 and the other in D1. When you select from the dropdown in B1 your selection loads the "range name" for which list to use for the dropdown in D1 dynamically. Hope this helps? JSW

P.S. The Private Sub above automatically checks for a change in B1,s navagation list value [which become the range names for each list named by B1[ and runs the macro to update the D1 selection list.

Thanks but that is way beyond my scope of understanding

Lets take it slow:

First build a dropdown [Data - Vailadation: list, then type each "Class" name in the box, use a comma between each "Class" name. Put a border around the cell that has the dropdown, so you don't lose it. In a cell above or in front of this dropdown add a label like: Class ==> if the label is to the left of the dropdown.

On the same sheet as your dropdown move to the Right say to Column: AA.
Starting in the first row of Column: AA list the "Items" in that class one item to each row of that column.

Then select that column and from the menu [Insert - Name - Define, in the box type the "Class Name" for that list. The list is now a named range, named your class name.

Do the same for each of the "Class-Items" lists.

Now build your other dropdown as before, only this time put the cell address for your first dropdown in the list values box of the dropdown tool.

Block Copy this code from here [Select code, Right click selection, select copy]

Then on the sheet that has the dropdowns, Right click the sheet tab and select "View Code." In the code window Right click and "Paste" the code.

Change the cell addresses in the code to the cell addresses of your dropdowns and change the sheet names if you need to. Click the top close "X" not the one below it, to return to your sheet.

Select the "Class" dropdown and then select your "Item" dropdown. The "Item" dropdown will now contain all the items found in the list you selected in the first dropdown. Each tine you select a new class in the first dropdown the list of choices in the second dropdown gets replased with the corect list!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Run from Sheet module.
'Automatically correct list for additions!

'This is the class dropdown location.
If Not Intersect(Target, Range("B3")) Is Nothing Then

'This is the items dropdown location.
Worksheets("Sheet1").Range("D3").Value = ""
myDataState = "=" & Worksheets("Sheet1").Range("B3").Value
With Worksheets("Sheet1").Range("D3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
Else

'This is the class dropdown location.
myData = "=" & Worksheets("Sheet1").Range("B3").Value

'This is the items dropdown location.
With Worksheets("Sheet1").Range("D3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myData
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End If
End Sub

Hi roland and Joe.
For another example of how this can be done you can check out this link where Aladin gives an example of a different approach to making dependant dropdown lists.
http://www.mrexcel.com/board2/viewtopic.php?t=53213
I've never seen it done the way Joe did it here and I think this is really, really cool. (with a nice explanation too!)
Between these two there shouldn't be anything a body can't do.
This is very nice Joe. Thanks.

Dan

Dan,
Both Aladin's and my way are set-up the same and work much the same way the difference is my VBE code checks for an addition to the dropdown'd data lists, and updates the range name to include any additions or deletions automatically. This way your lists are automatic. You just go to the sheet column where your list is and add the new item and the code does the rest.

I found adding lists to the data valuation list box, makes it hard to edit due to its small window. And, like to see my lists on a sheet, if needed it also gives update ability to the user, where data valuation up-dating by the user may be difficult.

Data validation: Can macro only delete B if A changes?

This macro posted by Joe Was works nice, but selecting cell B3 automatically wipes out the value in D3. I would prefer if it this would only happen in the value in B3 is changed. If the used simply clicks on B3 but doesn't change the value, the value in D3 still gets deleted. Any ideas?
Thanks!
Tracy

Hello Tracy,
I haven't tested this but if you change from using the Worksheet_SelectionChange event to using the Worksheet_Change event I think you should get what you're after.

(ie.) Change the line:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
to:
Private Sub Worksheet_Change(ByVal Target As Range)

Let us know if that doesn't help.

Still not working...

Unfortunately, it's still not doing what I want.

Is there a way to post a spreadsheet so you can see what I mean? If not, here is what my spreasheet looks like:
_A3________B3________C3__________D3
Major=>___Birthday___Minor =>___Sister BDY

The possible values in B3 are Birthday,Sympathy,Congrats. The data validation in cell D3 is =INDIRECT(B3) so it will reference a list of values depending on what is selected in B3. This works, but in the example above here is what I want to happen:
If B3 is changed to Sympathy, the value in D3 (Sister BDY) should clear out....BUT....
If B3 is selected or the the dropdown is activated, but the value doesn't change (i.e. Birthday is still the value selected), the value in D3 should not clear out. I don't want the act of just exploring the possible values to clear out data in another cell. Make sense???

Here is the code I am now using:
Option Explicit
Dim gPrevB3 As String

Private Sub Worksheet_Activate()
gPrevB3 = Range("B3").Text
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
If Target.Text <> gPrevB3 Then
With Application
.EnableEvents = False
Range("D3").ClearContents
.EnableEvents = True
End With
End If
End Sub

Thanks for your help!