Run macro when Enter key pressed in a range

[EssKayKay]

Hello,

I’m looking for a routine that will run a macro after the Enter key is pressed on a cell in a range (M33:M2033).

I have a routine I received that does something like this but only addresses a single cell. I would like the condition to run on any cell in the range. There will be other conditions set prior to running the code but let’s start with just seeing if I can get this to trigger as hoped.

Here is my current single cell routine:

Public cellAddress As String            ‘<== NOTE: This must be placed at top of All Code (not part of Subs) ===
    
‘- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'------ Run code after Enter in K7 ------
     If cellAddress = "$K$7" Then
        MsgBox "Do Something"
     End If
     cellAddress = ActiveCell.Address

End Sub

Thanks for viewing,
Steve K.

 

[Engberg]

I came up with this. It it very improvised, since there is no way to detect key down events on sheets (only possible in forms/controls).
But I think is does what you asked for, it triggers when you press ENTER, DOWN ARROW or click the cell below the active one in a given range.
There might be a better approach to your problem if you would explain more about it.

Public cellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Debug.Print cellAddress
   
    Dim detectRange As Range
    Set detectRange = Range("K1:K10") ' <-------------- Adjust your range here
    Dim enterRange As Range
    Set enterRange = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(1, 0))
   
    If Not Application.Intersect(enterRange, detectRange) Is Nothing Then
        If Not Application.Intersect(Range(cellAddress), ActiveCell.Offset(-1, 0)) Is Nothing Then
            MsgBox "Do Something"
        End If
    End If
    cellAddress = ActiveCell.Address
   
ErrorHandler:
        cellAddress = ActiveCell.Address
        Exit Sub
End Sub

 

[EssKayKay]

I came up with this. It it very improvised, since there is no way to detect key down events on sheets (only possible in forms/controls).
But I think is does what you asked for, it triggers when you press ENTER, DOWN ARROW or click the cell below the active one in a given range.
There might be a better approach to your problem if you would explain more about it.

Public cellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Debug.Print cellAddress
  
    Dim detectRange As Range
    Set detectRange = Range("K1:K10") ' <-------------- Adjust your range here
    Dim enterRange As Range
    Set enterRange = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(1, 0))
  
    If Not Application.Intersect(enterRange, detectRange) Is Nothing Then
        If Not Application.Intersect(Range(cellAddress), ActiveCell.Offset(-1, 0)) Is Nothing Then
            MsgBox "Do Something"
        End If
    End If
    cellAddress = ActiveCell.Address
  
ErrorHandler:
        cellAddress = ActiveCell.Address
        Exit Sub
End Sub

Thank you Engberg for your quick response. I tried your code and it almost worked as I wished. I modified it slightly to do what I had in mind. Please realize this may not be proper coding as I am by no means a programmer (probably less than a novice).

If I understand this correctly, as a test, if I set the range to Set detectRange = Range("K7:K9"). With this, I thought when I hit the Enter key in cells K7, K8, or K9, it would display the “Do Something” message. However, as originally coded the message is displayed when striking the Enter key in cells K5, K6, K7, K8, & K9. As a remedy, I changed the ActiveCell.Offset line to
Set enterRange = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0))
(i.e., all Offsets are now set at (-1,0). That appears to do what I was expecting.

Here’s my "revised" code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

     On Error GoTo ErrorHandler
     Debug.Print cellAddress

     Dim detectRange As Range
     Set detectRange = Range("K7:K9") ' <-------------- Adjust your range here
     Dim enterRange As Range

     'Set enterRange = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(1, 0))
     Set enterRange = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0))

     If Not Application.Intersect(enterRange, detectRange) Is Nothing Then

         'If Not Application.Intersect(Range(cellAddress), ActiveCell.Offset(-1, 0)) Is Nothing Then
         If Not Application.Intersect(Range(cellAddress), ActiveCell.Offset(-1, 0)) Is Nothing Then
              MsgBox "Do Something"
         End If
     End If
     cellAddress = ActiveCell.Address

ErrorHandler:
     cellAddress = ActiveCell.Address
     Exit Sub
End Sub

What are your thoughts on this? Any comments or suggestions would be appreciated.

Again, my thanks,
Steve K.

 

[Alex Blakenburg]

I think your last 3 questions are essentially the same question.
The Public Declaration needs to be in a Standard Module and as such I would suggest naming it so you know which sheet it belongs to.
eg Public sht1PrevActiveCell As Range

Then in the Sheet Module try adding something like this:
[Change Range to suit (you have used at least 3 different range to date)]

Private Sub Worksheet_Activate()
    Set sht1PrevActiveCell = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not sht1PrevActiveCell Is Nothing Then
        If Not Intersect(sht1PrevActiveCell, Range("K7:K9")) Is Nothing Then
            ' Do something
            Debug.Print sht1PrevActiveCell.Address
        End If
    End If
    Set sht1PrevActiveCell = ActiveCell
End Sub

As @Micron mentioned you will also need code in the ThisWorkbook module
[Change sheet name to correct name]

Private Sub Workbook_Open()
    If ActiveSheet.Name = Worksheets("Sheet1").Name Then
        Set sht1PrevActiveCell = ActiveCell
    End If
End Sub

 

[EssKayKay]

Thank Alex for your input. I tried entering your code as suggested. I receive a Run-time error 424.
As noted, I am not a programmer and as such I probably set something up incorrectly. Here is what/how I inserted your code.

Again, thanks. . .

 

[Alex Blakenburg]

Nice clear pictures.
I can't see a Standard Module in them though, see my picture. I have named it PublicVariable when you create it, it will say Module1.
You need to insert the Public sht1... declaration there.

PS: Right click on any item in the Project explorer window that belongs to that workbook and select Insert > Module

 

[EssKayKay]

Nice clear pictures.
I can't see a Standard Module in them though, see my picture. I have named it PublicVariable when you create it, it will say Module1.
You need to insert the Public sht1... declaration there.

PS: Right click on any item in the Project explorer window that belongs to t

 

[EssKayKay]

That did it - thank you Alex and Engberg. Both routines work great.

Again, much appreciated,
SKK