Solved

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

Posted on 2011-03-03
12
875 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:bearblack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35027622
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
 
LVL 2

Author Comment

by:bearblack
ID: 35027652
I don't want the user to have to get into the VBE if at all possible
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35027730
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:bearblack
ID: 35027746
I am new to this -- How is that done? do you have a sample
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35027869
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
 
LVL 45

Expert Comment

by:patrickab
ID: 35036983
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35037002
That requires the user access VBA IDE though. At least to stop/resume the code. And the OP doesn't want that.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35038434
>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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35038469
>> 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
 
LVL 45

Expert Comment

by:patrickab
ID: 35038790
But there's more than only one way to interact with a MsgBox. It all depends on how it's set up.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35039502

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
 
LVL 1

Accepted Solution

by:
martyk earned 500 total points
ID: 35072100
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question