How do you pause an Excel Macro to varify data then continue with the macro?

I have an extensive macro (below as an example of where I would like to pause the macro) but I need to in various araes to pause the macro for the user to fill in the displayed blank feilds and then proceed. I created a userform (click command listed). I will need to do this 5 times throughout the macro.

Private Sub CommandButton1_Click()
   GoTo 0
End Sub
Range("YEWIP_Data").AutoFilter Field:=12, Criteria1:="="
    Range("L7", Cells(YEWIPR1, "L")).SpecialCells(xlCellTypeVisible). _
        FormulaR1C1 = "=IFError(Vlookup(RC1,WMS_Lookup,6,false),"""")"
    UserForm1.Show vbModeless
    YEWIP.ShowAllData

Open in new window

LVL 2
bearblackGlobal Program ManagerAsked:
Who is Participating?
 
martykConnect With a Mentor Commented:
Here is a way to do it with the modeless userform using the DoEvents keyword.

Sub test()

' put code block 1 here

UserForm1.Show vbModeless
UserForm1.continue_button = False '

UserForm1.TextBox1 = "fill in dat on sheet1 in cell a1" ' instructions to user or results
UserForm1.CommandButton1.Caption = "Continue" ' describe next action

While UserForm1.continue_button = False
    DoEvents
Wend

' put code block 2 here

UserForm1.TextBox1 = "you filled in " & Sheet1.Range("a1").Value & ". press continue to end this macro" ' instructions to user or results
UserForm1.continue_button = False
UserForm1.CommandButton1.Caption = "Terminate macro" ' describe next action

While UserForm1.continue_button = False
    DoEvents
Wend

UserForm1.Hide

End Sub

Open in new window


I have attached the working xls file for easy testing.

EE-26861197-mk1.xls

Marty
0
 
CluskittCommented:
You can use a breakpoint. Just place one in the line you want to check. It will stop BEFORE it runs that line. You can then continue running the rest of the code (F5) or run line by line (F8), or even drag the yellow line to another line of code.
0
 
bearblackGlobal Program ManagerAuthor Commented:
I don't want the user to have to get into the VBE if at all possible
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CluskittCommented:
You can break the code into two macros. Or you can open a form and place the execution of the rest of the code on the exit function of that form.
0
 
bearblackGlobal Program ManagerAuthor Commented:
I am new to this -- How is that done? do you have a sample
0
 
CluskittCommented:
Try this site, it should help you creating one and placing code on the unload function: http://www.exceltip.com/st/Create_User_Forms_in_Microsoft_Excel/629.html

What you want is basically create a form that is similar to button, place a text like: "Check the data. Click below to continue."
Then place a button with "Done" that will close the form and run the rest of the code. Or something similar.

It would be simpler to just break it in two, though. I don't know how you're running the macros, but I'll assume it's with a button. Just create a new button and break the code into 2 macros. It's much easier to accomplish.
0
 
patrickabCommented:
bearblack,

Use MsgBox to report on macro progress or intermediate results. It's quick and easy to do. You can then use CTRL+Break when the message box is visible and the macro will stop at that point - and oyu can organise to have the information on whether you should stop the macro in the MsgBox.

Patrick
0
 
CluskittCommented:
That requires the user access VBA IDE though. At least to stop/resume the code. And the OP doesn't want that.
0
 
patrickabCommented:
>That requires the user access VBA IDE though. At least to stop/resume the code. And the OP doesn't want that.

In what way does that mean the use has to have access to the VBE? The user only has to respond to the Msgboxes - that's all. No big deal.
0
 
CluskittCommented:
>> You can then use CTRL+Break when the message box is visible and the macro will stop at that point

As soon as you hit CTRL+Break you will have to either end or debug the code. If you chose debug (the only option that will allow you to later resume the code) you will have to access the VBA IDE to resume the code. If you don't use CTRL+Break, you can't interact with Excel while the msgbox is open.
0
 
patrickabCommented:
But there's more than only one way to interact with a MsgBox. It all depends on how it's set up.
0
 
CluskittCommented:

Again, from the OP:

>> but I need to in various araes to pause the macro for the user to fill in the displayed blank feilds and then proceed

Can't do that with an MsgBox. Or rather, you can stop it with an MsgBox, but not resume it later. You could, depending on how the file is done and what is necessary to validate, use InputBoxes to fill cells on the fly. But in the end, that's all too complicated. Easier to use a form or break the code. Or even add a validation that stops execution when conditions aren't met.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.