Macro to force Excel out of edit-mode

Question

I have a macro that needs to run for update.

If Excel is in edit, need a macro to have excel exit edit mode --- take control.

Answer 1

This is not possible as Excel will not run any macros while in cell editing mode.

Answer 2

The natural way to do this is by using the Application.OnTimer method, but it has the "edit problem" you just noted. The called function will not get executed until the user leaves the edit mode.

You may overcome this: This solution is not very efficient, but you may yield control from a macro and regain it from a timer (or any other event you choose).

The following code is from the Excel timer function help.

While the timer is counting down (or you are awaiting other event), you can continue working on your worksheet.

Sub a()
  Dim PauseTime, Start, Finish, TotalTime
  If (MsgBox("Press Yes to fire update in 1000 secs", 4)) = vbYes Then
      PauseTime = 1000    ' Set duration 1000 secs or whatever.
      Start = Timer    ' Set start time.
      Do While Timer < Start + PauseTime
          DoEvents    ' Yield to other processes - THIS IS THE TRICK
      Loop
      Finish = Timer    ' Set end time.
      TotalTime = Finish - Start    ' Calculate total time.
      MsgBox "Paused for " & TotalTime & " seconds" 'Program your update HERE
   Else
     End
  End If
End Sub

You should invoke this macro beforehand, perhaps at the open workbook event.
Also, it is possible to keep the loop going, repeating the update when you want to.

You should check if this strategy does not interfere with other features used in your particular worksheet.