UserForm Controls

 < Day Day Up > 

UserForm Controls

The toolbox contains a useful assortment of controls that can be placed on a form. Some, like the Label control, are static and merely modify how the form appears. Other controls, such as the TextBox and the CommandButton, interact with the user to edit information and trigger tasks.

Programming Controls

In addition to displaying the form, the Visual Basic Editor also displays a floating toolbar called the toolbox. The toolbox contains the list of controls that can be placed on a form.

To add a control to the form, select the control from the toolbox, drag it onto the form, and drop it where you want the control. You can adjust the size and placement of the control graphically by selecting the control and dragging the control around or by dragging the box that surrounds the control. (See Figure 19-2.)

click to expand
Figure 19-2: You add controls to a form control by selecting a control and dragging and dropping it on the form.

All of the code associated with the form and its controls are stored in the form module. This is an independent element much like a regular module or a class module. Double-clicking a control will switch you from the graphical view of the form to the code view of the form and automatically insert the subroutine definition for the default event for that control.

Common Properties, Methods, and Events

Because UserForm controls are objects like the other objects you have already seen, they have a rich collection of properties, methods, and events. Table 19-2 contains a list of the key properties, methods, and events that are common to many of the form controls.

Table 19-2: Key Properties, Methods, and Events of the UserForm Controls




Property: controls the color of the background of the control.


Property: contains text that's displayed on the control, but can't be changed by the user.


Event: called when the Value property changes.


Event: called when the user clicks the control using the mouse.


Property: contains text that is displayed when the user hovers the mouse pointer over the control for a moment.


Event: called when the user double-clicks the control with the mouse.


Property: when True, means that the control can receive the focus and will respond to user input.


Event: called just before the control receives the focus from another control on the same form.


Event: called just before the control loses the focus to another control on the same form.


Property: contains an object reference to a Font object, which defines the characteristics of the text displayed on the control.


Property: contains the color used to draw the foreground of the control.


Property: contains the height of the control in points.


Property: contains the distance between the left edge of the control and the left edge of the form on which the control resides.


Property: when True, the user is not permitted to change the value of the control.


Property: contains the name of the control.


Property: specifies how the control is drawn.


Property: specifies the control's relative position in the tab sequence.


Property: when True, means that the control will accept the focus when the user tabs to it.


Property: contains the distance between the top edge of the control and the top edge of the form on which the control resides.


Property: describes the state or content of a control.


Property: when True, means that the control is displayed on the form.


Property: contains the width of the control in points.

ZOrder (zPosition)

Method: moves the control to the front (fmTop) or the bottom (fmBottom) of the z-order.

The Height, Width, Top, and Left properties dictate the physical size and placement of the control on the form. The Enabled property determines if the control can receive the focus, whereas the Visible control determines whether the control will be drawn. When the Locked property is True, the user can manipulate the contents of the control but will be unable to change its value. For instance, setting the Locked property to True means that the user could scroll through a large, multi-line text box to see all of the data. If the Enabled property were set to False, the text box control would still be visible, but the user would be unable to scroll to see any text that isn't currently visible on screen.

When two controls overlap, the ZOrder method controls which one is completely visible and which one is partially or totally hidden. Moving a control to the top of the z-order means that it will be displayed last-thus ensuring that the control will be visible even if it overlaps any other controls. Likewise, using ZOrder to move a control to the bottom means that it will be rendered first, and any other control that is rendered later might hide this control from view.

TabIndex identifies the order in which the focus will shift from one control to the next when the user presses the Tab key, and TabStop determines whether the control will accept the focus or the focus should shift to the control with the next higher TabIndex value. The Enter and Exit events are fired as the focus shifts from one control to the next on the form. The Exit routine contains a single argument called Cancel, which you can set to True to prevent the user from switching to a different control.


Checking Information
Because the code in the Exit event is only executed when the user switches to another control, it's an excellent time to examine the contents of the control to determine if the value is correct. For example, you can verify that the contents of the control are numeric, and if the user entered an invalid value, you can notify the user using a message box and then set the Cancel argument to True, thus preventing the user from switching to a new control on the same form.

The exact meaning of the Value property depends on the control, but in general it contains the value associated with the control. When this value changes, the Change event is fired. Remember that the Value property is different from the Caption property in that the Caption property usually represents a block of text that can be changed only by the program, whereas the Value property usually contains information that can be changed by the user interacting with the form.

The Click and DblClick events occur when the user clicks or double-clicks the mouse. In some cases, the Change event might also fire if clicking or double-clicking the control affects the control's Value.

The Label Control

The Label control allows your program to display text on the form that the user can't modify. Typically, the Label control is used to describe the contents of other controls, such as the TextBox control.

Although the user cannot change the value of a Label control, your program can, by assigning a value to the Caption property. Unlike many other controls, the Label control doesn't have a Value property because there's nothing the user can change. However, the Label control will respond to mouse clicks, triggering either the Click or the DblClick event.

The CommandButton Control

The CommandButton control displays a button that, when clicked, triggers the Click event associated with the control. Another way to trigger a command button is to use the Tab key to shift the focus to the command button and then press the Enter key or the Spacebar.

The following routine changes the text displayed on the button each time the button is clicked. So if the Caption property reads 'Off', the routine will reset the value to 'On'; otherwise, the Caption property will be set to 'Off'.

Private Sub CommandButton1_Click()

If CommandButton1.Caption = "Off" Then
CommandButton1.Caption = "On"

CommandButton1.Caption = "Off"

End If

End Sub


A Push of a Button
Use the command button when you want to call a subroutine to process the information on the form.

The TextBox Control

The TextBox control is the primary control that's used to accept text from the user. Text boxes come in two main flavors: single-line and multi-line. This is controlled by the Multiline property. When Multiline is True, the control will display multiple lines of text in the box; otherwise, the text box will display only a single line of text.


Taking a Break
You can force the text to start on a new line by embedding the constant vbCrLf (carriage- return, line-feed) before the first character of the next line. This white space can be helpful if you wish to break a long block of text into paragraphs.

If you have more text than can be displayed in either a single-line or multi-line text box, you have the option to include scroll bars so that the user can scroll to see the hidden text. You can set the ScrollBars property to fmScrollBarsHorizontal to display a scroll bar on a single-line text box, or you can set the ScrollBars property to fmScrollBarsHorizontal, fmScrollBarsVertical, or fmScrollBarsBoth to display a horizontal or vertical scroll bar, or both in a multi-line text box. Set this property to fmScrollBarsNone for either type of text box if you don't want to display any scroll bars.

Another useful property for multi-line text boxes is the WordWrap property. When WordWrap is set to True, any lines that are too long to be completely displayed in the horizontal space available will be wrapped to the next line on a word boundary so as not to split a word between lines. However, in this case, you will not be able to display a horizontal scroll bar.

The LineCount property returns the number of lines in a multi-line text box.

The characters displayed in a text box control are available in both the Text and Value properties, and you can change what is displayed in the text box by changing either property. However, it's customary to use the Text property instead of the Value property. The Change event will be fired if a value is assigned to either property.


The Change event is fired for each character entered by a user into the text box. This event can be used to validate the information entered into the text box one character at a time. However, if you wish to validate the entire text box after the user has finished entering data, use the Exit event (which is fired automatically when the focus leaves the text box) instead of the Change event.

You can hide the characters entered into a text box by specifying a value for the PasswordChar property. This property is typically used to hide password information. Typically, an asterisk (*) is used for this property. Set this property to the empty string to display the actual characters entered.

The MaxLength property specifies the maximum number of characters that can be entered into the TextBox, and the TextLength property returns the total length of the text. Notice that TextLength will include carriage return and linefeed characters in the count when they are present in a multi-line text box. If the AutoTab property is True, the focus will be shifted to the next control in the tab sequence when the maximum number of characters have been entered into the control.

The CheckBox Control

