7.4 The MS Forms Controls

The following subsections describe one after the other the predefined controls in the MS Forms library. The section titles relate to the texts that are shown when you let the mouse linger over one of the symbols in the toolbox window. Further examples on the use of individual controls and associated programming techniques are to be found in the two sections following.

Common Features

The MS Forms controls have a number of common properties, methods , and events that we describe here in order to avoid excessive repetition. These and some further common features can be found in the object browser under the MS Forms Control object.

The properties Cancel and Default identify the controls of a form that can be selected with Esc and Return, respectively (normally with the Cancel and OK buttons ). The properties can be set to True only for a single control in a form. (The form editor automatically resets the property to False for all other controls.)

With ControlSource you can link the contents of a control with the contents of a cell . Changes in the table then are reflected automatically in the content of the control. (In the case of listboxes RowSource can be used to synchronize the entire list with a range of cells in a worksheet.)

The Tag property usually is of help in managing controls. A character string can be stored in the control that is not displayed.

The Visible property governs the visibility of the control. This property can be used to make individual controls appear and disappear as needed.

COMMON PROPERTIES
Cancel	True if a control can be selected by means of Esc
ControlTipText	yellow infotext (tooltip text)
ControlSource	creates the linkage to a cell in a worksheet
Default	True if the control can be selected by Return
RowSource	creates the linkage to a range of cells (for listboxes)
TabIndex	the number in the tab order
TabStop	True if the control can be selected by Tab
Tag	invisible supplementary information that may help in control management
Visible	True if the control is visible

COMMON METHODS
SetFocus	moves the input focus to a control

COMMON EVENTS
Enter	the control has received the input focus
Error	an error has occurred
Exit	the control has given up the input focus

Label

The label box, true to its name , provides labels for the form (see Figure 7-10). A label is normally placed next to or above other controls, and it should offer a hint as to their function or the type of input that is expected. The text is set with the property Caption and can extend over several lines, in which case WordWrap must be set to True . The text can be aligned left, center, or right ( TextAlign ). If AutoSize is set to True , then the size of the label box is automatically adjusted to fit the length of the text.

Figure 7-10: Some representative forms of the label box

In contrast to the Excel 5/7 label box, the font and color can be freely set with the properties Font , BackColor , and ForeColor . Further options exist for the border ( BorderStyle , BorderColor ), and the property SpecialEffect governs the type of 3-D effect the buttons have. Finally, a bitmap can be displayed in the label ( Picture , PicturePosition ).

LABEL	PROPERTIES
AutoSize	size of the control fits the text
BackColor	background color
BorderColor	border color
BorderStyle	border yes/no
Caption	the displayed text
Font	font style
ForeColor	text color
Picture	bitmap
PicturePosition	position at which the bitmap is displayed
SpecialEffect	3-D effect
TextAlign	text alignment (left, right, center)
WordWrap	line breaking
Click	the label box was clicked

TextBox

Text boxes make possible the input of text. In contrast to the text box of Excel 5/7, the input of cell references is not permitted. For this purpose the RefEdit control is provided in Excel. This is described below.

Many of the properties of the text box are identical to those of the label box, so we may avoid covering that ground here. Access to the displayed text is achieved with Text . The number of characters can be determined with Len(textfield.Text) , the number of lines with the property LineCount , and the current line with CurLine .

With the properties MultiLine and Scrollbars a multiline text input is made possible, with the display of scroll bars if applicable (Figure 7-11). With PasswordChar you can set a character (usually *) that is shown instead of the input text. In this way you can accept the input of a password without any curious passersby looking over the user 's shoulder seeing what is being input.

Figure 7-11: Various forms of text boxes

With EnterFieldBehavior=0 you achieve that when a text box is activated the entire contents are automatically selected. This makes it convenient for new input and is especially practical with one-line text boxes.

EnterKeyBehavior governs the behavior of the field when Return is pressed. If the property is set to True , then Return allows the input of a new line. If, however, it is set to False , then Return selects the button with the setting Default=True . In that case a new line is input by pressing the combination Ctrl+Return . The meaning of TabKeyBehavior is analogous: The property tells whether in the control Tab the tab key can be used for input or whether that key is reserved for changing controls by moving through the tab order.

Tip

If you use a text box for short input texts, you will observe the appearance of an irritatingly large empty space at the left edge of the text box. This space is governed by the default setting SelectionMargin=True. This setting makes it easy to select entire lines, but it makes sense only for multiline text boxes. If you set SelectionMargin to False, the space at the left margin obligingly disappears.

Selected Text, Clipboard

Accessing selected text is accomplished via SelText . The properties SelStart and SelLength give the first character of the selected text and the length of the selection, respectively. The following lines demonstrate how these properties can be used.

 With textbx   .SelLength = 0                 ' cancel selection   .SelStart = 0: .SelLenght = 5  ' select the first five characters   .SelText = ""                  ' delete the selected text   .SelStart = 10                 ' input cursor to new position   .SelText = "abc"               ' there insert three characters End With

