Cell Changes with Dependent Validation

[bruleto]

I've spent the better part of the morning searching this group for an
answer to my question will little avail.

My spreadsheet makes use of dependent lists. The user selects one of
three items from a list in cell C11 and their selection dictates the
contents of the list in cell D11. This functionality works perfectly.
My problem is that when a user changes the contents in cell C11 I want
the values in cells D11 and E11 cleared so that they are forces to re-
choose from the list. Cell E11 contains a list but it is not dependent
on the value of another cell.

I was looking into change event macros but I'm not sure about syntax.

I hope I did a decent job explaining. Thanks for any help you can
provide.

 

[Dave Peterson]

Rightclick on the worksheet tab that needs this behavior. Select view code.
Paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:
If Intersect(Me.Range("c11"), Target) Is Nothing Then
'do nothing, not in C11
Else
'change to C11, so...
Application.EnableEvents = False 'don't fire the _change event again!
Me.Range("d11:E11").ClearContents
End If

ErrHandler:
Application.EnableEvents = True

End Sub

 

[bruleto]

Rightclick on the worksheet tab that needs this behavior.  Select view code.
Paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrHandler:
    If Intersect(Me.Range("c11"), Target) Is Nothing Then
        'do nothing, not in C11
    Else
        'change to C11, so...
        Application.EnableEvents = False  'don't fire the _change event again!
        Me.Range("d11:E11").ClearContents
    End If

ErrHandler:
    Application.EnableEvents = True

End Sub









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for the assistance. The macro works when I clear out cell C11
but I need it to do the same thing on C12, C13, etc. Do I need to used
named ranges? I completely stuck.

Tony

 

[Dave Peterson]

I don't know what you mean by etc.

And I don't know what cells should be cleared when you empty each of the other
cells--is it always the two cells over on the same row?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

On Error GoTo ErrHandler:
If Intersect(Me.Range("c11:c999"), Target) Is Nothing Then
'do nothing, not in the right range
Else
Application.EnableEvents = False 'don't fire the _change event again!
Target.Offset(0, 1).Resize(1, 2).ClearContents
End If

ErrHandler:
Application.EnableEvents = True

End Sub