Results 1 to 6 of 6

Thread: Running a Macro up Pressing the Numeric Enter Key

  1. #1
    New Member
    Join Date
    Jun 2014
    Location
    Glendale, Arizona
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Running a Macro up Pressing the Numeric Enter Key

    Th following code performs flawlessly. The code is in a sheet module as I do not wish for it to run throughout the entire workbook. The code calls for cell selection anytime a change is made to certain cells. I would like for the cell selections to be made upon pressing the numeric enter key instead as sometimes the cells will not require changing. Ideas?

    [code]
    Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo Whoa


    Application.EnableEvents = False


    If Not Target.Cells.CountLarge > 1 Then
    If Not Intersect(Target, Columns(6)) Is Nothing Then ' Column F to G
    Target.Offset(, 1).Select
    ElseIf Not Intersect(Target, Columns(7)) Is Nothing Then ' Column G to O
    Target.Offset(, 8).Select
    ElseIf Not Intersect(Target, Columns(15)) Is Nothing Then ' Column O to U
    Target.Offset(, 6).Select
    ElseIf Not Intersect(Target, Columns(21)) Is Nothing Then ' Column U to A Down 1
    Target.Offset(1, -20).Select
    End If
    End If


    Letscontinue:


    Application.EnableEvents = True
    Exit Sub


    Whoa:


    MsgBox Err.Description
    Resume Letscontinue

    End Sub
    [end code]

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,800
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Running a Macro up Pressing the Numeric Enter Key

    .
    Paste this code in the sheet module :

    Code:
    Option Explicit
    
    
    Private Sub Worksheet_Activate()
        Application.OnKey "{ENTER}", "RunIt"
    End Sub
    
    
    Private Sub Worksheet_Deactivate()
        Application.OnKey "{ENTER}", ""
    End Sub

    In a regular module paste this macro :

    Code:
    Option Explicit
    
    
    Sub RunIt()
        MsgBox "Hi"
    End Sub

    You can change the name of the sub to whatever you want so long as the same name is shown in the macro in the sheet module.
    Replace the MsgBox command with your macro code.

  3. #3
    New Member
    Join Date
    Jun 2014
    Location
    Glendale, Arizona
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running a Macro up Pressing the Numeric Enter Key

    Thank you for your quick response. Your answer was clear and easy to understand and follow. I really appreciate that.

    I created a new Workbook with two sheets: Sheet1 and Sheet2. I copied and pasted your first code into the Sheet2 Module. I copied and pasted your second code into Module1. I went to Sheet2 and selected random cells and hit the numeric enter key and received the MsgBox every time. I then went to Sheet1 and the numeric enter key did not behave as normal. Depressing that key should have selected the row below but it remained on the selected cell.

    SIDEBAR - If "{ENTER}" refers to the numeric enter key, how does one refer to the main enter key?

  4. #4
    New Member
    Join Date
    Jun 2014
    Location
    Glendale, Arizona
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running a Macro up Pressing the Numeric Enter Key

    Got it. Just needed to delete the "" at the end of your second code. Found it while researching the SIDEBAR question, the answer of which turns out to be "{~}".
    Could not have done it without your help. Thanks again!

  5. #5
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,800
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Running a Macro up Pressing the Numeric Enter Key

    .
    The primary ENTER key is the tilde sympbol ~


    Code:
    Option Explicit
    
    
    Private Sub Worksheet_Activate()
        Application.OnKey "~", "RunIt"
    End Sub
    
    
    Private Sub Worksheet_Deactivate()
        Application.OnKey "~", ""
    End Sub
    To turn the numeric ENTER key back to normal try this code:

    Code:
    Private Sub Worksheet_Deactivate()
        Application.OnKey "{ENTER}"
    End Sub
    If that doesn't work, you may need to create another OnKey macro for that specific sheet and assign the OnKey event to the other macro.
    Last edited by Logit; Jun 3rd, 2019 at 09:54 PM.

  6. #6
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,800
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Running a Macro up Pressing the Numeric Enter Key

    You are welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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