With the methods Copy and Cut you can copy or cut text that has been selected into the clipboard. Paste replaces the currently selected text with the contents of the clipboard.

Events

The most important event is Change . It always appears when the content of a text box changes (that is, at each input or deleted character). This event occurs very frequently when text is input, so see to it that the event procedure can be processed very quickly.

The events KeyDown , KeyUp , and KeyPress enable a precise evaluation of keyboard events:

KeyPress : This event occurs when an alphanumeric key is pressed. The ANSI code for the input character is passed to the event procedure. In addition to the alphanumeric characters, the keys Return and Esc, as well as Ctrl combinations, are reported . KeyPress does not occur when the user presses cursor or function keys, or keys such as Insert and Delete, and thus it is not sufficient for general control of the keyboard.
KeyDown : This event occurs when any key is pressed. The internal keyboard code of the pressed key as well as the position code for the shift key are passed to the event procedure. KeyPress occurs not only when cursor or function keys are pressed, but also when only the Shift or Ctrl key is pressed.
KeyUp : This event is the complement to KeyDown . It occurs when a key is released. The same parameters are passed as those for KeyUp .

When an alphanumeric key is pressed, Visual Basic first calls the KeyDown event procedure, then KeyPress , and finally KeyUp . If the key remains held down for a while, then the KeyDown and KeyPress event procedures are called several times (auto-repeat). The three events do not occur when the user does any of the following:

uses Tab to change controls
uses Esc to select a button for which Cancel=True
uses Return to select a button for which Default=True

When the KeyPress occurs, you have the possibility to change the ASCII code, upon which the text box will contain another character. This possibility could be used, for example, by the input of numbers to change every comma to a decimal point:

 Private Sub  TextBox1_KeyPress  (ByVal KeyAscii As MSForms.ReturnInteger)   If Chr$(KeyAscii) = "," Then KeyAscii = Asc("") End Sub

Further applications: With the function UCase you could transform lowercase letters into uppercase; you could respond to Return by executing a different part of the program.

TEXTBOX	PROPERTIES
CurLine	current line number
EnterFieldBehavior	0 if the entire contents are selected upon activation
EnterKeyBehavior	True if upon Return a new line can be input
LineCount	number of lines
MultiLine	True if several text lines are used
PasswordChar	placeholder character for text
Scrollbars	tells whether scroll bars are to be shown with long texts
SelectionMargin	True if a space appears at the left margin, facilitating selection of whole lines of text
SelLength	Length of selected text
SetStart	beginning of selected text
SetText	selected text
TabKeyBehaviour	True if Tab can be used to input a tab character
Text	content of the text box

TEXTBOX	METHODS
Copy	copy selected text to the clipboard
Cut	cut selected text to the clipboard
Paste	insert text from the clipboard to replace selected text

TEXTBOX	EVENTS
Change	text box contents have changed
KeyDown	a key was pressed
KeyPress	keyboard input
KeyUp	a key was released

ListBox and ComboBox

The MS Forms library recognizes three types of listboxes, which look different from one another but which have many similarities in their programming:

Normal listbox ( ListBox ): The list is displayed in a rectangular field whose size is fixed when the form is created. If not all elements can be shown at once, a scroll bar appears automatically.
Dropdown listbox ( ComboBox with Style=fmStyleDropDownList ): As above, but the list can be unfolded. In its folded state the control takes up much less space. This form of the listbox is probably the one most frequently used, but it is not the default setting. Do not forget to set Style in the properties window.
Dropdown combination listbox ( ComboBox with Style=fmStyleDropDownCombo ): It is this variant that gives the ComboBox its name: The unfoldable listbox is combined with a text box in which text can be input that does not correspond to any of the list entries. This listbox thus makes possible the extension of a list through user input.

With both of these controls you can achieve an alternative representation with ListStyle= fmListStyleOption : Every list entry will now appear with an option button or check box. This setting has no effect on the function of the listbox.

Tip

Using listboxes is not difficult in principle. The problem is rather that first various options must be set correctly, and the default settings are usually unsatisfactory. The following list gives the four most frequent sources of problems in creating a useful setting. A detailed description of the properties follows below.

BoundColumn	0, so that Value contains the number of the list entry
RowSource	source data from a table, e.g., "Sheet2!A1:A5"
SelectMargin	True, so that input is shown without the left margin
Style	fmStyleDropDownList, so that no text input is possible

Access to List Elements

The individual entries of a list are passed to the control with the method AddItem (see the example program). Access to the list elements is achieved with the property List(n) . ListIndex gives the last selected entry (or “1 if no entry has been selected); ListCount gives the number of entries in the list. With RemoveItem individual list entries can be removed. Clear deletes the entire list.

The three listboxes that appear in Figure 7-12 are initialized in the following manner when the form is displayed:

Figure 7-12: The three types of listbox

 Private Sub  UserForm_Initialize()  Dim i As Integer   For i = 1 To 12     lstNormal.AddItem MonthName(i)     cmbDropDown.AddItem MonthName(i)     cmbCombo.AddItem MonthName(i)   Next i End Sub

