Excel VBA: SendKeys does not work on some computers

Question

I am working on an excel sheet in which each row should indicate the last time a cell inside that row has changed. The simplest method I have found for this is to put a small amount of VBA into the worksheet code, for example:

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 this row is edited. Large! Resolved except ...

Since I am changing the value of a cell in the code, the cancellation stack is immediately lost, and, of course, this means that ANY work on this sheet cannot be undone.

So, an alternative to this is a trick that surpasses the thought that I did not edit the cell. This code saves the cancellation stack when the date changes:

 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 a cell, use SendKeys to fake cell editing and restore the cursor to its original location. "^; ~" uses Excel "Ctrl +;" shortcut to enter the date. Large! Resolved except ...

This code works fine on my machine (Win7, Excel 2010), but it doesn’t work on an employee computer (Win8, Excel 2010, maybe a little faster). On a Win8 machine (I don’t know if this is the OS, which is the problem, by the way), what happens is that whenever a cell changes, each cell immediately below this cell becomes the current date, and, of course, saving the cancellation history does not make sense, since the execution of Cancel immediately activates the code of the worksheet and turns everything into dates again.

I myself realized that the same thing would happen on my machine if I removed the "Wait" inherent to the SendKeys command. That is, if I use the line:

 SendKeys "^;~", False 

So, I assume that for some reason, even when using the same version of Excel, my computer is waiting for the SendKeys command to complete, but my computer colleague is not. Any ideas?

Answer 1

You're right. This gives this problem in Excel 2010 / Win8.

Try it. Use the custom Wait code 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