Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PAUSE EXCEL MACRO FOR INPUT OF DATA VARIABLE
How do I pause a macro in Excel 2003 for input of a data variable and then
restart running the remainder of the macro |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PAUSE EXCEL MACRO FOR INPUT OF DATA VARIABLE
Maybe you can use Inputbox() to get what you want.
dim myStr as string 'your code that does something mystr = inputbox(Prompt:="please type something") msgbox "Hey, you typed: " & mystr 'your code that does more. lynne b wrote: How do I pause a macro in Excel 2003 for input of a data variable and then restart running the remainder of the macro -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PAUSE EXCEL MACRO FOR INPUT OF DATA VARIABLE
Have you tried InputBox()? -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=550508 |
#4
|
|||
|
|||
Quote:
Basically, cut the macro into two parts. Run the first half and then at the end of that macro call the First_Half macro Edit the Second_Half macro to call the macro that contains the rest of your programming. Knowledge Base Creating a PAUSE Button in Visual Basic for ApplicationsArticle ID: 131847 Article Last Modified on 8/15/2005 -------------------------------------------------------------------------------- APPLIES TO Microsoft Excel 95 Standard Edition Microsoft Excel 5.0c Microsoft Excel 5.0a for Macintosh Microsoft Visual Basic for Applications 1.0 -------------------------------------------------------------------------------- This article was previously published under Q131847 SUMMARY In Visual Basic, Applications Edition, there is no built-in equivalent for the MS Excel 4.0 PAUSE() macro function. The Visual Basic code in this article provides an alternative. You might want to use this code to pause execution of your macro while a user enters data directly in a sheet or manipulates menus. MORE INFORMATION Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Microsoft Excel does not have a "Macro Paused" Toolbar that can be invoked by a single command. This is true even when you run an MS Excel 4.0 macro. The following Visual Basic code comprises two sub procedures that you can modify to create the effect of pausing a macro. If you have a single procedure within which you need to pause execution until the user desires to resume execution, split your sub procedure into two halves. On the last line of the first half of the sub procedure, call the "First_Half" sub procedure. Within the "Second_Half" sub procedure, substitute the name of the sub procedure that is the second half of your original sub procedure on the line where the MsgBox is currently located. ' This Sub procedure should be called on the last line of your original ' Sub procedure. Sub First_Half() ' Create a toolbar. Toolbars.Add "resume" ' Add the button to the toolbar. ' Note that if you are using Microsoft Excel version 5.0, ' comment out the "StatusBar:" argument of the Add method and remove ' the comma and line-continuation character after "Enabled:=True" Toolbars("resume").ToolbarButtons.Add Button:=102, _ OnAction:="Second_Half", Pushed:=False, Enabled:=True, _ StatusBar:="Click here to resume macro" ' Name the button. Toolbars("resume").ToolbarButtons(1).Name = "Resume Macro" ' Make sure the bar is not docked. Toolbars("resume").Position = xlFloating ' Position the bar midway between the left and right of the window. Toolbars("resume").Left = _ (ActiveWindow.Width + Toolbars("resume").Width)/ 2 ' Position the bar midway between the top and bottom of the window. Toolbars("resume").Top = _ (ActiveWindow.Height + Toolbars("resume").height)/ 2 ' Make sure the bar is showing. Toolbars("resume").Visible = True End Sub Sub Second_Half() ' Delete the toolbar. Toolbars("resume").Delete ' Insert the name of the second half of your procedure in place of ' this message box. ' ' NOTE: Any variables dimensioned in the first half of your ' procedure may lose their values before this sub procedure is ' executed. You will need to be certain any variables used by the ' procedure you name in place of this message box are dimensioned at ' the Module level. MsgBox "after" End Sub REFERENCES For more information, see the Visual Basic User's Guide, Chapter 12, "Managing Toolbars and Toolbar buttons with Visual Basic" Additional query words: XL Keywords: kbcode kbhowto kbprogramming KB131847 -------------------------------------------------------------------------------- Send feedback to Microsoft © Microsoft Corporation. All rights reserved. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Fetching External Data from Excel | Excel Discussion (Misc queries) | |||
Export Excel Data to Access With a Macro!!! | Excel Discussion (Misc queries) |