Modeless Dialog

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

A

E L

D E

F

G

" 1

1

Win«

3

frndiKT 'jIh llrtlrc

itrUntt PiinlTiitsI

i

WKfcltl

&raH »

M&13 *i.7S

VupucM«

JMJM fj <AWAi J »LI*

D

Hmfc-^

UliM I

?

njllmcmtrt

Sai.M 1

SBAI aw

lotil:

iJ.i.'J.iJ u

H

10

tilt IH

Q

-

LI l-i i«

7n

17

IS

H

1

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

CD- This example, named ■■*■ modeless userforml .xlsm, is available on the companion ROM CD-ROM.

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

CD- This example, named ■■*■ modeless userforml .xlsm, is available on the companion ROM 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- Refer to Chapter 19 for additional information about events.

REFERENCE

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.

*L

I t 9 t

-

i

J

J¥DANI A ¿VMfrN t PtiHum ( full).' m fhwve

•r

■4.0%

7

Mrt

JJH

i.m ».7»

LC'.TVJ

'

IULII

i IJ14 s

ALUS § t.IWl *J4.Mi|

1

ID

u

inivSw ¡WC#T1 :f*fA*Cb$

tl

U

LV

11

IPiMwOffcKdk t ttmprir Trif

ff frWipfiMt r Shi- hn, pnlK: -expi

jim J

in

17

4GCV/H

I HLWJ

V'

■Pi

Itnfcw r i-kK .tr *,*«Qjfe_B"|l!l r«; _lt"

-Iti-iAJ

H

rtnii -sjMpaca;

n

h«». /nfaii

OflCBMi h»)

u

LHpwd»-.'jfe Ti^KT.'.'rirrr

fcraiJai.

n

51_

ihKrii^-Cfll* a:

'J* 4 rata-!». S

M iJ

■ ► Sh*+t i

H -

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

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

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

CD- This example, named modeless userform2 .xlsm, is available on the companion ROM 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- Refer to Chapter 29 for more information about class modules.

REFERENCE

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

4 PREV

NEXT

0 0

Post a comment