VBA to check for data validation selection

[bdepolo]

Hi Everyone,

I have 3 data validation boxes that each draw their choices from each other. So the user will select a category in H2 which will decide the available choices in I2 which will then allow certain choices only for J2. I wanted to make it so that when H2 is changed I2 and J2 are cleared out. I've tried doing it through the SelectionChange Event, but with data validation it seems like just clicking in the cell runs the SelectionChange event. Is there another way this could be done? If I can provide any further details that would help let me know.

Below is my SelectionChange code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("H2:H75")) Is Nothing Then
    
        If ActiveCell.Validation.Type = xlValidateList Then
             ActiveCell.Offset(0, 1).Value = ""
             ActiveCell.Offset(0, 2).Value = ""
        End If
        
    ElseIf Not Intersect(Target, Range("I2:I75")) Is Nothing Then
    
        If ActiveCell.Validation.Type = xlValidateList Then
             ActiveCell.Offset(0, 1).Value = ""
        End If
        
    End If

End Sub

Thanks in advance

 

[Andrew Poulsom]

Have you tried the Worksheet_Change event?

 

[bdepolo]

That seems to lead to the same result. Just clicking into the cell with the validation triggers the macro to run and clear out the other 2 cells. I'm not sure there's a way around it since I don't want anything to run unless a change is made to in the cell value.

 

[Andrew Poulsom]

The Worksheet_Change event won't fire when you just click in a cell.