Solved

Pause macro for data entry & prompt user with a message to enter the data...

Posted on 2008-10-19
5
683 Views
Last Modified: 2013-11-25
I wrote a series of individual macros in excel that I want to operate sequentially.  I'm ready to compile them into one grand macro, but I need the grand macro to pause while the user enters data into the spreadsheet.  Once the data is entered, I'd like the macro to run through to completion.

Suggestions?
0
Comment
Question by:ronadair
  • 3
5 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 22754203
Create a new user form with an OK button and the text (in a label):

"Enter the data required for me to continue. Click OK when done."

Split your grand macro into two parts:

Public Sub Grand1()
...
   UserForm1.Show vbModeless
End Sub

Public Sub Grand2()
...
End Sub

In the click handler for the OK button of the user form add this code:

   Me.Hide
   Grand2

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 22754261
Attached is an example workbook.

Kevin
Q-23828249.xls
0
 
LVL 7

Expert Comment

by:hippohood
ID: 22756510
If you have no need for creaing an extra foorm you may use InputBox function. Below is the sample from MS VBA Help
"Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box... If the user clicks OK or presses ENTER , the InputBox function returns whatever is in the text box. If the user clicks Cancel, the function returns a zero-length string ("")."  

Dim Message, Title, Default, MyValue

Message = "Enter a value between 1 and 3"    ' Set prompt.

Title = "InputBox Demo"    ' Set title.

Default = "1"    ' Set default.

' Display message, title, and default value.

MyValue = InputBox(Message, Title, Default)
 

' Use Helpfile and context. The Help button is added automatically.

MyValue = InputBox(Message, Title, , , , "DEMO.HLP", 10)
 

' Display dialog box at position 100, 100.

MyValue = InputBox(Message, Title, Default, 100, 100)

Open in new window

0
 

Author Closing Comment

by:ronadair
ID: 31507670
Thanks... the procedure works.  One more question:  what code should I enter to exit the sequence so I can start from the beginning in case the data is in some way not ready to enter?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 22760752
hippohood,

That does not solve the problem. VB and Excel message and input boxes are displayed modally which prevents the user from interacting with the worksheet. The Asker specifically stated that he wants the user to interact with the worksheet while the dialog is displayed.

Kevin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 43
File not loading into PowerPivot 4 9
How to move the last 4 digits of a number to a new column 8 43
Auto Adjust Percent rate 5 30
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

17 Experts available now in Live!

Get 1:1 Help Now