Tags: box, code, enter, excel, key, keyboard, keypad, message, microsoft, msdn, pops, software, written

Keyboard Enter key and Keypad Enter key

On Microsoft » Microsoft Excel

5,962 words with 8 Comments; publish: Wed, 05 Dec 2007 07:40:00 GMT; (306156.25, « »)

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

All Comments

Leave a comment...

  • 8 Comments
    • Here is a revised sub for Auto Open

      Sub Auto_Open()

      If Not ActiveSheet.Name = "Sheet1" Then

      Worksheets("Sheet1").Activate

      Else

      'run the code that is in the worksheet_activate Event

      '~ is the code for Return (not Numeric)

      Application.OnKey "~", "AMPM"

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

      End If

      End Sub

      remember to add this code as well on sheet1

      Private Sub Worksheet_Deactivate()

      Application.OnKey "~"

      Application.OnKey "{Enter}"

      End Sub

      The ~ is the code here for return

      HTH

      David

      #1; Tue, 11 Dec 2007 22:00:00 GMT
    • 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"

      #2; Tue, 11 Dec 2007 22:01:00 GMT
    • Have you set onkey as a variable somewhere by mistake
      #3; Tue, 11 Dec 2007 22:02:00 GMT
    • 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

      #4; Tue, 11 Dec 2007 22:03:00 GMT
    • Oh, by the way, the only the keypad enter key works even with your code. I don't know what to do.
      #5; Tue, 11 Dec 2007 22:04:00 GMT
    • 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

      #6; Tue, 11 Dec 2007 22:05:00 GMT
    • 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.

      #7; Tue, 11 Dec 2007 22:06:00 GMT
    • 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

      #8; Tue, 11 Dec 2007 22:07:00 GMT