code to execute on enter

[Guest]

I am running excel 2002. I have a sheet with cell f5 active. This is the
only cell the user is allowed to type in or select. Is there a way to make
my vba code execute when the user presses the enter key?

Thanks
sc

 

[okrob]

I am running excel 2002. I have a sheet with cell f5 active. This is the
only cell the user is allowed to type in or select. Is there a way to make
my vba code execute when the user presses the enter key?

Thanks
sc

Add a Worksheet_Change event to the worksheet code.
Chip Pearson has an excellent guide for beginners.
http://www.cpearson.com/excel/events.htm

But basically,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$F$5" Then

'run your code here

End If
End Sub

 

[Guest]

Hi sc -

Two addtional methods:

Method 1: Onkey method. Add the following two event procedures to the
worksheet's module (yourProcedure can be located in a standard module; it
will be called by the pressing of Return on the keyboard or Enter on the
keypad):

Private Sub Worksheet_Activate()
Application.OnKey "{Return}", "yourProcedure"
Application.OnKey "{Enter}", "yourProcedure"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{Return}" 'resets the Return key
Application.OnKey "{Enter}" 'resets the Enter key
End Sub


Method 2: SelectionChange event. This procedure will work whether or not a
value is entered in F5.

First, unprotect the sheet, unlock the cell below F5, and then re-Protect
the sheet. Next, check |Tools | Options | EditTab and make sure that "Move
selection after Enter Direction" is set to 'Down.'
Finally, copy this procedure to the worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Range("F5").Select
Application.EnableEvents = True

'Your VBA code here

End Sub

Note: this second method doesn't require that the worksheet be protected at
all, but protection minimizes screen flicker associated with a user clicking
here and there to test the sheet.