Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Keyboard Enter key and Keypad Enter key

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Here is a revised sub for Auto Open

    Code:
    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
    Code:
    Private Sub Worksheet_Deactivate()
        Application.OnKey "~"
        Application.OnKey "{Enter}"
    End Sub
    The ~ is the code here for return

    HTH
    David

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    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"

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Have you set onkey as a variable somewhere by mistake

  5. #5
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    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

  6. #6
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    Oh, by the way, the only the keypad enter key works even with your code. I don't know what to do.

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  8. #8
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    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.

  9. #9
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •