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?

Answer 1

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 :)

Answer 2

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

Answer 3

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.