Pause or stop VBA for a manual edit to the sheet
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Pause or stop VBA for a manual edit to the sheet

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    396
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pause or stop VBA for a manual edit to the sheet

    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.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,686
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    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.
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Dec 2009
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    What you are describing can’t be done with a standard excel msgbox. You could manage it with a modeless userform though.
    My favorite Excel Add-in:= Nutilities

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,992
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    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 by JLGWhiz; Mar 30th, 2019 at 04:37 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,734
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    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")
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,686
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    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 by DanteAmor; Mar 30th, 2019 at 08:56 PM.
    Regards Dante Amor

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,734
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    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.

    Quote Originally Posted by DanteAmor View Post
    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 by My Aswer Is This; Mar 30th, 2019 at 09:18 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,686
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    Quote Originally Posted by My Aswer Is This View Post
    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()
        Call only_a_script
    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

    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,992
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    Quote Originally Posted by My Aswer Is This View Post
    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 by JLGWhiz; Mar 31st, 2019 at 09:46 AM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,734
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Pause or stop VBA for a manual edit to the sheet

    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.
    Quote Originally Posted by JLGWhiz View Post
    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •