A Modeless Dialog Box


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.

image from book
Figure 15-1: This modeless dialog box remains visible while the user continues working.
CD-ROM  

This example, named image from book  modeless userform1.xlsm , is available on the companion CD-ROM.

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.

CROSS-REFERENCE  

Refer to Chapter 19 for additional information about events.

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.

image from book
Figure 15-2: This modeless UserForm displays various information about the active cell.
CD-ROM  

This example, named image from book  modeless userform2.xlsm , is available on the companion CD-ROM.

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.

CROSS-REFERENCE  

Refer to Chapter 29 for more information about class modules.

  • 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.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net