?
Solved

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

Posted on 2011-03-03
12
Medium Priority
?
884 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

765 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