The number of the currently selected list element is accessible via the property ListIndex . (The numbering begins, as with all properties of the listbox, with 0.) Value normally contains the same value as ListIndex (provided that BoundColumn has been left in its default state; see below). The Text property contains the contents of the selected element.

Tip

For some unexplained reason a selection border has been provided in the text area of the combination listbox as it is in a text box. The default setting of SelectionMargin is True (although in the text area of this control only one line can be shown). Set this property to False if you wish to get rid of the annoying margin.

Multiple Selection

In normal listboxes several entries can be selected simultaneously if the property MultiSelect is set to fmMultiSelectMulti (1) or fmMultiSelectExtended (2) . For evaluation you have to test all the Selected(i) properties in a loop in order to determine which list entries were selected. (The multiple selection is accomplished via Shiftclicking or Ctrl-clicking.)

Multicolumn ListBoxes

Several columns can be displayed simultaneously in a listbox (see Figure 7-13). For this to happen ColumnCount must be set to a value greater than 1. Access to individual list entries is achieved with List(row, column) , where the numbering begins with 0. References to List can also be made directly by means of a two-dimensional field, that is, List=field() . However, this is not possible in the reverse direction.

Figure 7-13: A multicolumn listbox whose content is linked to a worksheet

If ColumnHead is set to True , then a place for an additional header line is provided. It does not seem to be possible to achieve direct access to these entries. The headers are read automatically from an Excel table if RowSource has been used to set up a link to a range of cells. In the listbox in Figure 7-13 we have RowSouce="Sheet2!B2:D6" .

The column headers from B1:D1 are read by the listbox. With ControlSource an additional worksheet cell can be given that contains the number of the current column.

The widths of the columns are governed by ColumnWidths . In the default setting “1 all columns are the same width. (But at least 95 points; if the listbox is too narrow for this, a horizontal scroll bar is inserted.) With the setting "2cm;3cm" the first column will be 2 centimeters wide and the second, 3 centimeters. The width of the third column will be the amount of space remaining.

Since our listbox now consists of several columns, there are also several possibilities for the values that the properties Text and Value should contain when the user selects a particular row. For this purpose TextColumn determines which column's contents should appear in the Text property. Note, here, that in contrast to all other list properties, the numbering begins with 1, that is, 1 for the first column, and so on. The setting 0 indicates that it is not the contents of the column, but the column number (that is, ListIndex ) that should appear in the particular property. Furthermore, with TextColumn the setting “1 is allowed. In this case Text contains the contents of the first column whose width is not equal to zero.

In order that the entire list available in sheet 2 be gone through in the form pictured in Figure 7-13, there is a rather complicated-looking instruction in btnListBoxMulti_Click:

 ' file Userform.xls, UserForm "dlgListBoxMultiColumn" Private Sub  btnListBoxMulti_Click()  With dlgListBoxMultiColumn     .ListBox1.RowSource = Worksheets(2).name & "!" & _       Intersect(Worksheets(2).[b2].CurrentRegion, _                 Worksheets(2).[b2:d1000]).Address     .Show   End With End Sub

Worksheets(2).Name returns the name of the second worksheet (that is, "Sheet2" ).

Worksheets(2).[b2].CurrentRegion returns a Range object with all cells belonging to the range beginning with B2. (In Figure 7-13 CurrentRegion returns the range A1:D6.)

For the listbox, however, neither cell A1 (in which the index of the selected list entry is displayed) nor the header line (which the listbox determines itself) is needed. Therefore, the CurrentRegion range is restricted by Intersect to columns B “D and rows 2 “1000. Thus Intersect forms a range of cells that have the CurrentRegion and B2:D1000 in common.

The entire instruction returns the result sheet1!$B$2:$D$6 for the list in Figure 7-13.

Events

For controlling listboxes there are two events of interest: Click (for the selection of a list entry) and Change (when the text of a combination listbox is changed by keyboard input). Sometimes, DblClick is evaluated in order for this form of selection of an element to be interpreted as a request to exit the form.

With combination listboxes the event DropButtonClick is triggered before the drop-down list appears or again disappears. That can be used to build the list dynamically only when it is actually needed.

Example

In Figure 7-14 you see a form with a listbox in which the names of all worksheets of the active Excel file are presented. A double click on one of the names (or selecting a name followed by OK) activates that worksheet.

Figure 7-14: A listbox for switching into another worksheet

The programming is a piece of cake: The listbox is initialized in UserForm_Initialize . For this purpose a loop is executed over all worksheets ( Worksheets enumeration). The names of the sheets are transferred into the listbox with AddItem .

 ' userform.xls, Userform dlgListWorksheets ' form for changing the active worksheet ' fill listbox with the names of all worksheets Private Sub  UserForm_Initialize  ()   Dim wsh As Worksheet   For Each wsh In Worksheets     listboxSheets.AddItem wsh.Name   Next End Sub

