Halting Macro for User Input in Excel

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.

Additional query words: 2.0 2.01 2.1 2.2 2.20 2.21 3.0 3.00 4.0 4.00 4.0a 4.00a 5.0 pause pausing

Keywords :
Version :
Platform :
Issue type :