How can I define ENTER keypressed event for a dynamically chosen Cell in VBA for Excel

Question

I got a dynamically chosen Cell that will be filled with some information that im going to put and when I put the information and ENTER keypressed at that Cell;

1 - it should trigger a macro

'macro(value)
macro1 myinfo

2 - macro should get the info in that cell

myinfo = Cells( i, j )

So how can i achive that?

Solution 1

To capture a specific key being pressed, you need the OnKey method:

Application.OnKey "~", "myMacro" ' for the regular enter key
' or if you want Enter from the numeric keypad:
' Application.OnKey "{ENTER}", "myMacro"
' Below I'll just assume you want the latter.

The above says that myMacro must be run when the Enter key is pressed. The OnKey method only needs to be called once. You could put it in the Workbook_Open event:

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

To stop capturing the Enter key,

Application.OnKey "{ENTER}"

---

To check whether Enter was pressed while on cell A1, you could do this:

Sub myMacro()
    If Not Intersect(Selection, Range("A1")) Is Nothing Then
    ' equivalent to but more flexible and robust than
    'If Selection.Address = "$A$1" Then
        MsgBox "You pressed Enter while on cell A1."
    End If
End Sub

---

Now to detect if Enter was pressed in a specific cell only if that cell has been edited, we have to be a bit clever. Let's say you edit a cell value and press Enter. The first thing that is triggered is the OnKey macro, and after that the Worksheet_Change event is triggered. So you first have to "save the results" of OnKey, and then handle the Worksheet_Change event based on those results.

Initiate OnKey like this: Application.OnKey "{ENTER}", "recordEnterKeypress"

In your code module you would have this:

Public enterWasPressed As Boolean

Sub recordEnterKeypress()
    enterWasPressed = True
End Sub

The cell edit will be captured by the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If enterWasPressed _
        And Not Intersect(Target, Range("A1")) Is Nothing Then
        MsgBox "You just modified cell A1 and pressed Enter."
    End If
    enterWasPressed = False 'reset it
End Sub

---

Now, the above code does what you ask in the question, but I would like to reiterate: your question sounds awfully like an XY problem. Why do you want to detect the Enter key being pressed? Let us know and maybe we can suggest alternatives.

Solution 2

cause ill start a macro when a stock code entered at that cell and give the info of that stock in excel and Worksheet_Change or Change commands will only cause it to get in a loop cause when the stock info being parsed into the cells it will trigger Change event again and again.. – Berker Yüceer 31 mins ago

Berker,

For this you don't need to trap the "ENTER" Key. Let's say, you type the Stock Code and instead of pressing ENTER, you clicked on another cell. Wouldn't you like the macro to be fired in that scenario as well? If yes, then try the code below. I am assuming that the macro has to run when the Stock Code is entered in Cell A1.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    '~~> This line ensure that the code will enter into the
    '~~> block only if the change happened in Cell A1
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.EnableEvents = False

        '
        ' ~~> Put your macro code here or run your macro here
        '
    End If

LetsContinue:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

EDIT: I see you have already selected your answer :)

Solution 3

use worksheet change event;

some thing as below,

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then
        ' call your sub

    End If
End Sub

Put this code in the corresponding worksheet module.

Solution 4

THANKS VERY Much for this and i do i bit change to it as the following:

Dim oldvalue As String Dim newvalue As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoa

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'~~> This line ensure that the code will enter into the
'~~> block only if the change happened in Cell A1
If Not Intersect(Target, Range("A:D")) Is Nothing Then
    Application.EnableEvents = False

    '
    ' ~~> Put your macro code here or run your macro here
    '
    oldvalue = Range(Target.Address).Value
    Range(Target.Address).Value = Range(Target.Address).Value * 2.33
    newvalue = Range(Target.Address).Value
    MsgBox ("value changed from  " & oldvalue & "  to  " & newvalue)
End If

LetsContinue: With Application .ScreenUpdating = True .EnableEvents = True End With

Exit Sub

Whoa: MsgBox Err.Description Resume LetsContinue End Sub

that will give you a chance to change any cell within range by certain value(i want the cell value to be multiply-ed by factor once the cells value is changed and show me a message that will give old and new value,

cheers best luck

View more solutions

Comments

[Berker Yüceer]

so how can i figure if the Enter key pressed in that cell? MY question was all about defining a keypressed event in a cell...

[Berker Yüceer]

if keypressed event is not posible for a cell than ur code might come in handy but my first priority is finding a way for that keypressed event.. for that reason +1

[Jean-François Corbett]

@BerkerYüceer has a point: The Worksheet_Change event can be triggered by things other than the Enter key being pressed (e.g. edit a cell and then click elsewhere to exit edit more). So you're not really detecting the Enter key being pressed. Why @BerkerYüceer wants to do that, however, I have no idea.

[Jean-François Corbett]

Also you can press enter while on a cell without modifying its contents. This will not be captured either by the Worksheet_Change event.

[Siddharth Rout]

+ 1 I was almost done with my post when I got an alert that someone has posted a solution so I refreshed the page and almost smiled. My code matches exactly like yours except for 2 things. 1) I was ALSO capturing "~" as well for "ENTER" key so that it incorporates for both "ENTER" Keys. 2) I was adding a precaution as well. One shouldn't trap the enter key else it won't exhibit the default behavior which is moving on to the next cell :) "EDIT: I see you have taken care of "~" as well" :) Ah I see a 3rd Differece "Enableevents=False" which is a must when handling Worksheet_Change()

[Berker Yüceer]

+1 perfect explanation about how it works. Also as i told as the comment under your question. @Jean-FrançoisCorbett cause ill start a macro when a stock code entered at that cell and give the info of that stock in excel.. those Worksheet_Change or Change commands will only cause it to get in a loop..

[Siddharth Rout]

@Berker Yüceer: It depends on how you handle the Worksheet Event ;) I just posted a reply but you had already selected your answer :) Sid

[Berker Yüceer]

EDIT: I see you have already selected your answer :) that wouldnt cause any trouble u will still get the +1 for taking off my horse glasses.. lol

[Siddharth Rout]

LOL. it wasn't for points at all. It was a good question and had me interested for quite sometime. :)

[Berker Yüceer]

yea i know what you mean with "quite sometime".. xD so +1 means that ur answer is valuable for this question. its not a competition point, its showing people more than a couple valuable resources to solve their problems so thanks for your time and good answer.

[M_Mogharrabi]

@Jean-François Corbett,Thanks for your clear and complete answer.But i have another problem in my win.app. ,The OnKey method does not work because of the protection that i have applied on the sheet.Is there any way to detect key pressing even in a protected sheet?

[Jean-François Corbett]

Hm, not sure. You should ask a new question.

[M_Mogharrabi]

@Jean-FrançoisCorbett,Thanks for your guide.I asked my question here stackoverflow.com/questions/15239781/…. please help me...

[andysando]

@Jean-FrançoisCorbett Where do you put the Application.OnKey code, becuase it tries to run the macro if I have two spreadsheets open at the same time, and press enter in the other spreadsheet.

[Jean-François Corbett]

Please ask a new question so that you can fully explain your situation. Too long for comments.

[Ibo]

This only works only with the Numpad Enter and not the keyboard Enter key. To enable the keyboard Enter this line should be added too: Application.OnKey "~", "recordEnterKeypress"

[DeerSpotter]

Thank you, here is allowed me to see something else i needed.

[DeerSpotter]

Dangerous, No catch for error, loops it over and over and crashed my pc.