In btnOK_Click a test is made as to whether a valid selection of a list element exists (that is, ListIndex>0 ). If that is the case, the selected entry (property ListIndex ) is determined from the control's list (property List ). The resulting character string is used for selecting a worksheet, which is activated with Activate . The procedure is also executed when the user selects a list entry with a double click.

 ' activate selected sheet, close form Private Sub  btnOK_Click  ()   If listboxSheets.ListIndex >= 0 Then     Worksheets(listboxSheets.List(listboxSheets.ListIndex)).Activate     Unload Me   Else     Beep   End If End Sub Private Sub listboxSheets_DblClick(ByVal Cancel As _                                    MSForms.ReturnBoolean)   btnOK_Click End Sub

LIST AND COMBOBOX	PROPERTIES
BoundColumn	column whose contents are declared in Value
ColumnHead	header for multicolumn lists
ColumnWidths	width of the column
ControlSource	cell with the number of the selected element
List(n)	access to list elements
List(row, column)	access for multicolumn lists
ListCount	number of list elements or rows
ListIndex	number of the selected element (beginning with 0)
ListStyle	list entry represented as an option button
MultiSelect	permit multiple selection
RowSource	cell range with list contents (e.g., "Sheet1!A1:B3" )
Style	fmStyleDropDownList or fmStyleDropDownCombo ( ComboBox only)
Text	text of selected element
TextColumn	column whose contents are declared in Text
Value	number or text of the list element (with BoundColumn>0 )

LIST AND COMBOBOX	METHODS
AddItem	expand list
Clear	delete list
RemoveItem	delete list entry

LIST AND COMBOBOX	EVENTS
Change	element selection or text input with ComboBox
Click	element selection
DblClick	double click on a list element
DropButtonClick	the dropdown list should be displayed ( ComboBox only)

Check Box and OptionButton

Check boxes are useful for carrying out yes/no decisions. The current state is indicated by a check displayed in a small square. See Figure 7-15.

Figure 7-15: Check box and option button

The state of an option button is indicated by a dot in a circle. In addition to this visual difference, in comparison to the check box, there is a difference in functionality as well: When one of several option buttons is activated, then all the others are automatically deactivated; that is, only one of the several options can be selected at a given moment. If there are several independent groups of option buttons in a form, then the GroupName property of the associated controls must be supplied with identical character strings.

The current state of both controls is taken from the Value property. Permitted values are True , False , and Null . (The setting Null denotes an undefined state. That would be useful, for example, in a check box for boldface type when a region of text is selected whose text is only partially in boldface.) When the property TripleState is set to True , then all three states can be set with the mouse ( otherwise , only True or False ).

Note	For some mysterious reason it is impossible to preset the Value property in the form editor. Rather, you must execute the relevant instruction in User_Initialize.

 ' file Userform.xls, "dlgOption" Private Sub  UserForm_Initialize()  OptionButton1.Value = True End Sub Private Sub CheckBox1_Click()   Label1.Font.Bold = CheckBox1.Value End Sub Private Sub CheckBox2_Click()   Label1.Font.Italic = CheckBox2.Value End Sub Private Sub OptionButton1_Click()     Label1.Font.Name = "Arial" End Sub Private Sub OptionButton2_Click()     Label1.Font.Name = "Times New Roman" End Sub Private Sub OptionButton3_Click()     Label1.Font.Name = "Courier New" End Sub

CHECKBOX, OPTIONBUTTON	PROPERTIES
Caption	descriptive text
TripleState	permit " undefined " ( Null ) as input
Value	current state

CHECKBOX, OPTIONBUTTON	EVENT
Click	the state has changed

Command Buttons and Toggle Buttons

Using buttons is particularly easy (Figure 7-16). The caption text is set with Caption . An optional graphic image (bitmap) can be displayed in the button, which can be created with the help of the Picture property. One can do without a Caption text and create a purely graphical button. In this case there should at least be the yellow infotext provided by means of ControlTipText .

Figure 7-16: MS Forms buttons

Toggle buttons are distinguished from normal buttons in that they remain in their pressed-in state and pop back only upon being pressed a second time. The current state can be determined with Value ( True/False/Null ) as with option buttons.

Note

If you use buttons directly in a worksheet (as opposed to a form), then by all means set the property TakeFocusOnClick to False. Otherwise, the keyboard focus remains in the button after it is clicked on, thereby blocking a number of other functions until a further mouse click brings the focus again into the worksheet.

COMMANDBUTTON, TOGGLEBUTTON	PROPERTIES
AutoSize	fit button size to contents (text/graphic)
Cancel	select with Esc
Caption	caption text
ControlTipText	yellow infotext
Default	select with Return
Picture	graphic
PicturePosition	position of the graphic
TakeFocusOnClick	the setting False prevents the button from obtaining the input focus when clicked (important in worksheets)
TripleState	permit " undefined " ( Null ) as input
Value	current state

COMMANDBUTTON, TOGGLEBUTTON	EVENT
Click	the button was clicked

Frames

The frame has the job of visually collecting associated controls (see Figure 7-17). All of the controls within a frame are considered to be a single unit. If the frame is moved, the controls will move with it.

Figure 7-17: Two frames with different zoom factors

One feature of frames is that a zoom factor can be given for all of its controls. The controls are correspondingly enlarged or shrunk. In rare cases this feature can be used to represent extensive forms.

