Clearing Drop Down Selection

[MrBold]

I have two data validation drop down boxes that are linked. 1. Select brand.
2. Select Colour . The colour options list (box 2) is different depending on
which brand is selected.
It works great when starting from a blank sheet, but after making a
selection in both 1 and 2, if you go back and select a different brand in box
1, it will still display the colours from the prevoius brand selection in box
2 and result in an error in the total column.
Is there any way I can get box 2 to clear when a new selection is made in
box 1?

Thanks for your help.

 

[Jim Rech]

You can only clear the cell by using a Worksheet_Change event macro. Here's
an example for Excel 97 that is still valid for newer versions that explains
the basic idea:

http://support.microsoft.com/kb/305565/en-us

Rather than format a cell as in the example you'd want to clear it, sort of
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Range("FirstCell"), Target)
If Not r Is Nothing Then
Application.EnableEvents = False
Range("SecondCell").ClearContents
Application.EnableEvents = True
End If
End Sub


--
Jim
|I have two data validation drop down boxes that are linked. 1. Select
brand.
| 2. Select Colour . The colour options list (box 2) is different depending
on
| which brand is selected.
| It works great when starting from a blank sheet, but after making a
| selection in both 1 and 2, if you go back and select a different brand in
box
| 1, it will still display the colours from the prevoius brand selection in
box
| 2 and result in an error in the total column.
| Is there any way I can get box 2 to clear when a new selection is made in
| box 1?
|
| Thanks for your help.
|

 

[Debra Dalgleish]

Another option is to create cross-dependent validation lists, as in the
sample workbook here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0013 - Cross Dependent Validation Lists'