The CheckBox control provides the user a way to chose between two values, such as Yes and No, True and False, and Enabled or Disabled. The text in the Caption property is displayed next to the check box.

The Click and Change events in this control accomplish the same thing because to change the check box's value, the user simply clicks anywhere over the control, including the caption. You can display the value of the CheckBox control by using code like this in the check box's Click event:

Private Sub CheckBox1_Change()

MsgBox "Value of checkbox is " & CheckBox1.Value

End Sub

The ToggleButton Control

The ToggleButton control is similar to a CheckBox control in that it gives the user a way to choose between two values. The only real difference is that the ToggleButton control appears to be 'up' when the Value property is set to False and 'down' when Value is set to True.

The SpinButton Control

The SpinButton control makes it easy for the user to choose from a range of numeric values. The Value property holds the current value of the control. Pressing the Up Arrow button increases the value, and pressing the Down Arrow button decreases the value.

The Min and Max properties specify the smallest and the largest values for the control. The SmallChange property specifies the number that will be added or subtracted each time the Up Arrow button or the Down Arrow button is pressed. By default, this value is 1.

Each time the Value property is changed in this control, the Change event is fired. If the Up Arrow button is pressed, the SpinUp event will also be fired. If the Down Arrow button is pressed, the SpinDown event is triggered.

The Delay property specifies the amount of time between Change events when the user clicks and holds the mouse down on the Up Arrow or the Down Arrow button. The default is 50 milliseconds. The delay between the first call and the second call is five times the Delay value to make it easier for a user to press the button once. After the second call, the Change event will be called after the amount of time specified in Delay has elapsed.

Typically, you will want to include a TextBox or a Label control beside the SpinButton control to display the current value. This value is displayed by using code like the following:

Private Sub SpinButton1_Change()

TextBox1.Text = SpinButton1.Value

End Sub

The Frame Control

The Frame control provides a way to group together a series of controls. Technically, this control is known as a container because it's the only control in Visual Basic form controls that can hold other controls. The controls contained in a frame are also known as child controls.


From Frame to Form
You can't drag an existing control onto a frame, nor can you drag a control from the frame to the UserForm. Instead, if you wish to move a control from a frame to the form or from the form to the frame, you need to use a cut-and-paste operation. Select the control, and select Edit, Cut from the main menu. Then select the desired container (either the frame or the form), and select Edit, Paste from the main menu. Once the control is on the form or frame, you can drag it around to place it exactly where you want it.

Frames are also useful if you want to draw visual attention to a group of controls. The Caption property is displayed on the border of the Frame control.


Moving Frames
Resizing and/or moving a frame around on a form also moves all the controls that it contains. The same relative position between the child controls and the upper-left corner of the frame will remain constant.

The OptionButton Control

The OptionButton control provides a way to choose exactly one item from a group of items. Each option button works like a check box in that the option button control has two states, either selected or not selected. However, only a single option button on a form may be selected. When the user clicks on a different option button, the currently selected option button is unselected before the new option button is selected. This means that if your form has 20 option button controls on it, only one will be selected at a time.

If you need to display more than one group of option buttons on a form, you need to place each group of option buttons in its own Frame control. This means that the group of option buttons can affect only the values of the other option buttons in the same frame. Any option buttons outside a frame will not be affected, nor will any option buttons stored in any other frame.


Use extreme caution when deleting a Frame control. Deleting the Frame control will also delete any controls that it contains. If you want to preserve these controls, you should cut or copy them to the clipboard, and then delete the Frame control itself. When the Frame control is gone, you can paste those controls directly onto the form or wherever else you want to place them.

The Image Control

The Image control is a relatively simple control that allows you to display a picture on a form. It supports only these file formats: BMP, CUR, GIF, ICO, JPG, and WMF.

The Picture property contains the actual binary picture image that will be displayed in the control. In the Visual Basic Editor, you can enter the name of a file and it will automatically load the image for you. However, if you want to load an image while the program is running, you will need to use the LoadPicture function like this, where <pathname> contains the file name and path.

