Worksheet events...Why doesn't this work?!?! So confused!!
 

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

Worksheet events...Why doesn't this work?!?! So confused!!
 

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

Worksheet events...Why doesn't this work?!?! So confused!!
 

Thanks so much. I'm still a bit new at this and when I had two
instances of SelectionChange it told me that I had an ambiguous call,
so I changed the name of the second one. Would you by chance be able to
teach me how to consolidate these 2 functions into 1 or to get around
the fact that I can only have 1 SelectionChange? Thanks so much for the
help!!


Gary''s Student wrote:
They are not the same. The second is ClearChange. SelectionChange responds
to a mouse click or arrowkey movement.

ClearChange responds to .....????????
--
Gary's Student


" 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

Worksheet events...Why doesn't this work?!?! So confused!!
 

I want to thank both of you guys so much!! It works and it works
great!! I don't know what I would have done without you!!

wrote:
Thanks so much. I'm still a bit new at this and when I had two
instances of SelectionChange it told me that I had an ambiguous call,
so I changed the name of the second one. Would you by chance be able to
teach me how to consolidate these 2 functions into 1 or to get around
the fact that I can only have 1 SelectionChange? Thanks so much for the
help!!


Gary''s Student wrote:
They are not the same. The second is ClearChange. SelectionChange responds
to a mouse click or arrowkey movement.

ClearChange responds to .....????????
--
Gary's Student


" 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

Worksheet events...Why doesn't this work?!?! So confused!!
 

Hi,

You're welcome.
Thank's for the feedback.

Ken Johnson