Solved

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

Posted on 2011-03-03
12
861 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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