Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Most dialog boxes that you encounter are modal dialog boxes, which must be dismissed from the screen before the user can do anything with the underlying application. Some dialogs, however, are modeless, which means the user can continue to work in the application while the dialog box is displayed.

To display a modeless UserForm, use a statement such as

UserForm1.Show vbModeless

The word vbModeless is a built-in constant that has a value of . Therefore, the following statement works identically:

UserForm1.Show 0

Figure 15-1 shows a modeless dialog box that displays information about the active cell. When the dialog box is displayed, the user is free to move the cell cursor, activate other sheets, and perform other Excel actions.

Figure 15-1: This modeless dialog box remains visible while the user continues working.

The key is determining when to update the information in the dialog box. To do so, the example monitors two workbook events: SheetSelectionChange and SheetActivate . These event handler procedures are located in the code module for the ThisWorkbook object.

The event handler procedures follow:

Private Sub Workbook_SheetSelectionChange _ (ByVal Sh As Object, ByVal Target As Range) Call UpdateBox End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call UpdateBox End Sub

The two previous procedures call the UpdateBox procedure, which follows :

Sub UpdateBox() With UserForm1 ' Make sure a worksheet is active If TypeName(ActiveSheet) <> "Worksheet" Then .lblFormula.Caption = "N/A" .lblNumFormat.Caption = "N/A" .lblLocked.Caption = "N/A" Exit Sub End If .Caption = "Cell: " & ActiveCell.Address(False, False) ' Formula If ActiveCell.HasFormula Then .lblFormula.Caption = ActiveCell.Formula Else .lblFormula.Caption = "(none)" End If ' Number format .lblNumFormat.Caption = ActiveCell.NumberFormat ' Locked .lblLocked.Caption = ActiveCell.Locked End With End Sub

The UpdateBox procedure changes the UserForm's caption to show the active cell's address; then it updates the three Label controls ( lblFormula , lblNumFormat , and lblLocked ).

Following are a few points to help you understand how this example works:

• The UserForm is displayed modeless so that you can still access the worksheet while it's displayed.

• Code at the top of the procedure checks to make sure that the active sheet is a worksheet. If the sheet is not a worksheet, the Label controls are assigned the text N/A .

• The workbook monitors the active cell by using a Selection_Change event (which is located in the ThisWorkbook code module).

• The information is displayed in Label controls on the UserForm.

Figure 15-2 shows a more sophisticated version of this example. This version displays quite a bit of additional information about the selected cell. Long-time Excel users might notice the similarity to the Info window - a feature that was removed from Excel several years ago. The code is too lengthy to display here, but you can view the well-commented code in the example workbook.

Figure 15-2: This modeless UserForm displays various information about the active cell.

Following are some key points about this more sophisticated version:

• The UserForm has a check box (Auto Update). When this check box is selected, the UserForm is updated automatically. When Auto Update is not turned on, the user can use the Update button to refresh the information.

• The workbook uses a class module to monitor two events for all open workbooks: the SheetSelectionChange event and the SheetActivate event. As a result, the code to display the information about the current cell is executed automatically whenever these events occur in any workbook ( assuming that the Auto Update option is in effect). Some actions (such as changing a cell's number format) do not trigger either of these events. Therefore, the UserForm also contains an Update button.

• The counts displayed for the cell precedents and dependents fields include cells in the active sheet only. This is a limitation of the Precedents and Dependents properties.

• Because the length of the information will vary, VBA code is used to size and vertically space the labels - and also change the height of the UserForm if necessary.