Can I Link VBA clear contents macro to Cell Criteria from Dropdown selection

[Dazzybeeguy]

Hi

I want to allow the user to change a cell contents to a given Criteria

I wanted to add Validation droop down where they select Del in cell B9

I want to create VBA that on that action would clear the cell contents of a range C9:BH9

Is that possible, I cant seem to be able to assign a macro on that basis

Sub clear9()

If Range("b9").Value = "Del" Then
Range("c9:bh9").ClearContents
End If

End Sub

 

[davesexcel]

Checkout the WorkSheet_Change event

 

[Dazzybeeguy]

Hi

I want to allow the user to change a cell contents to a given Criteria

I wanted to add Validation droop down where they select Del in cell B9

I want to create VBA that on that action would clear the cell contents of a range C9:BH9

Is that possible, I cant seem to be able to assign a macro on that basis

Sub clear9()

If Range("b9").Value = "Del" Then
Range("c9:bh9").ClearContents
End If

End Sub

If I insert that in a module and run it then it clears the content, but when I change the dropdown in cell B9 to Del it doesnt do anything

 

[davesexcel]

Th code belongs in the worksheet module.
Right click on the sheet tab and select "View Code"

Paste this code there.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$9" Then
If Target = "Del" Then
Range("C9:BH9").ClearContents
End If
End If

End Sub

If you want to have it dynamic so it works for every row in column B

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target = "Del" Then
Range("C" & Target.Row & ":BH" & Target.Row).ClearContents
End If
End If

End Sub

 

[Dazzybeeguy]

Th code belongs in the worksheet module.
Right click on the sheet tab and select "View Code"

Paste this code there.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$9" Then
If Target = "Del" Then
Range("C9:BH9").ClearContents
End If
End If

End Sub

If you want to have it dynamic so it works for every row in column B

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target = "Del" Then
Range("C" & Target.Row & ":BH" & Target.Row).ClearContents
End If
End If

End Sub

Thanks, the Dynamic one works, is there a VBA way to reset column B on Exit to turn any that had been changed to "Del" back to "N" ???