So I have written 2 worksheet events, 1 of which works, the other one
does not. But they're both the same!

In a nut shell this is what I'm trying to do. When a cell in range
B32:D45 is selected, cell F32 returns the data in the target cell. This
part works

What does not work: When I click a cell in range A3:D4, I want cell F32
to be cleared. The two pieces of code are exactly the same, but the
latter one does not work. Can someone please explain to me why? Any
help would be so greatly appreciated!


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("B32:D45")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Me.Parent.Worksheets("Output").Range("F32").Value = Target.Value

errHandler:
Application.EnableEvents = True

End Sub

Private Sub Worksheet_ClearChange(ByVal Target1 As Range)

If Intersect(Target1, Me.Range("A3:D4")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Me.Parent.Worksheets("Output").Range("F32").Value = ""

errHandler:
Application.EnableEvents = True

End Sub

wrote:
So I have written 2 worksheet events, 1 of which works, the other one
does not. But they're both the same!

In a nut shell this is what I'm trying to do. When a cell in range
B32:D45 is selected, cell F32 returns the data in the target cell. This
part works

What does not work: When I click a cell in range A3:D4, I want cell F32
to be cleared. The two pieces of code are exactly the same, but the
latter one does not work. Can someone please explain to me why? Any
help would be so greatly appreciated!


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("B32:D45")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Me.Parent.Worksheets("Output").Range("F32").Value = Target.Value

errHandler:
Application.EnableEvents = True

End Sub

Private Sub Worksheet_ClearChange(ByVal Target1 As Range)

If Intersect(Target1, Me.Range("A3:D4")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Me.Parent.Worksheets("Output").Range("F32").Value = ""

errHandler:
Application.EnableEvents = True

End Sub

Hi,

You can only use Excel's very own Event procedure headings, you cannot
customize them and still expect Excel to recognise and act on.

You have to combine the two tests into the one selection change event
procedure. Try...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Me.Range("A3:D4")) Is Nothing Then


On Error GoTo errHandler
Application.EnableEvents = False
Worksheets("Output").Range("F32").Value = ""
Application.EnableEvents = True
Exit Sub
End If

If Not Intersect(Target, Me.Range("B32:D45")) Is Nothing Then
If Target.Cells.Count 1 Then
MsgBox "You selected more than one cell."
Exit Sub
End If
On Error GoTo errHandler
Application.EnableEvents = False
Worksheets("Output").Range("F32").Value = Target.Value
Application.EnableEvents = True
Exit Sub
End If

errHandler: Application.EnableEvents = True

End Sub

For the B32:D45 selection, I've added a check to cover the user
selecting more than one cell. Excel can't make F32 the value of more
than one cell. Another way around the more than one cell problem, if
you prefer not to use the MsgBox, is to change...

Worksheets("Output").Range("F32").Value = Target.Value

to...

Worksheets("Output").Range("F32").Value = ActiveCell.Value

Also, I removed the Me.Parent bits. I couldn't see any need for them.
If I am wrong, and have missed something, then just put them back in.

Plus, if the Output sheet does not have any Event procedures that would
be triggered by the changes to its F32 cell then there is no need for
the errHandler parts of the code.

Ken Johnson