The contents of a frame can be equipped with scroll bars. In order for scroll bars to be displayed, Scrollbars must be set to fmScrollbarsBoth . If you want the scroll bars to disappear when the entire contents of the frame are visible, then you must additionally set KeepScrollbarsVisible=fmScrollbarsNone .

In order for the frame to know how large its scrollable contents are, the properties ScrollWidth and ScrollHeight must also be given values. The appropriate settings usually must be determined in program code (such as in Form_Load ). The commands in Form_Initialize have the effect of making the scrollable region correspond to the currently visible inside dimensions of the frame. (Scroll bars will then be required when either the zoom factor is increased or the frame decreased in size.) InsideWidth and InsideHeight specify the usable inside dimensions of the frame.

The following lines show the necessary initialization code for displaying scroll bars correctly. The temporary setting of the zoom factor to 100 percent is necessary to ensure that the entire inner region can be displayed.

 Private Sub  UserForm_Initialize()  With Frame2     .Zoom = 110     .ScrollWidth = .InsideWidth     .ScrollHeight = .InsideHeight     .Zoom = 100   End With End Sub

With the Controls enumeration you can access all the controls within the frame. ActiveControl refers to the active control within the frame. The methods AddControl and RemoveControl enable the insertion and deletion of controls.

FRAME	PROPERTIES
ActiveControl	active control within a group
Controls	access to the individual controls
InsideWidth/-Height	size of the usable inside area
KeepScrollbarsVisible	always display scroll bars
Scrollbars	specifies whether scroll bars should be used
ScrollLeft/-Top	upper left corner of visible region
ScrollWidth/-Height	size of scrollable region
Zoom	zoom factor for the contents of the region

FRAME	METHODS
AddControl	insert control
RemoveControl	delete control

MultiPage, TabStrip

The controls MultiPage and TabStrip offer two ways of creating multipage forms. The resulting forms are indistinguishable, but the effort required to set up and program is more difficult in the case of TabStrip . (There is really no good reason for TabStrip to be in the MS Forms library at all.) Therefore, in this section we restrict our attention to a description of the MultiPage control.

Multipage Forms

Multipage forms are often used to allow for a large number of options to be set in a single form (see the Excel Options dialog). There is, however, the possibility that the user will get lost among too many choices. Take care that your sheets are easily distinguishable one from the other with respect to their function and that the captions make sense. Less can often be more! Forms that have so many sheets that their captions take up several lines constitute an unreasonable demand on the user.

The user should be able to exit a multipage form at any time (independently of the currently visible page). Therefore, place the OK, Cancel, and other such buttons outside the individual sheets!

The MultiPage Control

The creation of a multipage form is extremely simple, requiring not a single line of code. You simply insert a MultiPage control, activate one of the pages with a mouse click, and there insert the desired controls. The moment you click on the second page, the first disappears into the background and you can proceed at your leisure to insert controls into the second page. With the pop-up menu summoned with a right-click of the mouse you can change the captions and sequence of pages, and insert new pages or delete existing ones.

You will have an easier time with the MultiPage control if you understand from the outset that you are working with not one but several controls. When you insert a MultiPage control into a form, then at once two Page objects are inserted into the MultiPage control. The MultiPage control is thus primarily a container for Page objects. For their part, the Page objects then take responsibility for the controls for the individual pages of the form. (To be precise, you are dealing with three rather than two objects: Pages is an independent enumeration object, even if this is usually unnoticeable.)

Note

In setting the properties, take care that you have activated the correct control. It is almost impossible to click on the MultiPage control in the form editor. The editor always activates an embedded Page control. Select the MultiPage control in the listbox of the properties window. Furthermore, it is impossible to set the properties of several sheets simultaneously, or to copy sheets and insert them via the clipboard.

One of the most interesting properties of the MultiPage object is TabOrientation . With this you can specify whether the page caption (the tab) is displayed above, below, on the left, or on the right. If you have very many tabs (which will make for a form that is confusing and difficult to use, such as the options forms in Word), you can set MultiRow=True to display the captions over several lines.

Access to the Page object is achieved, perhaps not surprisingly, by means of the Pages property. This property is also the default property, so that MultiPage1(1) can access the first Page object. The number of the currently active Page object can be retrieved and edited with the Value property.

If you wish to insert new pages or delete existing ones in program code, you can use the Pages methods Add and Remove . This leads to an AddControl , respectively RemoveControl , event for the MultiPage object.

Let us now proceed to the properties of the individual pages (that is, those of the Page object): These exhibit practically the same events, properties, and methods as the frame. Thus you can equip individual pages with scroll bars, shrink or enlarge with Zoom , and so on. What is new are the properties TransitionEffect and TransitionPeriod , with which imaginative programmers can set fanciful effects for changing pages.

Example

Our example program is once again merely for the purposes of demonstration. To set the type style of a cell in a worksheet a two-page form is summoned. In the first sheet (Figure 7-18) the type attributes can be set (boldface, italic), while in the second sheet (Figure 7-19) five font families are offered from which to choose. When the form appears, the Font object of the cell is analyzed and the corresponding options are shown. (Of course, there is a much more complete, and above all predefined, form for setting the type style; the example here is just to demonstrate the principle.)