Image1.Picture = LoadPicture(<pathname>)

By default, when a picture is loaded into the control, either initially in the Visual Basic Editor or in your application, the picture will be displayed at its normal size. This means that you will see the complete picture only if the image control is the same size as the image. If the control is smaller than the image, you will see only the upper-left corner of the image, but if the control is larger than the image, you'll see the entire image, plus the background of the control.

The PictureAlignment property controls how the image is placed in the control. By default, the picture is aligned such that the upper-left corner of the picture is placed in the upper-left corner of the control (fmPictureAlignmentTopLeft). However, you can set this property to these values as well: fmPictureAlignmentTopRight, fmPictureAlignmentCenter, fmPictureAlignmentBottomLeft, and fmPictureAlignmentBottomRight.

You can use the PictureSizeMode property to automatically resize the image to see the entire picture. A value of fmPictureSizeModeStretch resizes the image to fill the entire space of the control. However, you might need to adjust the Height and Width properties to prevent the image from being distorted. Setting this property to fmPictureSizeModeZoom will enlarge or shrink the picture to fit the box, but it won't change its height-to-width ratio. A value of fmPictureSizeModeClip displays the image at normal size and chops off any part of the image that will not fit into the control.

When the PictureTiling property is True, the image is repeated as many times as necessary to fill up the area available in the image control. The first copy of the image is placed according to the PictureAlignment property.

The ScrollBar Control

The ScrollBar control allows you to add a classic Windows scroll bar to your application. Depending on the value of the Orientation property, your scroll bar will appear to be horizontal (fmOrientaionHorizontal) or vertical (fmOrientationVertical).


By default, the Orientation property is set to fmOrientationAuto, meaning that the Visual Basic Editor will automatically select the control's orientation based on whether the control is wider than tall (horizontal) or taller than wide (vertical).

Moving the scroll box along the scroll bar changes the Value property of the control. The Min and Max properties specify the minimum and maximum numbers that will be returned through the Value property. The Min value will be returned when the scroll box is at the top of a vertical scroll bar or at the left side of a horizontal scroll bar. The Max property will be returned when the scroll box is at the bottom of a vertical scroll bar or at the right of a horizontal scroll bar.

The LargeChange property specifies how much the scroll box will be moved when the user clicks in between the scroll box arrow buttons, whereas the SmallChange property specifies how much the scroll box will be moved when the user clicks the arrow buttons on each end of the scroll bar.

The Scroll event is fired whenever the scroll box is moved on the scroll bar, but the Change event is fired whenever the value changes. For all practical purposes, these two events are the same. The only difference between the two events is that the Scroll event will be triggered multiple times while the user moves the scroll box around on the scroll bar. The Change event will be fired only after the user has released the scroll box.

The ListBox Control

The ListBox control displays a list of information to the user, from which the user can select one or more entries. The list of items that can be selected are arranged into a series of rows and columns. The number of columns is controlled by the ColumnCount property. This value contains the number of columns that are available. By default, this property is set to 1, meaning that a single column will be displayed. If you set this property to -1, all available columns will be displayed (up to a total of 10).

When the ColumnHeads property is True, the first row in the list is used as the header for the column. Notice that this row can't be selected. The ColumnWidths property specifies the size of each column. The sizes for all column widths are contained in a single string formatted as a set of numbers separated by semicolons (;). Each number specifies the width of the column in points. You may also specify widths in centimeters (cm) and inches (in). Decimal points are allowed. Specifying a value of zero as the width of a particular column means that that particular column won't be displayed.

The ListCount property contains the number of rows in the list. The List property accesses the list of items and takes two parameters, the row number and the column number. Both parameters start with zero and have a maximum value of one less than the ColumnCount or ListCount properties. If you reference the List property without any arguments, you can copy a two-dimensional array of values to the property in a single statement.

The following routine shows how you might use the List and ColumnCount properties to initialize a list box control:

Private Sub UserForm_Initialize()

Dim MyList(10, 2) As String
Dim i As Long

