On Got Focus, Clear Cells

[Suzanne]

I can't quite figure out the right code for this:

On selection (GotFocus) of a validation drop-down in Column A, clear cells
in Columns B, D, & E; OR on selection (GotFocus) of the validation drop-down
in Column E, clear cells in Columns A, B, & D

Only the cells on the row with focus should be cleared

Thanks -- Suzanne

 

[Jim Thomlinson]

Expanding the Validation Drop Down is not an event that is tracked by XL. The
closest things are the select and change events. Select fires when the cell
is selected. Change fires when the cell changes. Would change work for you.
Once and item is selected from the validation list then the cells are
cleared??? That is not too difficult to do...

 

[Suzanne]

Change will do it too... I was trying to manipulate other solutions with
change (with no success). I appreciate your help.

Suz

 

[Jim Thomlinson]

This should be pretty close...

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Select Case .Column
Case 1 'column a
Application.EnableEvents = False
Cells(Target.Row, "B").ClearContents
Cells(Target.Row, "D").ClearContents
Cells(Target.Row, "E").ClearContents
Application.EnableEvents = True
Case 5 'column e
Application.EnableEvents = False
Cells(Target.Row, "B").ClearContents
Cells(Target.Row, "D").ClearContents
Cells(Target.Row, "A").ClearContents
Application.EnableEvents = True
End Select
End With
End Sub

 

[Peter T]

This works in the selection event, not quite as you ask but see if it meets
your needs.

It goes in the Worksheet module, right-click tab, view code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim col As Long

col = Target(1).Column
If col = 1 Or col = 5 Then
On Error Resume Next
nt = ActiveCell.Validation.Type
On Error GoTo 0
If nt = 3 Then
If col = 1 Then
Set rng = Range("B1,D1:E1")
Else
Set rng = Range("A1:B1,D1")
End If
rng.Offset(Target.Row - 1).ClearContents
End If
End If

End Sub

Regards,
Peter T

 

[Jim Thomlinson]

Sorry this is a little cleaner. The original code will work but this is a tad
better...

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Select Case .Column
Case 1 'column a
Application.EnableEvents = False
Cells(.Row, "B").ClearContents
Cells(.Row, "D").ClearContents
Cells(.Row, "E").ClearContents
Application.EnableEvents = True
Case 5 'column e
Application.EnableEvents = False
Cells(.Row, "B").ClearContents
Cells(.Row, "D").ClearContents
Cells(.Row, "A").ClearContents
Application.EnableEvents = True
End Select
End With
End Sub

 

[Suzanne]

Thanks VERY much! Jim, your formula did the trick.

Peter: I tried out your formula (I think I got it in right); but came up
with a var not defined error.

 

[Peter T]

Hi Suzanne,

I take it you have Option Explicit at the top of the module, which is good,
and I forgot to declare 'nt' like this -
Dim nt as Long

I wasn't sure if you want to clear cells if any cell in column A or E is
selected, or only when selecting cells in those columns with a valadation
list, which is what is looked for in the routine I posted. If that's what
you want, for clarity could change -

If nt = 3 Then

to
If nt = xlValidateList Then

Regards,
Peter T