ClearContents when a different cell changes

[itsjoje]

Hi, First I want to say i'm new to this forum and already it has been a great help. Looks like there are a lot of really experienced people on here.

I have a few cells that use data validation to generate a drop down lists from ranges elsewhere on the sheet. Some of those cells are dependent on previous cells. I have borrowed some VBA script from another post that clears the contents of dependent cells when a higher level item is changed. The only thing i'd like to change is that it clears the dependent cells even when you re-select the same choice from a higher level drop list. anyone have any ideas?
Here's the code i'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c7")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(4, 0).ClearContents
Target.Offset(6, 0).ClearContents
Target.Offset(8, 0).ClearContents
Application.EnableEvents = True
End If
End Sub

Thanks again,
Jon

 

[wolverineb]

Welcome to the forum Jon.

Can you explain a little more? It looks like your code would clear out C11, C13, and C15 if C7 changes.

Are you asking to clear those same cells even if C7 doesn't change?

 

[wolverineb]

If that's the case, you might try using the exact same code but change the name of the macro to Worksheet_SelectionChange and see if that does what you want.

 

[itsjoje]

Thanks for the quick response. the code does what it is supossed to. It clears the contents of C11, C13, and C15 when the contents of cell C7 change. Cell C7 is a drop list generated through the Data Validation tool. The only problem I'm having with the code is that if the user has already selected an option from the drop down list and they click in cell C7 again and select the same option from the drop down list; excel will see that as a change and clear the contents of cells C11, C13, and C15. While to the user they did not make any change. I'm probably just being picky, but it would be nice if it only cleared the lower cells when the selection on cell C7 changed to a different option.

 

[wolverineb]

Ah. I thought you were asking for it to do that. To stop it, you can have the code undo the action, check to see if it's the same, and then redo it, skipping the clearing steps if it wasn't really a change.

I tested this and it worked on my machine:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myOldValue, myNewValue
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("C7")) Is Nothing Then
        myNewValue = Target.Value
        Application.Undo
        myOldValue = Target.Value
        Target.Value = myNewValue
        If myOldValue <> myNewValue Then 'this was a "real" change
            Target.Offset(4, 0).ClearContents
            Target.Offset(6, 0).ClearContents
            Target.Offset(8, 0).ClearContents
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[itsjoje]

Awesome, That worked like a charm. Thanks so much Wolverineb