VBA to only trigger macro after Enter key

[EssKayKay]

Hello,

I’m having a problem triggering a macro after an “edit” entry has been made in a cell. I want to run the macro only if an edit has taken place. However, what appears to be happening is the macro is being triggered to soon (i.e., before the edit/entry is made). Is there a way to only run the code if an edit/Enter (or arrow) is made in the selected cell?

Here is a snippet of my code where I believe the problem exists (after the “If DoIt = vbYes Then” line):

If Not Intersect(Target, Range("M33:M2032")) Is Nothing Then
     If ActiveCell.Offset(0, 0) > 0 And Sheets("Amortize").Rows(7).Hidden = True Then
         Dim DoIt As Integer
         DoIt = MsgBox(" - EDITING - " & vbNewLine & _
              " Do you wish to edit Payment Date?", vbYesNo, " Edit Payment")
         If DoIt = vbYes Then

              MsgBox "Run Macro here"                  ‘ <----- This is what runs prior the edit

         Else
              SendKeys "{right}{left}"                 ‘ <----- Highlights DueDate
              Range("M31").End(xlDown).Offset(1, 0).Select
         End If
     End If
End If

Please bear with me as I have to note, I am not a programmer so this coding may appear to be incorrect, sloppy, or even a bit silly but for the most part it appears to be working.

Thanks for viewing,
Steve K.

 

[EssKayKay]

EDIT - I forgot to add one line of code. Prior to MsgBox "Run Macro Here" there is another line that opens the cell'

If DoIt = vbYes Then

               SendKeys "{Backspace}"                     '  <----- This deletes the current entry 
              MsgBox "Run Macro here"                  ‘ <----- This is what runs prior the edit

         Else

However, it never stops at the Backspace and just proceeds to the MsgBox

PS - I really wish you could edit these post and not be limited to some time frame.

 

[Peter_SSs]

PS - I really wish you could edit these post and not be limited to some time frame.

There is good reason for the time limit. Threads can be rendered meaningless if an early post is edited after many responses have been made based on its original content. With the 10 minute edit window we have, that can still happen of course but over a long period of time it was decided that 10 minutes should be enough time for a poster to check their post and correct it if needed while not having too many threads (possibly) completely messed up by edits.

 

[EssKayKay]

I'm back again. I should have stated my code is included in:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Engberg]

Would adding a Worksheet_Change event for the same range that runs the code do what you want it to?

So remove these so that If vbYes is empty:

SendKeys "{Backspace}"
MsgBox "Run Macro here"

And add a Worksheet_Change like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M33:M2032")) Is Nothing Then
        MsgBox "Run Macro Here"
    End If
End Sub

 

[EssKayKay]

Would adding a Worksheet_Change event for the same range that runs the code do what you want it to?

So remove these so that If vbYes is empty:

SendKeys "{Backspace}"
MsgBox "Run Macro here"

And add a Worksheet_Change like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M33:M2032")) Is Nothing Then
        MsgBox "Run Macro Here"
    End If
End Sub[Code]
[/QUOTE]

Would adding a Worksheet_Change event for the same range that runs the code do what you want it to?

So remove these so that If vbYes is empty:

SendKeys "{Backspace}"
MsgBox "Run Macro here"

And add a Worksheet_Change like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M33:M2032")) Is Nothing Then
        MsgBox "Run Macro Here"
    End If
End Sub

Hey Engberg - I think you got it - much appreciated. I have some more testing to do as there are a few more parts of the routine but this is what was holding me up. I'll keep you posted.

Again. my sincere thanks,
Steve K.