How to keep ONKey's macro on Worksheet Selection Change Event

Using the following VBA code I get an error message "The macro "C:\data\MSExcel\statement.xls'!JumpToA14' cannot be found.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("$B9:$C$9"), Range(Target.Address)) Is Nothing Then
'To turn on enter key macro
Application.OnKey "~", "JumpToA14"
Application.OnKey "{Enter}", "JumpToA14"
'To turn off enter key macro
Else
Application.OnKey "~"
Application.OnKey "{Enter}"
End If
End Sub

Private Sub JumpToA14()
'This line seems to work better Range("A14").Activate
Application.Goto Reference:=Range("A14")
End Sub

It works great if I put the Private Sub JumpToA14() in Module 1. Does anybody know how I can make it work by keeping both subs under Worksheet selection change event? (I really want the OnKey on the Worksheet selection change event)

Thanks a million, Mikeburg

The procedure referenced by the OnKey function must reside in a module. There
is no way around it.

FYI... Target and Range(Target.Address) are exactly the same thing. They
both are range objects.
--
HTH...

Jim Thomlinson


"mikeburg" wrote:

>
> Using the following VBA code I get an error message "The macro
> "C:\data\MSExcel\statement.xls'!JumpToA14' cannot be found.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Application.Intersect(Range("$B9:$C$9"), Range(Target.Address))
> Is Nothing Then
> 'To turn on enter key macro
> Application.OnKey "~", "JumpToA14"
> Application.OnKey "{Enter}", "JumpToA14"
> 'To turn off enter key macro
> Else
> Application.OnKey "~"
> Application.OnKey "{Enter}"
> End If
> End Sub
>
> Private Sub JumpToA14()
> 'This line seems to work better Range("A14").Activate
> Application.Goto Reference:=Range("A14")
> End Sub
>
> It works great if I put the Private Sub JumpToA14() in Module 1. Does
> anybody know how I can make it work by keeping both subs under
> Worksheet selection change event? (I really want the OnKey on the
> Worksheet selection change event)
>
> Thanks a million, Mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=400992
>
>

Thanks a million. I'm still learning.

mikeburg