DoEvents and Application.Undo

[tiredofit]

Further to my post here:

https://www.mrexcel.com/board/threads/doevent-allow-value-change-without-stopping-code.1148205/#post-5570534

the code provided by Jaafar Tribak was:

Sub Test()

    Dim I As Long, CTimer As String
    I = 1: CTimer = Now

    Application.Calculation = xlCalculationManual
   
    On Error Resume Next

    Do Until I = 10
        If CTimer <> Now Then
           If Application.CommandBars.GetEnabledMso("MergeCenter") Then
               Sheet1.Cells(I, 1).Value = I
               I = I + 1: CTimer = Now
           End If
        End If
        DoEvents   
    Loop

    Application.Calculation = xlCalculationAutomatic

    MsgBox "Completed"
   
End Sub

I now have an additional requirement.

When running the code, if the user clicks and changes a cell on the worksheet, I want that action to be undone.

I added this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.Undo
    
End Sub

but now when Test is running, if the user changes the value in a cell, the Worksheet_Change event kicks off but crashes on this line:

Application.Undo

with an error message of:

Method 'undo object'_application failed

How can I amend it?

Thanks

 

[Trixterz]

Use selection change...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Undo
End Sub

 

[tiredofit]

Use selection change...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Undo
End Sub

Still crashes the moment I click on a cell.

 

[Jaafar Tribak]

Application.Undo will crash because the Test macro clears the Undo stack .

When running the code, if the user clicks and changes a cell on the worksheet, I want that action to be undone.

Why don't you protect the sheet while the Test Macro is running ... You can set the sheet protection as UserInterfaceOnly to allow the Test Macro to edit the cells while preventing users from editing cells.

 

[tiredofit]

Application.Undo will crash because the Test macro clears the Undo stack .


Why don't you protect the sheet while the Test Macro is running ... You can set the sheet protection as UserInterfaceOnly to allow the Test Macro to edit the cells while preventing users from editing cells.

Two reasons for NOT protecting the sheet:

1. The spreadsheet contains a chart and the user clicks the play button to see the chart moving. Adding DoEvents allows the user to click the stop button to stop the chart playing. Would adding UserInterfaceOnly help here?

2. There is also a speed parameter in the form of a value on the worksheet. Whilst the chart is playing, I want the user to be able to change the value of this parameter and not stop the chart.

However that cell is the only cell I want users to change. Changing other cells, I want the Application.Undo to "kick in".

 

[tiredofit]

Two reasons for NOT protecting the sheet:

1. The spreadsheet contains a chart and the user clicks the play button to see the chart moving. Adding DoEvents allows the user to click the stop button to stop the chart playing. Would adding UserInterfaceOnly help here?

2. There is also a speed parameter in the form of a value on the worksheet. Whilst the chart is playing, I want the user to be able to change the value of this parameter and not stop the chart.

However that cell is the only cell I want users to change. Changing other cells, I want the Application.Undo to "kick in".

I've tried your suggestion and got it to work.

I protected the sheet but the speed parameter is not locked, so the user can freely change its value whilst the chart is playing.

Thanks