Excel DropDowns

[Guest]

How do I restore default values in an excel drop down

Example: 1st drop down : NorthAMerica, Asia
2nd Drop Down: IF NorthAmerica is selected then( United
States, Canada)
If Asia is selected from the 1st
drop down(Japan, China)
1st run: if I select N.America from first drop down and select United
States from the 2nd drop down.

2nd run: I go ahead and select Asia in the 1st filter but the 2nd filter
remains United States and I have to manually go aheah and select Japan or
china

IS it possible that if I select Northamerica, it doesnt' show anything till
I go and manually select the 2nd filter?

SOrry for my long essay, but I am very desparate to get an answer. Any help
is appreciated!

 

[Guest]

Perhaps something like this:

Assumptions:
Parent List cells are in A1:A10
Dependent List cells are in B1:B10


'---start of code----
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range

Set rngAllParentCells = Range("A1:A10")
Set rngDepCells = Intersect(Target, rngAllParentCells)

If Not rngDepCells Is Nothing Then
For Each rngCell In rngDepCells.Cells
'Move 1 cell to the right and clear contents
rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
Next rngCell
End If

Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing

End Sub
'---end of code----

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro