Pause or stop VBA for a manual edit to the sheet

[Javi]

Hi All,

I have a sheet with approximately 75K rows. My code is adding columns, formulas and formatting working great. I would like to stop the code in the middle/at specific points with a message box to allow me to do a manual edit on the sheet. For instance, have a message box pop up with the message of what edit to perform (This will be predetermined not any type of calculation) and a continue button that will allow the code to continue after I finished my manual edit.

I seem to recall we can have the sheet active behind the message box so the message box would still be there as I am performing the manual edit. Once the manual edit is completed I could click continue and the code will continue or start/call a second macro.


Thank you, any advice or input would be appreciated.

 

[JLGWhiz]

That is what I always thought. But I was getting the impression others here were saying this can be done. This is a fairly often question people ask here.

the method suggested by @DanteAmor will allow limited editing, but it also can have disatrous results by allowing values already caluculated by the code to become invalid. Also, certain changes can crash the code. My recommendation would be to write short macros that run to completion, then do the editing between macros.

 

[=ODIN=]

While you guys are technically correct in that you can’t stop and start a sub while you edit a sheet, you can use a mode less userform with a single button and sub that will simulate the effect. The designer of this sub should know what types of edits need to happen. So the sub just needs to validate what type of edit has taken place each time it is run, then it can determine what action to take. While you are technically rerunning the same sub over and over with each button press, from a user’s standpoint it would feel like the code was pausing.

 

[DanteAmor]

We all know that you can not pause a macro and return to execution, the attempt is a simulation.
I ignore the user's reason why he wants to go to the sheet to edit it.
A good option is to put the data in the userform controls as suggested by JLGWhiz; and send them to the sheet. But I suppose the user does not know this excel functionality or in this case a userform.


But in the end, the data belongs to the user, he decides what to do with his data. The sheet can be blocked and allow the editing of certain cells, I do not know if it is to put comments or to simply put a happy face in the cell.

 

[My Aswer Is This]

I'm not sure about this:
You said:

We all know that you can not pause a macro and return to execution.

I have seen this same type question on this forum maybe 50 times or more in the last couple years.
I really think some people believe you can do this. And maybe it would be nice if you could.
​

We all know that you can not pause a macro and return to execution, the attempt is a simulation.
I ignore the user's reason why he wants to go to the sheet to edit it.
A good option is to put the data in the userform controls as suggested by JLGWhiz; and send them to the sheet. But I suppose the user does not know this excel functionality or in this case a userform.


But in the end, the data belongs to the user, he decides what to do with his data. The sheet can be blocked and allow the editing of certain cells, I do not know if it is to put comments or to simply put a happy face in the cell.

 

[DanteAmor]

I really think some people believe you can do this.
​

Who?

We hope that ms-excel develops the application for batch processes.

 

[JLGWhiz]

think about it guys. They already have a built in break for errors with debug capability where you can do editing and resume the macro once you have fixed the problem. It wouldn't take much more to develop a mthod with user interface that allows them to build a stop point in their code so they can edit and just click a button to resume the code. But I doubrt that MS will do that.

 

[DanteAmor]

in agreement

 

[Javi]

Thank you everyone for all of the input and discussion on this topic. I use the Userform method. I separated my original code into several codes where I wanted the code to stop/pause. In the properties of the Userform I set “Show modal = False “ allowing me to edit the sheet with the Userform still open. The continue button on the Userform simply called the rest of the code.

FYI - the reason why I needed to stop/pause the code was my sheet ended up with 200K rows and I needed to delete out approximately hundred 190K based on a cell value. For some reason the code took too long to validate each row and delete, so I did it manually. The code took 20 minutes to delete the lines and manually it took about two minutes.

Below are the 2 delete row codes I tried.

Sub DeleteRows_with_NoValue()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim i As Long
    FirstRow = 2
        For i = Range("M" & Rows.Count).End(xlUp).Row To 1 Step -1
                   If Not (Range("M" & i).Value > 1) Then
            Range("M" & i).EntireRow.Delete
        End If
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Sub DeleteRow()
Dim r As Long
Dim FirstRow As Long
Dim LastRow As Long


'Need to avoid the top blank rows and grand total
FirstRow = 2
LastRow = Cells(Rows.Count, "M").End(xlUp).Row - 0
For r = LastRow To FirstRow Step -1
If Cells(r, "M") > 1 And Cells(r, "M") > 1 Then
'Cells(r, "M") = "Delete" 'remove if code OK
Rows(r).Delete 'use this if code OK
End If
Next r
End Sub

Thanks again.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[=ODIN=]

It looks like your code was deleting one row at a time. Have you tried applying an autofilter and then deleting all the rows at once?