Data Validation

[mrhilder]

I have 4 cells all beside each other in my Excel Spreadsheet. They
are all data validations that are linked to one another. I.E. each
depends on the input from the previous cell. I'm looking for a code
that will clear the cells (but keep the data validations) if I change
one of the previous cells. For Example: Cell 1 contains these
items: Shovel, Axe, and a Saw. If I choose Shovel in the first cell
then my options for the second cell are now Flat or Round. Then the
third box will give me the price depending on which I choose.
However
the problem is that once I pick the Flat and the price shows up, If I
choose that I would rather have the Round and I go back and select
it. The same price that the Flat shovel still shows in its cell even
after I select Round until I go and physically do the drop down menu
again for its price. I'm looking for a code that once I change my
mind and go with the Round, then the next cell otimatically goes back
to blank as if I havent ever clicked on it. Hope this makes sense.
Any help is greatly apprieciated

 

[T. Valko]

Assume your drop downs are in the range A22

Select the sheet where you want this to happen.
Right click on the sheet tab and select View Code.
Copy/paste the code below into the window that opens.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A2")) Is Nothing Then
Range("B22").ClearContents
End If
If Not Intersect(Target, Range("B2")) Is Nothing Then
Range("C22").ClearContents
End If
If Not Intersect(Target, Range("C2")) Is Nothing Then
Range("D2").ClearContents
End If
sub_exit:
Application.EnableEvents = True
End Sub

Return to Excel - hit ALT Q or click the top "X" to close the window.

Try it out.

 

[Dave Peterson]

First, this may not work in xl97.

xl97 has a bug that under certain conditions, the worksheet_Change event won't
fire.

Debra Dalgleish explains it in more detail:
http://contextures.com/xlDataVal08.html#Change

Anyway, you could use code like this that fires each time you make a change to a
cell in that worksheet.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myAddresses As Variant
Dim aCtr As Long 'Address Counter
Dim nCtr As Long 'next counter

'change this to match your range
'A1 controls B1 which controls C1 which controls E1
myAddresses = Array("a1", "B1", "c1", "e1")

'one cell only
If Target.Cells.Count > 1 Then
Exit Sub
End If

On Error GoTo ErrHandler:
For aCtr = LBound(myAddresses) To UBound(myAddresses)
If Intersect(Target, Me.Range(myAddresses(aCtr))) Is Nothing Then
'not in that range, do nothing
Else
Application.EnableEvents = False
For nCtr = aCtr + 1 To UBound(myAddresses)
Me.Range(myAddresses(nCtr)).ClearContents
Next nCtr
End If
Next aCtr

ErrHandler:
Application.EnableEvents = True

End Sub