Figure 7-18: A simple multipage form

Figure 7-19: The second page of the form

The program code for this form has little to do with the fact that it is dealing with a multipage form. The programming effort is concentrated in editing the Font object correctly. Before the form is called, the Font object to be edited must be written into the public module variable fnt . In the procedure UserForm_Activate , which is executed automatically when the form is displayed, the option buttons and control boxes corresponding to the properties of fnt are preset.

 ' Userform.xls, UserForm "dlgMultiPage" Public fnt As Font Private Sub  OptionButton1_Click()  ' as soon as something changes,   CommandButton2.Enabled = True   ' "Apply" button is activated End Sub Private Sub OptionButton2_Click()         ' as above Private Sub OptionButton3_Click() Private Sub OptionButton4_Click() Private Sub OptionButton5_Click() Private Sub CheckBox1_Click() Private Sub CheckBox2_Click()

The three event procedures for the buttons OK, Apply, and Cancel offer few surprises . According to which button is pressed a change is made or the form is exited.

 Private Sub  CommandButton1_Click()  'OK   WriteAttributes   Unload Me End Sub Private Sub  CommandButton2_Click()  ' Apply   WriteAttributes   CommandButton2.Enabled = False End Sub Private Sub  CommandButton3_Click()  ' Cancel   Unload Me End Sub

The procedure ReadAttributes first tests whether either or both of the properties Bold and Italic of the Font object are set. Depending on the result, one or both of the corresponding control boxes are initialized. Somewhat more original is the loop over all controls in the second page of the MultiPage . Here the fact is made use of that the caption text of the option buttons corresponds exactly with the name of the font. If the name of the fnt font agrees with that of the control, then the Value property of this option button is set to True .

Furthermore, the button Apply is deactivated (that is, Enabled=False ). This button becomes active only when a change is made to the form, that is, when there are actually some data to process. For this reason each control on both pages of the form has an event procedure with CommandButton2.Enabled = True .

 ' read data from fnt variable Sub  ReadAttributes()  Dim c As Control   If fnt.Bold Then CheckBox1 = True Else CheckBox1 = False   If fnt.Italic Then CheckBox2 = True Else CheckBox2 = False   For Each c In MultiPage1("Page2").Controls     If fnt.Name = c.Caption Then c.Value = True   Next   CommandButton2.Enabled = False End Sub

Note

Originally, it was planned that ReadAttributes would be called in UserForm_Activate automatically each time the form was displayed. Because of a bug in Excel 97 the Activate event procedure is not reliably executed each time the form is displayed. For this reason ReadAttributes must be executed in code for calling the form before Show is executed.

WriteAttributes has precisely the opposite task. There the current state of the control box is evaluated and Font correspondingly changed.

 ' write data to the fnt variable Sub  WriteAttributes()  Dim c As Control   If CheckBox1 Then fnt.Bold = True Else fnt.Bold = False   If CheckBox2 Then fnt.Italic = True Else fnt.Italic = False   For Each c In MultiPage1("Page2").Controls     If c.Value Then fnt.name = c.Caption   Next End Sub

The form is called by the following event procedure:

 ' file Userform.xls, "mainmenu" module Private Sub  btnMultipage_Click()  Worksheets(3).Activate   With dlgMultiPage     Set .fnt = Worksheets(3).[a1].Font     .ReadAttributes     .Show   End With End Sub

MULTIPAGE	PROPERTIES
Pages	refers to the Pages enumeration object
Pages(n)	refers to an individual Page object
MultiRow	several rows of tabs for page selection
TabOrientation	tabs left/right/above/below

PAGE	PROPERTIES
Caption	page caption (tab text)
Scrollbars	specifies whether scroll bars should be used
KeepScrollbarsVisible	always display scroll bars
ScrollWidth/-Height	size of scrollable region
ScrollLeft/-Top	upper left corner of visible region
InsideWidth/-Height	size of usable inside region
Zoom	zoom factor for page contents
TransitionEffect, -Period	effect in changing to another page

Scrollbar and SpinButton

Scroll bars are useful for selecting an integer from a given range of values. In comparison to Excel 5/7 scroll bars, the allowed range of numbers has been extended to the Long range (that is, ±2*10 ⁹ ). The scroll bar can be used in both the horizontal and vertical directions according to the way you have presented your controls.

A spin button is an emaciated variant of the scroll bar. It consists of two small arrows pointing up and down, or left and right. A spin button does not have a scroll bar.

The most important properties are Min (the smallest allowed value), Max (largest allowed value), SmallChange (size of change when the arrow is clicked), and Value (current value). For the scroll bar there is the additional property LargeChange for the sideways movement of the scrollable region. Delay specifies the time in milliseconds by which the event is delayed and thus controls the maximum speed of change.

In the form displayed in Figure 7-20 the three scroll bars allow the user to set the background color of an image. The code consists of seven identical procedures:

Figure 7-20: Three scroll bars

 ' Userform.xls, UserForm "dlgScrollbar" Private Sub  UserForm_Activate()  Image1.BackColor = RGB(scrR, scrG, scrB) End Sub Private Sub scrR_Change() ' as above Private Sub scrG_Change() Private Sub scrB_Change() Private Sub scrR_Scroll() Private Sub scrG_Scroll() Private Sub scrB_Scroll()

SCROLLBAR, SPINBUTTON	PROPERTIES
Delay	delay between events in milliseconds
LargeChange	sideways change (for Scrollbar only)
Min/Max	allowed range of values
Orientation	arrow up/down or left/right
SmallChange	change amount when button is clicked
Value	current value

SCROLLBAR, SPINBUTTON	PROPERTIES
Change	Value has changed
Scroll	scroll region has been moved ( Scrollbar only)
SpinDown	lower (right-hand) arrow was selected ( SpinButton only)
SpinUp	upper (left-hand) arrow was selected ( SpinButton only)

Image

The Picture property is used to display a bitmap file on an object (image field). If a graphic is not placed in the properties window, but is to be loaded by program code, you can use LoadPicture :

 Image1.Picture = LoadPicture("filename")

A host of further properties govern the resulting display: PictureAlignment specifies how the bitmap is to be positioned if it is larger than the image field image. PictureSizeMode tells whether the bitmap should be resized to fit the image field. PictureTiling determines whether the bitmap should be repeated horizontally or vertically to make use of the entire available space (as is used for the background bitmap of the Windows screen).

Images with 256 colors are generally displayed in MS Forms forms at lower quality (that is, fewer colors). This seems to depend on the color palette used by Excel.

There are also some properties available that have been discussed previously: AutoSize=True has the effect of making the image field fit the bitmap. Border controls the border around the image field, while SpecialEffect produces 3-D effects for the border. With this, one can give the image field the appearance of a graphical button. With the program that appears in Figure 7-21 you can try out various effects.

Figure 7-21: Test program for the SpecialEffect property

IMAGE	PROPERTIES
AutoSize	image field is made to fit the bitmap
Border	border control
Picture	bitmap
PictureAlignment	positions the bitmap
PictureSizeMode	scaling for the bitmap
PictureTiling	True if the bitmap is to be repeated horizontally and vertically
SpecialEffect	3-D effect for the border

IMAGE	EVENT
Click	the control was clicked

Formulas (RefEdit)

The RefEdit control (Figure 7-22) facilitates input of cell references, that is, addresses of ranges of cells. (In Excel 5/7 this task was performed by the text box when Edit Validation was set to Formula. ) The RefEdit control does not belong to the MS Forms library, but is an independent ActiveX control. RefEdit has two features that distinguish it from all other controls. First, it is possible to select a range of cells in a worksheet and even to change worksheets while the input cursor is in the control. (With all other controls Excel responds to this bizarre request with a beep.) Second, the entire form shrinks to the size of the RefEdit control (Figure 7-23), so that the form does not take up space needed for selecting the range.

Figure 7-22: Input of a range of cells in a RefEdit control

Figure 7-23: The shrunken control

The RefEdit control is provided with a large assortment of properties, methods, and events, most of which you will never need to use. (Many properties are the same as those of the text box.) The Value property contains, according to the selection, a character string with the cell reference, for example, "sheet1!$A$1" or "[book2]sheet1!$B$13" , the latter case for when the range of cells is located in another Excel file.

Caution

The RefEdit form does not have about it the air of a finished product. For one, the CPU load climbs frequently (though not always) to 100 percent when a form with a RefEdit control is displayed. Furthermore, the control offers a large number of events, but the only important one, namely Change, is not triggered at every change. If you wish to carry out a validation control or a calculation based on what has been input, you can do that only in the event procedure of a button.

Sad to say, in a RefEdit control not only cell references, but an arbitrary character string, can be input. But there is no function that allows you to determine whether the Excel corresponds to a correct cell reference. You should therefore back up the following code with an error-handling routine.

After completion of the example form in Figure 7-24 the formula of the selected range of cells and the sum of numbers in those cells is displayed in an alert form. The requisite code for displaying the alert form is as follows:

Figure 7-24: Result of the selection

 ' Userform.xls, UserForm "dlgRefEdit" Private Sub  CommandButton1_Click()  On Error Resume Next   Hide   MsgBox "You have selected the range " & RefEdit1.Value & _     ". The sum of these cells is: " & _     WorksheetFunction.Sum(Range(RefEdit1.Value))   If Err Then     MsgBox RefEdit1.Value & " is not a valid range of cells"   End If End Sub

REFEDIT	PROPERTIES
Value	contains a character string with the cell reference

REFEDIT	EVENTS
Change	Value has changed ( unfortunately , the event is not always triggered)

The UserForm Object

