Some useful Application object methods

The Application object includes these useful methods and properties.

DisplayAlerts allows you to turn off a warning message using a statement of the form Application.DisplayAlerts = False. Use this if you want to prevent a warning message from appearing whenever you delete a worksheet. You can turn it back on with the line Application.DisplayAlerts = True. Use with care for though warning messages can irritate us, we certainly miss them if we inadvertently delete work which has taken much effort on our part. Cursor is a property. Sometimes, when a lengthy macro is running, the cursor will change to an hourglass, then momentarily back to normal, only to resume a wait status again. Users sometimes find this irritating. The statement: Application. Cursor = xlWait turns the cursor to an hourglass. Application. Cursor = xlNormal restores the cursor to normal.

The ScreenUpdating property relates to the display. The statement: Application.ScreenUpdating = False will freeze the screen while a macro runs and thus prevent any flickering. The screen will remain frozen until you assign the property value back to True, or when the macro finishes executing.

InputBox is a method that uses the Application object to prompt for a range. The following statement illustrates its use:

myRange = Application.InputBox (prompt: = "Enter the range required", Type: = 8)

The InputBox method may look similar to the InputBox function which we looked at in Chapter 3, however, it is very different in practice. The InputBox method of the Application object here has a more flexible format than the InputBox function, in that we can specify the type of data that has to be input via the dialog box. For example, in the statement above we have specified Type: = 8, meaning that the data input is expected to be a cell range. If the user attempts to input anything other than a cell range, it will not be accepted and will invoke an error message. This is very useful if you want to restrict the type of data that can be used with a program input. More examples that use the InputBox Application object method will be given in Chapter 6. Note that the user can type in the cell range in the usual format, or can select the range using the mouse.

0 0

Post a comment