Pausing Macros for User Input

For those who have been around spreadsheet programs for quite some time, you may remember the old {?}~ command that was available in Lotus 1-2-3. This command allows you to pause the macro while the user enters data in the spreadsheet.

Excel doesn't include the same capability, but it does have ways that you can prompt the user for input. The two primary methods are these:

• MsgBox function. This function displays a dialog box and a set of buttons. When the user clicks on a button, an integer value is returned that indicates the button clicked. Your program can then take action based on the value returned.
• InputBox function. This function displays a dialog box an allows the user to type a response. Whatever the user types is returned as a string to the macro.

Both of these functions have been discussed in other issues of ExcelTips; I won't go over them again here. Based on the user's input, you can modify what the macro does in any way desired. The only drawback to the functions is that they only return a single, discrete piece of data. In other words, they aren't designed to allow the user to input a range of cells and then continue processing. For instance, if you wanted to ask the user to provide five values destined for five cells, you would need to present an InputBox five times, depositing the user's responses into the desired cells one after the other.