Solved

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

Posted on 2011-03-03
12
850 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now