MyList(0, 0) = "Line"
MyList(0, 1) = "Column A"
MyList(0, 2) = "Column B"

For i = 1 To 10
MyList(i, 0) = "#" & FormatNumber(i, 0)
MyList(i, 1) = "A" & FormatNumber(i, 0)
MyList(i, 2) = "B" & FormatNumber(i, 0)

Next i

ListBox1.ColumnCount = 3
ListBox1.List = MyList

End Sub

The Column property can be used to reference a particular column or row and column value. If only one argument is supplied to the Column property, you can assign values to the specified column in the list. Notice that the arguments are column number and row number, which is the reverse order of the List property.

The AddItem method is typically used to add a new item to the list. You may optionally specify the row where the new row should be placed. (Rows are numbered starting with row 0.) The RemoveItem method removes the specified row from the list. The Clear method removes all items from the list.

The TopIndex property contains the index of the first visible row in the list. The ListIndex property contains the index of the currently selected row. It might also have a value of -1, meaning that no row is currently selected. The Click event is called when the user clicks the control. This routine displays the currently selected item in the list when the user clicks the item.

Private Sub ListBox1_Click()

MsgBox ListBox1.ListIndex

End Sub

When the MultiSelect property is set to fmMultiSelectMulti, the user may select more than one item in the list by pressing the spacebar or clicking the desired item. A value of fmMultiSelectExtended allows the user to use the Shift and Ctrl keys to aid in the selection process. Holding the Shift key while moving the mouse pointer adds all the items between the last selected item and the item currently under the mouse pointer. The Ctrl key allows users to click and select multiple nonadjacent items. However, if the user releases both the Shift and Ctrl keys, clicking on a single item will clear the list and mark only the newly selected item.

When your program allows multiple items to be selected, you should use the Selected property along with the row number to determine the status of each row. A value of True means that the row is currently selected.

Setting the ListStyle property to fmListStylePlain means that the list of items are displayed as a normal list. Selected items are highlighted by changing the item's background color. However, a value of fmListStyleOption means that option buttons (MultiSelect=False) or check boxes (MultiSelect=True) will be displayed in front of each row to simplify the selection process.

The Text property contains the currently selected value from the list. If the list has more than one column, the TextColumn property identifies which column will be saved into the Text property.

The MatchEntry property determines how the ListBox can be searched. A value of fmMatchEntryFirstLetter means that when the user types a letter, the list is searched for the first row that has a matching character in the first position. Pressing the same character again will locate the second occurrence of the letter as the first character. A value of fmMatchEntryComplete allows the user to select the row by typing the prefix of characters that match the desired entry. A value of fmMatchEntryNone disables the match function.

The ComboBox Control

The ComboBox control is perhaps one of the most complicated controls in Visual Basic. It combines the functions of both a ListBox control and a TextBox control. All the properties and methods discussed in the previous section, 'The ListBox Control,' also apply to the ComboBox control.

The only feature of the ListBox control not supported by the ComboBox is the ability to select multiple items from the list. Therefore, the MultiSelect and Selected properties are not available in this control. Also, any of the features related to multi-line text boxes aren't present in the ComboBox control.

By default, a ComboBox consists of a text field in which the user can enter characters followed by a drop-down button, which when pressed will display a ListBox containing a list of values that can be selected. By setting the Style property to fmStyleDropDownList, the user may not type a value, but instead must choose one of the values displayed in the drop-down list. The currently selected value is displayed in the text box field.

You can also specify when the drop-down button is displayed at the end of the text field by setting the ShowDropButtonWhen property. Possible values include: fmShowDropButtonWhenNever, which hides the drop-down button; fmShowDropButtonWhenFocus, which shows the button only when the control has the focus; and fmShowDropButtonWhenAlways, which means that the button is always displayed.

