Halting Macro for User Input in Excel

 Last reviewed: August 20, 1995
Article ID: Q62290
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.0, 2.1, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SUMMARY

Lotus 1-2-3 allows the use of the "?" (the question mark [without quotation marks]) to temporarily suspend macro execution while waiting for your input. Microsoft Excel, however, does not allow halting the macro to wait for your input without using an INPUT() or similar command.

To mimic the "?" in a Lotus macro, the following macro segment can be used to halt a macro to wait for your input, without using an INPUT() or related function: 

   On.time sub-macro to halt macro and wait for user input

   =ON.TIME(NOW()+0.0000000001,"(reference to resume calling macro)")
   =SEND.KEYS("{f2}")
   =RESTART()
   =RETURN()
The RESTART() function tells the macro not to return to the calling macro. This method is used because the ON.TIME() function is used to restart the macro in a specified amount of time.

This same method can be used to bring up a dialog box for which Excel does not provide a macro command, such as the Formula Notes dialog box. A modification to the SEND.KEYS() function to bring up the desired dialog box is all that is needed. 

   =ON.TIME(NOW()+0.0000000001,"(reference to resume calling macro)")
   =SEND.KEYS("(%r)n")
   =RESTART()
   =RETURN()
NOTE: the information above also applies to version 5.0 and later only when using version 4.0 macro language.

KBCategory: kbusage kbmacro
KBSubcategory:

Additional reference words: 7.00 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 4.0a 4.00a 5.0 5.00 pause pausing

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 20, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.