Hi,

I have written a code that on "Enter", a message box pops up, but it only works with the keypad Enter key and not the keyboard one. How can I have both Enter keys work?

Also, I have to create a procedure to activate Sheet1 at startup otherwise the code for the AM/PM does not work. Is there a way to remedy this?

Sub Auto_Open()
Sheet2.Activate
Sheet1.Activate
End Sub

Here is my code for AM/PM:


Public Sub AMPM()
Dim mCt, i, j As Integer

mCt = 0

i = 0
For i = 1 To 50
If Range("J" & i).Value = "AM" Then
mCt = mCt + 1
End If
Next i

If mCt >= 20 Then
Range("J" & CStr(ActiveCell.Row)).Value = ""
Selection.ClearContents
MsgBox ("Booked!")
Exit Sub
End If
ActiveCell.Offset(1, 0).Select

End Sub

Private Sub Worksheet_Activate()
'this only works for the keypad enter key. I want it to work for both enter keys.
Application.onkey "{Enter}", "AMPM"

End Sub

Here is a revised sub for Auto Open

remember to add this code as well on sheet1
The ~ is the code here for return

HTH
David

Hi,

Thanks for prompt reply.
Something odd is happening right now. When I enter the line bellow, the OnKey goes from the upper case 'O' to lower case and the same thing is happening for the 'K', thereby rendering the code dead. How do you fix this? Or what happened that make the upper to lower?

from
Application.OnKey "{Enter}", "AMPM"

to

Application.onkey "{Enter}", "AMPM"

Have you set onkey as a variable somewhere by mistake

No, I did not.

here is the entire code:

Option Explicit
Sub Auto_Open()
If Not ActiveSheet.name = "Sheet1" Then
Worksheets("Sheet1").Activate
Else
Application.onkey "~", "AMPM"
Application.onkey "{Enter}", "AMPM"
End If

End Sub

Option Explicit
Public Sub AMPM()
Dim mCt, mCtPM, mCtAMPM, i As Integer

mCt = 0
mCtPM = 0
mCtAMPM = 0
i = 0

For i = 1 To 50
If Range("J" & i).Value = "AM" Then
mCt = mCt + 1
ElseIf Range("J" & i).Value = "PM" Then
mCtPM = mCtPM + 1
ElseIf Range("J" & i).Value = "AM/PM" Then
mCtAMPM = mCtAMPM + 1
End If
Next i

If mCt >= 20 Then
Range("J" & CStr(ActiveCell.Row)).Value = ""
Selection.ClearContents
MsgBox ("Booked!")
Exit Sub
End If
If mCtPM >= 20 Then
Range("J" & CStr(ActiveCell.Row)).Value = ""
Selection.ClearContents
MsgBox ("Booked!")
Exit Sub
End If
If mCtAMPM >= 20 Then
Range("J" & CStr(ActiveCell.Row)).Value = ""
Selection.ClearContents
MsgBox ("Booked!")
Exit Sub
End If
ActiveCell.Offset(1, 0).Select

End Sub

Private Sub Worksheet_Activate()
Application.onkey "~"
Application.onkey "{Enter}", "AMPM"
End Sub

Private Sub Worksheet_Deactivate()
Application.onkey "~"
Application.onkey "{Enter}"
End Sub

Oh, by the way, the only the keypad enter key works even with your code. I don't know what to do.

I thought i might try the easier stuff before getting to complicated
I tried copying your code to my version and without a problem so there probably isnt anything wrong with your code just now.

some things to try
Is the OnKey Event still in the Object Browser under the Application Class,
Try in another section of your code typing application.onkey and see if try's to AutoComplete itself (maybe in a new sub or in a new module or something).
another thought try copying the code into a new workbook and see what happens then.
You have 2 Option Explicit statements in your code i presume that they come from at least 2 different sections. If not try removinfg the 2nd Option Explicit
Try Compiling your project and see if any errors come up

what version of excel are you using im running 97 and 2000 at the moment

ive tested the ~ in my version and it seems to work
let me know how you get on
David
I notice from your original code that the OnKey event is all in Lower Case as well

I tried on another workbook and it works fine, except for the enter key on the keyboard. That one eludes me. I have to return this small project to my boss, today. But it is not going to do if only one Enter key works. I looked at Excel VBA Macro Programming by Richard Shepherd and it does not help me either. I looked at Microsoft Excel VBA Programming by Duane Birnbaun but that one did not help either on the issue of Application.OnKey "~", "AMPM".
That one Application.OnKey "{Enter}", "AMPM" works for the keypad enter key. but does not work for the keyboard one. Application.OnKey "~", "AMPM" works also for the keypad enter key, but not for the keyboard one.

Again this might come from the version you are looking at
try searching the help file for OnKey Statement

The Help FIle i Have states that

ENTER (numeric keypad) {ENTER}
ENTER ~ (tilde)

Hence why i was stating the tilde command

HTH

David