You can also modify the symbol displayed on the drop-down button using the DropButtonStyle property. By default, a down arrow is displayed in the button (fmDropButtonStyleArrow). However, you can also display an ellipsis (…) by using the fmDropButtonStyleEllipsis or display an underscore (_) by specifying fmDropButtonStyleReduce. Finally, you can leave the button blank by choosing fmDropButtonStylePlain.

When AutoSize is True, the control will automatically be expanded to accommodate the longest line; otherwise, the drop-down list will be truncated at the edge of the control. If you merely want to control the width of the drop-down list, you can use the ListWidth property. Be sure to allow sufficient space for a vertical scroll bar when specifying a value for ListWidth.

The MatchFound property is True when the value displayed in the text box portion of the control matches one of the items in the list. If MatchRequired property is True, the user will not be allowed to leave the control until a matching entry is selected.

The RefEdit Control

The RefEdit control makes it easy to select a range of cells from a workbook. The user has the option to enter the text value of the range in the text box part of the control. However, pressing the button located to the right of the control will collapse the current form so that only the RefEdit control is visible and then transfer control back to Excel. There the user can select the range of cells that will appear in the text box. Pressing the button a second time will restore the original form with the newly selected range reference inserted in the RefEdit box.

The TabStrip Control

By providing a series of control containers such as the Frame control, plus a mechanism for selecting a container, the TabStrip control provides a simple way to fit more controls onto a form than otherwise would fit.

Each tab on the tab strip is an independent object within the TabStrip control. The Tabs collection returns a collection of Tab objects. Methods are included in the collection to Add a new Tab object, Remove an existing Tab object, or to Clear the collection. The currently selected tab can be referenced directly through the TabStrip control's SelectedItem property.

Each tab object contains unique Name and Index values, which are used to locate the tab from within the collection. The Caption property contains the text that will be displayed on the tab.

The Style property allows you to choose the way the tab information is displayed. By default, fmTabStyleTabs is selected. However, you can specify fmTabStyleButtons to display buttons instead of tabs. Finally, you can choose fmTabStyleNone so that no tab information is displayed on the control.

When the MultiRow property is True, the tab strip can have more than one row of tabs. The TabOrientation property specifies where the tabs are located. Valid choices for TabOrientation include: fmTabOrientationTop, fmTabOrientationBottom, fmTabOrientationLeft, and fmTabOrientationRight.

The size of the tabs are specified using the TabFixedHeight and TabFixedWidth properties. If these properties are assigned a value of zero, the tab sizes are automatically adjusted to fit the contents; otherwise, the value specifies the point size that will be used for each tab.

Typically, you'll use this control at design time and drag controls onto the various tab surfaces as you design your application. At run time, the user merely clicks the appropriate tab button to switch to the desired tab. You can add and remove forms within the TabStrip control by right-clicking the control and choosing the appropriate option from the pop-up menu.


No Programming Required
This control typically needs no programming. The only time you might want to write any code for a TabStrip control is when you want to automatically shift the user from one tab to another as a result of interacting with a control on one of the tabs.

The MultiPage Control

The MultiPage control is similar to the TabStrip control. It has a collection of Page objects, which correspond directly to the Tab objects. The primary difference between the TabStrip control and the MultiPage control is that the individual Page objects with the MultiPage control include a richer collection of properties and methods. In fact, the MultiPage control contains most of the properties and methods available on the UserForm object, such as the ability to display scroll bars and use pictures as the background.

The TransactionEffect property associated with the Page object controls the visual representation of one page moving to another. With this property, you can instruct the new page to move over the old page horizontally, vertically, or diagonally; or you can specify that the new page will 'push' the old page off the screen either horizontally or vertically. The TransitionPeriod property defines the milliseconds that the transaction effect will last.

In this chapter, you learned about user forms and the controls you can add to them. User forms are useful when you need to prompt the user to enter information or display a result to the user. The most common controls are the label control, the text box control, and the command button control. The label control provides a way to display information to the user. The text box control provides a way for the user to accept information from the user, while the command button control provides a convenient way for the user to request the application to perform a specific task. The other controls available for a user form provide alternate ways to present information to a user or accept information from a user.

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: