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.

 

[DanteAmor]

That could be done from a userform. At a certain moment, the macro stops, the place where the macro stopped is stored somewhere, you can open the userform in modal, to edit the sheet. Then you return to the userform and press continue or cancel.

 

[=ODIN=]

What you are describing can’t be done with a standard excel msgbox. You could manage it with a modeless userform though.

 

[JLGWhiz]

Tjere is a way to do that, but it is kind of klunky. You put your message box in where you want to do the edit, then put a breakpoint immediately after it in the code. When you click Ok on the message box it will go into break mode and you can do limited editing on the worksheet(s) and manually resume the code after editing. But other than that, once the code starts, the application does not have a built in pause mechanism for editing that allows you to resume at the same place you stopped.

 

[My Aswer Is This]

I would like to see some specific code to do something like this:

I have seen this same type question asked several times and have never seen a answer that works.
With or without using a UserForm.

So for example show me some code that would In Sheet named ("Me") Put:
"Alpha" in Range("A1")
"Bravo" in Range("A2")

Then have the script stop so I can do something manually.

Then have the same script continue and Put "Charlie" in Range("A3")

 

[DanteAmor]

the place where the macro stopped is stored somewhere

For example, in the sheet "temp" you store the number 3. You go to the sheet and make the changes. You return to the form and press a "continue" button, the button executes the same script and passes as a parameter the number stored in the sheet "temp". Something like that

 

[My Aswer Is This]

Would still like to see the script I asked for.
The only way I can see this happening would be:

You have one script that does several Task. Then that script ends and you do what you want manually on the sheet then you press a different button to start another script. I know of no way to tell a script to stop at a certain point and then some how have the same script proceeding on when something else happens.

For example, in the sheet "temp" you store the number 3. You go to the sheet and make the changes. You return to the form and press a "continue" button, the button executes the same script and passes as a parameter the number stored in the sheet "temp". Something like that

 

[DanteAmor]

Would still like to see the script I asked for.
The only way I can see this happening would be:

You have one script that does several Task. Then that script ends and you do what you want manually on the sheet then you press a different button to start another script. I know of no way to tell a script to stop at a certain point and then some how have the same script proceeding on when something else happens.

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

Option Explicit
Dim temp As Worksheet, data As Worksheet
'
Private Sub CommandButton1_Click()
[COLOR=#0000ff]    Call only_a_script[/COLOR]
End Sub
'
Sub only_a_script()
    Dim n As Long, m As Long, i As Long
    n = 10 'the macro stops every 10 iterations
    m = 0
    For i = temp.Range("A1").Value To 75000
        'do task1
        data.Range("A" & i).Value = WorksheetFunction.RandBetween(1, 75000)
        'do task2
        'do task3
        'do task4
        m = m + 1
        If m = n Then
            MsgBox "Press ok, move to the sheet and make the changes." & vbCr & vbCr & _
                   "Then return to the Userform and press button1 to continue the process", vbOKOnly
            temp.Range("A1").Value = i + 1
            data.Select
            Exit For
        End If
    Next
End Sub
'
Private Sub UserForm_Activate()
    Set temp = Sheets("Temp")
    Set data = Sheets("Data")
    '
    temp.Range("A1").Value = 1  'initial value
End Sub

In a module:

Sub openform()
    UserForm1.Show vbModeless
End Sub

Or change the property ShowMoidal = False of the userform

 

[JLGWhiz]

I would like to see some specific code to do something like this:

I have seen this same type question asked several times and have never seen a answer that works.
With or without using a UserForm.

So for example show me some code that would In Sheet named ("Me") Put:
"Alpha" in Range("A1")
"Bravo" in Range("A2")

Then have the script stop so I can do something manually.

Then have the same script continue and Put "Charlie" in Range("A3")

You cannot do it with code alone. VBA will not let you stop and edit and just continue from the last point without interrupting the code itself. That is why I said that breakpoints would need to be entered in the code to interrupt it and it will need to be manually restarted. It is a klunky way of doing it, but it will allow a user to edit at specific points in the code. Would I do it? No. I would try to design my database and my code so that it did not require edits during runtime. Editing can be built in with UserForms, Textboxes, etc. which provide for user input option. But the code cannot be stopped for UI edits and then resume.

 

[My Aswer Is This]

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.

You cannot do it with code alone. VBA will not let you stop and edit and just continue from the last point without interrupting the code itself. That is why I said that breakpoints would need to be entered in the code to interrupt it and it will need to be manually restarted. It is a klunky way of doing it, but it will allow a user to edit at specific points in the code. Would I do it? No. I would try to design my database and my code so that it did not require edits during runtime. Editing can be built in with UserForms, Textboxes, etc. which provide for user input option. But the code cannot be stopped for UI edits and then resume.