How to Use Ctrl-Y (F4) Feature During Macro Running in Background?

[greenbcz]

Hello,

I have a macro which uses application.ontime.
When it runs, Excel's Control+Y (F4) feature stops working. This feature is required by users of the macro.

The macro runs fine and users can carry on working while it runs. There is just one specific issue I need to address.
The problem is that, while the macro is running, the user is unable to use the Control+Y (F4) feature to repeat the last operation. I'm looking for a way round this limitation; so users can be running the macro in the background, perform an operation and user control-Y whenever they want to.

I need someone to return this same worksheet to me so that when I run the macro, I can perform an operation on one cell, move to another cell and type Control-Y to repeat that last operation (while the macro is still running and before the timeout).

Thank you!

[Jan Karel Pieterse]

Your problem is that the OnTime method is actually added to the repeat last action list and thus replaces what the user last did.

The only way around that is by using the windows API timer with some callback hooks. Tricky and dangerous because it will call VBA even when Excel would normally disallow that. Hence (in the example) the code that checks whether Excel is in edit mode.

Proceed at own risk....



Option Explicit
'API Declarations
Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long

' Public Variables
Public mlTime As Long
Public mlTimerID As Long
Public mbTimerState As Boolean
Sub TimerOn()
mlTime = 1
If mbTimerState = False Then
mlTimerID = StartTimer
End If
If mlTimerID = 0 Then
MsgBox "Unable to create the timer", vbCritical + vbOKOnly, "Error"
Exit Sub
End If
mbTimerState = True
End Sub
Sub TimerOff()
If mbTimerState = True Then
mlTimerID = KillTimer(0, mlTimerID)
If mlTimerID = 0 Then
MsgBox "Unable to stop the timer", vbCritical + vbOKOnly, "Error"
End If
mbTimerState = False
End If
Application.StatusBar = False
End Sub
Function StartTimer()
StartTimer = SetTimer(0, 0, mlTime * 1000, AddressOf TimedSub)
End Function
Sub TimedSub()
If IsExcelInEditMode Then
Application.StatusBar = Now & ", Editing a cell!"
Else
Application.StatusBar = Now & ", Not editing a cell!"
End If
End Sub
Private Function IsExcelInEditMode() As Boolean
IsExcelInEditMode = (Not Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=18, recursive:=True).Enabled)
End Function