Run macro when the enter key (only) is pressed

[Jeckford]

I would like a macro to run every time I press the enter key anywhere on a specific sheet. I will be entering data in several cell before pressing enter, so the macro only needs to run once enter is press and not when the cells are changed.

[davesexcel]

This goes in the worksheet module, right click the sheet tab and select View Code
One is the worksheet change, and the other is the selection change

Use the one you want.
Ussually one would onlt want the code to run when a specific range is selected, but you have not determined that.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    MsgBox "Add code here"

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "Add code here"

End Sub

Look at my other examples here.
http://www.davesexcel.com/vbacodes.htm#858300135

[Jeckford]

Hi Dave,

I've put the code in and it runs every time I select a cell, I only need it to run when I press the "Enter" key. Am I missing something?

Thanks,

[Olly]

Try:

Private Sub Worksheet_change(ByVal Target As Range)
    If Not Target.Row = ActiveCell.Row Then
        MsgBox "Enter pressed to commit changes"
    End If
End Sub

[Jeckford]

Deleted reply

[Jeckford]

Hi Olly,

I've manage to get yours to work, but how do I call the macro from this point. I removed the "MsgBox "Enter pressed to commit changes"" and replaced it with Call Macro (the macro is called macro) and an error run time appears.

Thanks,

[Olly]

Hi Olly,

I've manage to get yours to work, but how do I call the macro from this point. I removed the "MsgBox "Enter pressed to commit changes"" and replaced it with Call Macro (the macro is called macro) and an error run time appears.

Thanks,

Should work. This does:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Row = ActiveCell.Row Then Call Macro
End Sub

Sub Macro()
    Debug.Print Now, "New row entered"
    'your code here
End Sub

If that's still not working, try attaching your workbook so I can see what's going on.

[Jeckford]

Hi Olly,

Okay it wasn't working a minute ago, but now it seems to be all good.

Thanks heaps for your help. If it comes up with an error again I'll send you a sample.

Thanks again.

[Debraj Roy]

If I emphasize the (ONLY) word.. and ignore your #ofPost.. and assume you as a intermediate level of Excel user.. then

I would like to suggest.. this one..

On this_Workbook..

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "{CAPSLOCK}"
End Sub

Private Sub Workbook_Open()
    Application.OnKey "{CAPSLOCK}", "myMacro"
End Sub

In a separate Module..

Sub myMacro()
        MsgBox "You pressed CAPS LOCK Key"
End Sub

BTW.. dont forget to change all the {CAPSLOCK} to {ENTER}


EDIT: Please Ignore.. as I think.. you really need (only) Worksheet_change events..

[davesexcel]

My idea was that everytime you press enter you would be selecting another cell anyway. So why not use the selection_change event.

Is there a specific range you would want the code to activate?

[Debraj Roy]

Check post # 6..

[Debraj Roy]

Another approach.. to limit it within a sheet..

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ActiveSheet.Name = "Sheet1" Then
        Application.OnKey "{CAPSLOCK}", "myMacro"
    Else
        Application.OnKey "{CAPSLOCK}"
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "{CAPSLOCK}"
End Sub

[Debraj Roy]

and final one.. with API..

On a Module ..

Option Explicit
#If VBA7 And Win64 Then
    Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If
    Const VK_ENTER As Integer = &HD
Sub DisplayKeyStatus()
    Dim Msg As String
    If GetKeyState(VK_ENTER) < 0 Then MsgBox "Enter Key Pressed"
End Sub

and in sheet_SelectionChange event..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call DisplayKeyStatus
End Sub