Now that we have described all the important controls, we should say a few words about the object for representing the form. All forms are based on the UserForm object. This object has a large number of properties that you already know from other controls: how in a frame all controls in a form can be addressed with the enumeration Controls ; how with Controls.Add and Controls.Remove additional controls can be added and controls deleted. ActiveControl refers to the control that currently exhibits the input focus. With Zoom the scaling factor for the inside region of a form can be set between 10 and 400 percent.

The background of the form can be underlaid with a bitmap graphic. The bitmap is loaded by means of the Picture property. All the options for representing graphics that were available for an image field are available here.

Displaying the Form

To display a form you use the method Show . Normally, the form is displayed in modal form, that is, the form must be terminated before control is passed back to Excel. Beginning with Excel 2000 there is also the option of displaying the form modeless by specifying the optional parameter vbModeless .

 dlgName.Show                'display form in normal mode dlgName.Show vbModeless     'display form modeless

Positioning the Form

It is interesting to note that there are certain UserForm properties and methods for which you can search in the object browser in vain. For example, with StartupPosition you can set the place at which the form appears on the screen. There are four possibilities:

	manual positioning via the properties Left and Top
1	centered in the Excel window: the default
2	centered on the screen
3	Windows default position (upper left corner of the screen)

Completely independent of StartupPosition you can have a form appear at an arbitrary place if you set the properties Left and Top in the UserForm_Activate event procedure. The ideal spot, namely in the neighborhood of the actual mouse position, cannot be set without DLL functions.

Closing the Form

There are two ways in which a form can be closed: the method Hide and the command Unload , to which a reference to the form ”the property Me ”is passed as parameter. This apparent duplication often causes confusion. However, in this case the two commands carry out two completely different operations.

Unload Me closes the form and clears it from memory. All local variables in the module belonging to the form are lost. If the form is later displayed with Show , it is reloaded into memory and appears as if for the first time in an uninitialized state.

Hide makes the current form invisible. Visually, the effect is the same as that of Unload , but internally the form remains in memory. Show shows the form as it was last displayed. This means that earlier text inputs or option settings remain available when the form is shown again.

Thus the decision whether to use Unload or Hide depends on the use to which it will be put. Usually, Unload Me is preferable, because the form is cleared from memory. But if you wish to access the form's properties or variables outside of the module belonging to the form, or if the form's settings are to be preserved from one call to the next, then Hide is the better choice.

Events

The UserForm object recognizes a number of familiar events, such as Click , DblClick , MouseDown , MouseMove , and MouseUp for precise control of the mouse, as well as KeyDown , KeyUp , and KeyPress for keyboard events.

For managing the form the events Activate , Deactivate , Initialize , and Terminate are helpful: Initialize occurs when the form is loaded into memory (before it is displayed for the first time). Terminate occurs when the form is later removed from memory (that is, when the controls and variables contained within are deleted). Terminate is triggered both by Unload Me and through clicking the Close box (that is, the — in the upper right corner of the form).

Activate occurs (if necessary after Initialize ) every time the form is displayed. The difference between it and Terminate is that Activate , for example, also occurs at subsequent displays of a form that was closed with Hide . (In this case Initialize does not occur again, since the form remains in memory and therefore does not need to be reinitialized.)

Deactivate occurs only when a second form is displayed while the first form remains visible. If the second form is closed (whereby the first becomes again active), another Activate event is triggered.

Caution

In Excel 97 there were occasional problems with the Activate event. This event occurred only at the first display of a form, but not again (or only when the development environment was open ). This problem has apparently been solved in Excel 2000. But you should keep your eye on the situation if you wish to program applications that are to run under Excel 97 as well as Excel 2000.

Avoiding Form Termination via the Close Box

Sometimes, the user is to be prevented from being able to close the form with the x-button in the upper right-hand corner of the form. In this case the event QueryClose (apparently forgotten about in the Office 2000 documentation) occurs. The parameter CloseMode specifies for what reasons the window can be closed:

vbFormControlMenu (0)	close button
vbFormCode (1)	Unload instruction in code
vbAppWindow (2)	Windows is terminated (shutdown)
vbAppTaskManager (3)	program end by the task manager

With Cancel the attempt to exit the form can be blocked.

 ' Do not exit the form with the close button Private Sub  UserForm_QueryClose(Cancel%, CloseMode%)  If CloseMode = vbFormControlMenu Then Cancel = True End Sub

USERFORM	PROPERTIES
ActiveControl	active control within the group
Controls	access to controls
InsideWidth/-Height	size of available internal region
KeepScrollbarsVisible	always show scroll bars
Picture	bitmap graphic
PictureAlignment	position of the picture
PictureSizeMode	picture scaling
PictureTiling	True if the image should be repeated horizontally and vertically
Scrollbars	determines whether scroll bars are to be used
ScrollLeft/-Top	upper left corner of the visible region
ScrollWidth/-Height	size of the scrollable region
Zoom	zoom factor

USERFORM	EVENTS
Activate	the form is displayed or reactivated (after Deactivate )
Click	the form (not a control) was clicked
Deactivate	the form loses the focus because a subform is displayed
Initialize	the form is loaded into memory (initialization)
QueryClose	the form should be closed (close box)
Terminate	the form is removed from memory (cleanup tasks )