Excel VBA: SendKeys fails on some computers

Question

I'm working on an excel sheet wherein each row needs to indicate the last time that any cell within that row has changed. The simplest method I've found to do this is to put some tiny amount of VBA in the worksheet code, like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If (Target.Row > 2) And (Cells(Target.Row, "A") <> "") Then
        Cells(Target.Row, "N").Value = Date
    End If
    Application.EnableEvents = True
End Sub

This will effectively change the date in the "N" column whenever any other item in that row is edited. Great! Solved, except...

Because I'm changing the cell value in the code, the undo stack is immediately lost, and of course this means that ANY work in this worksheet cannot be undone.

So, an alternative to this is to trick excel into thinking I haven't edited a cell. This code preserves the undo stack while changing the date:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cursorLocation As Range
    Application.EnableEvents = False
    If Target.Row > 2 And Cells(Target.Row, "A") <> "" Then
        Set cursorLocation = ActiveCell
        Cells(Target.Row, "N").Select
        SendKeys "^;~", True
        cursorLocation.Select
    End If
    Application.EnableEvents = True
End Sub

In this case, we select the cell, use SendKeys to fake editing the cell, and the restore the cursor to its original location. "^;~" is using Excel's "Ctrl+;" shortcut to input the date. Great! Solved, except...

This code works fine on my machine (Win7, Excel 2010) but fails on a co-worker's machine (Win8, Excel 2010, maybe a bit faster). On the Win8 machine (no idea if it's the OS that's the problem, btw), what happens is that whenever a cell is changed, every cell immediately below that cell becomes the current date, and of course preserving the Undo history is meaningless because executing an Undo immediately reactivates the worksheet code and turns everything into dates again.

I figured out on my own that the same thing will happen on my machine if I remove the "Wait" inherent in the SendKeys command. That is, if I use the line:

SendKeys "^;~", False

So, what I'm guessing is that for whatever reason, even when using the same version of Excel, my computer is waiting for the SendKeys command to finish, but my co-worker's computer is not. Any ideas?

Answer 1

You are right. It gives that problem in Excel 2010/Win8.

Try this. Use the custom Wait code that I wrote. (Tested in Excel 2010/Win8)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cursorLocation As Range
    Application.EnableEvents = False
    If Target.Row > 2 And Cells(Target.Row, "A") <> "" Then
        Set cursorLocation = ActiveCell
        Cells(Target.Row, "N").Select
        SendKeys "^;~"
        Wait 1 '<~~ Wait for 1 Second
        cursorLocation.Select
    End If
    Application.EnableEvents = True
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

Alternative

Using Doevents also has the desired effect.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cursorLocation As Range
    Application.EnableEvents = False
    If Target.Row > 2 And Cells(Target.Row, "A") <> "" Then
        Set cursorLocation = ActiveCell
        Cells(Target.Row, "N").Select
        SendKeys "^;~"
        DoEvents
        cursorLocation.Select
    End If
    Application.EnableEvents = True
End Sub