Pause or stop VBA for a manual edit to the sheet

Javi

Active Member
Joined
May 26, 2011
Messages
396
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,218
Office Version
2007
Platform
Windows
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=

Active Member
Joined
Dec 3, 2009
Messages
288
What you are describing can’t be done with a standard excel msgbox. You could manage it with a modeless userform though.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,063
Office Version
2013
Platform
Windows
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.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,218
Office Version
2007
Platform
Windows
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,218
Office Version
2007
Platform
Windows
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

Code:
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:
Code:
Sub openform()
    UserForm1.Show vbModeless
End Sub
Or change the property ShowMoidal = False of the userform

 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,063
Office Version
2013
Platform
Windows
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.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,800
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top