| The default Toolbox offers 14 different controls for your custom forms. The next few sections introduce you to each type of control and show you the various options and properties associated with each object. Command Buttons Most forms include command buttons to let the user accept the form data (an OK button), cancel the form (a Cancel button), or carry out some other command at a click of the mouse. To create a command button, use the CommandButton tool in the Toolbox. A command button is a CommandButton object that includes many of the common control properties mentioned earlier, as well as the following design-time properties (among others): Cancel ” If this property is set to True, the button is selected when the user presses Esc. Caption ” Returns or sets the text that appears on the button face. Default ” If this property is set to True, the button is selected when the user presses Enter. Also, the button is displayed with a thin black border. Labels You use labels to add text to the form. To create labels, use the Label button in the Toolbox to draw the label object, and then edit the Caption property. Although labels are mostly used to display text, you can also use them to name controls that don't have their own captions ”such as text boxes, list boxes, scroll bars, and spinners. It's even possible to define an accelerator key for the label and have that key select another control. For example, suppose you want to use a label to describe a text box, but you also want to define an accelerator key that the user can press to select the text box. The trick is that you must first create a label and set its Accelerator property. You then create the text box immediately after. Because the text box follows the label in the tab order, the label's accelerator key will select the text box. tip  | To assign a label and accelerator key to an existing control, add the label and then adjust the tab order so that the label comes immediately before the control in the tab order. |
Text Boxes Text boxes are versatile controls that let the user enter text, numbers , and, in Excel, cell references and formulas. To create a text box, use the TextBox button in the Toolbox. Here are a few useful properties of the TextBox object: EnterFieldBehavior ” Determines what happens when the user tabs into the text box. If you select 0 ( fmEnterFieldBehaviorSelectAll ), the text within the field is selected. If you select 1 ( fmEnterFieldBehaviorRecallSelect ), only the text that the user selected the last time he was in the field will be selected. EnterKeyBehavior ” When set to True, this property lets the user start a new line within the text box by pressing Enter. (Note that this is applicable only if you set MultiLine to True, as described in a moment.) When this property is False, pressing Enter moves the user to the next field. MaxLength ” This property determines the maximum number of characters that the user can enter. MultiLine ” Set this property to True to let the user enter multiple lines of text. PasswordChar ” If this property is set to True, the text box displays the user's entry as asterisks (which is useful if you're using the text box to get a password or other sensitive data). Text ” Returns or sets the text inside the text box. WordWrap ” When this property is True, the text box wraps to a new line when the user's typing reaches the right edge of the text box. Frames You use frames to create groups of two or more controls. There are three situations in which frames come in handy: -
To organize a set of controls into a logical grouping ” Let's say your form contains controls for setting program options and obtaining user information. You could help the user make sense of the form by creating two frames ”one to hold all the controls for the program options, and one to hold the controls for the user information. -
To move a set of controls as a unit ” When you draw controls inside a frame, these controls are considered to be part of the frame object. Therefore, when you move the frame, the controls move right along with it. This can make it easier to rearrange multiple controls on a form. -
To organize option buttons ” If you enter multiple option buttons inside a frame (see the next section), VBA treats them as a group and therefore allows the user to activate only one of the options. To create a frame, click the Frame button in the Toolbox and then click and drag a box inside the form. Note that you use the Frame object's Caption property to change the caption that appears at the top of the box. Option Buttons Option buttons are controls that usually appear in groups of two or more; the user can select only one of the options. To create an option button, use the OptionButton tool. You can determine whether an option button starts off activated or deactivated by setting the Value property: If it's True, the option is activated; if it's False, the option is deactivated. For option buttons to work effectively, you need to group them so that the user can select only one of the options at a time. VBA gives you three ways to do this: -
Create a frame and then draw the option buttons inside the frame. -
Use the same GroupName property for the options you want to group. -
If you don't draw the option buttons inside a frame or use the GroupName property, VBA treats all the option buttons in a form as one group. tip | | If you already have one or more "unframed" option buttons on your form, you can still insert them into a frame. Just select the buttons, cut them to the Clipboard, select the frame, and paste. VBA will add the buttons to the frame. |
Check Boxes Check boxes let you include options that the user can toggle on or off. To create a check box, click the CheckBox button in the Toolbox. As with option buttons, you can control whether a check box is initially activated (checked). Set its Value property to True to activate the check box, or to False to deactivate it. Toggle Buttons A toggle button is a cross between a check box and a command button: Click it once, and the button stays pressed; click it again, and the button returns to its normal state. You create toggle buttons by using the ToggleButton tool in the Toolbox. You control whether a toggle button is initially activated (pressed) by setting its Value property to True to "press" the button or to False to "unpress" the button. List Boxes VBA offers two different list objects you can use to present the user with a list of choices: a ListBox and a ComboBox . The ListBox Object The ListBox object is a simple list of items from which the user selects an item or items. Use the ListBox button to create a list box. Here are some ListBox object properties to note: ColumnCount ” The number of columns in the list box. ColumnHeads ” If this property is True, the list columns are displayed with headings. MultiSelect ” If this property is True, the user may select multiple items in the list. RowSource ” Determines the items that appear in the list. In Excel, enter a range or a range name. Text ” Sets or returns the selected item. The ComboBox Object The ComboBox object is a control that combines a text box with a list box. The user clicks the drop-down arrow to display the list box and then selects an item from the list or enters an item in the text box. Use the ComboBox button to create this control. Because the ComboBox is actually two separate controls, the available properties are an amalgam of those discussed earlier for a text box and a list box. You can also work with the following properties that are unique to a ComboBox object: ListRows ” Determines the number of items that appear when the user drops the list down. MatchRequired ” If this property is True, the user can only enter values from the list. If it's False, the user can enter new values. Style ” Determines the type of ComboBox. Use 0 ( fmStyleDropDownCombo ) for a list that includes a text box; use 2 ( fmStyleDropDownList ) for a list only. List Box Techniques How do you specify the contents of a list if the RowSource property isn't applicable (that is, if you're not working in Excel or if the data you want in the list isn't part of an Excel range)? In this case, you must build the list at runtime. You can use the AddItem method, described later in this section, or you can set the List property. For the latter, you must specify an array of values. For example, the following statements use a form's Initialize event to populate a list box with the days of the week: Private Sub UserForm_Initialize() ListBox1.List() = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday",  "Saturday", "Sunday") End Sub List boxes also have a few useful methods for controlling from your VBA code the items that appear in a list box: AddItem ” Adds an item to the specified list box. Here's the syntax: object .AddItem( text,index ) | object | The name of the ListBox object to which you want to add the item. | | text | The item's text. | | index | The new item's position in the list. If you omit this argument, VBA adds the item to the end of the list. | Clear ” Removes all the items from the specified list box. RemoveItem ” Removes an item from the specified list box using the following syntax: object .RemoveItem( index ) | object | The ListBox object from which you want to remove the item. | | index | The index number of the item you want to remove. | Scrollbars Scrollbars are normally used to navigate windows , but by themselves , you can use them to enter values between a predefined maximum and minimum. Use the ScrollBar button to create either a vertical or horizontal scrollbar. Here's a rundown of the ScrollBar object properties you'll use most often in your VBA code: LargeChange ” Returns or sets the amount that the scrollbar value changes when the user clicks between the scroll box and one of the scroll arrows. Max ” Returns or sets the maximum value of the scrollbar. Min ” Returns or sets the minimum value of the scrollbar. SmallChange ” Returns or sets the amount that the scrollbar value changes when the user clicks one of the scroll arrows. Value ” Returns or sets the current value of the scrollbar. Spin Buttons A spin button is similar to a scrollbar in that the user can click the button's arrows to increment or decrement a value. To create a spin button, use the SpinButton tool in the Toolbox. The properties for a SpinButton object are the same as those for a ScrollBar (except that there is no LargeChange property).  | I use Excel as the underlying application for the procedures in this chapter. To get the code for these procedures, see my Web site: http://www.mcfedries.com/ABGVBA/Chapter13.xls For Listing 13.1, see the code for the form named TestForm. |
Most spin buttons have a text box control beside them to give the user the choice of entering the number directly or selecting the number by using the spin button arrows. You have to use VBA code to make sure that the values in the text box and the spinner stay in sync. (In other words, if you increment the spinner, the value shown in the text box increments as well, and vice versa.) To do this, you have to add event handler code for both controls. For example, suppose you have a text box named TextBox1 and a spin button named SpinButton1 . Listing 13.1 shows the basic event handler code that will keep the values of these two controls synchronized. Listing 13.1. Event Handler Code that Keeps a Text Box and a Spin Button in Sync Private Sub TextBox1_Change() SpinButton1.Value = TextBox1.Value End Sub Private Sub SpinButton1_Change() TextBox1.Value = SpinButton1.Value End Sub Tab Strips and MultiPage Controls I mentioned earlier that you can use frames to group related controls visually and help the user make sense of the form. However, there are two situations in which a frame falls down on the job. The first situation is when you need the form to show multiple sets of the same (or similar) data. For example, suppose you have a form that shows values for sales and expense categories. You might want the form to be capable of showing separate data for various company divisions. One solution would be to create separate frames for each division and populate each frame with the same controls, but this is clearly inefficient. A second solution would be to use a list or a set of option buttons. This will work, but it might not be obvious to the user how he is supposed to display different sets of data, and these extra controls just serve to clutter the frame. A better solution is to create a tabbed form where each tab represents a different set of data. The second situation is when you have a lot of controls. In this case, even the judicious use of frames won't be enough to keep your form from becoming difficult to navigate and understand. In situations where you have a large number of controls, you're better off creating a tabbed form that spreads the controls over several tabs. In both of these situations, the tabbed form solution acts much like the tabbed dialog boxes you work with in Windows, Office, and other modern programs. To create tabs in your forms, VBA offers two controls: TabStrip and MultiPage . The TabStrip Control The TabStrip is an ideal way to give the user an intuitive method of displaying multiple sets of data. The basic idea behind the TabStrip control is that as the user navigates from tab to tab, the visible controls remain the same, and only the data displayed inside each control changes. The advantage here is that you need to create only a single set of controls on the form, and you use code to adjust the contents of these controls. You create a TabStrip by clicking the TabStrip button in the Toolbox and then clicking and dragging the mouse until the strip is the size and shape you want. Here are a few points to keep in mind: -
The best way to set up a TabStrip is to add it as the first control on the form and then add the other controls inside the TabStrip . -
If you already have controls defined on the form, draw the TabStrip over the controls and then use the Send to Back command (described earlier) to send the TabStrip to the bottom of the Z-order. -
You can also display a series of buttons instead of tabs. To use this format, select the TabStrip and change the Style property to fmTabStyleButtons (or 1). Figure 13.5 shows a form that contains a TabStrip control and an Excel worksheet that shows budget data for three different divisions. The goal here is to use the TabStrip to display budget data for each division as the user selects the tabs. Figure 13.5. Using the form's TabStrip to display budget data from the three divisions in the Excel worksheet.  The first order of business is to use code to change the tab captions, add a third tab, and enter the initial data. Listing 13.2 shows an Initialize event procedure that does just that. Listing 13.2. An Initialize Event Procedure that Sets Up a TabStrip Private Sub UserForm_Initialize() ' ' Rename the existing tabs ' With TabStrip1 .Tabs(0).Caption = "Division I" .Tabs(1).Caption = "Division II" ' ' Add a new tab ' .Tabs.Add "Division III" End With ' ' Enter the intial data for Division I ' With Worksheets("2004 Budget") txtSales = .[B2] txtExpenses = .[B12] txtGrossProfit = .[B13] End With End Sub The code first uses the Tabs collection to change the captions of the two existing tabs. The Tabs collection represents all the tabs in a TabStrip , and you refer to individual tabs using an index number (where the first tab is 0, the second is 1, and so on). Then the Tabs collection's Add method is used to add a third tab titled Division III to the TabStrip . Finally, the three text boxes within the TabStrip (named txtSales , txtExpenses , and txtGrossProfit ) are set to their respective values for Division I in the 2004 Budget worksheet. Now you must set up a handler for when the user clicks a tab. This fires a Change event for the TabStrip , so you use this event handler to adjust the values of the text boxes, as shown in Listing 13.3. Listing 13.3. A Change Event Procedure that Modifies the Controls Within a Tab Strip Whenever the User Selects a Different Tab Private Sub TabStrip1_Change() With Worksheets("2004 Budget") Select Case TabStrip1.Value Case 0 ' ' Enter the data for Division I ' txtSales = .[B2] txtExpenses = .[B12] txtGrossProfit = .[B13] Case 1 ' ' Enter the data for Division II ' txtSales = .[C2] txtExpenses = .[C12] txtGrossProfit = .[C13] Case 2 ' ' Enter the data for Division III ' txtSales = .[D2] txtExpenses = .[D12] txtGrossProfit = .[D13] End Select End With End Sub Here, a Select Case checks the Value property of the TabStrip (where the first tab has the value 0, the second tab has the value 1, and so on). Figure 13.6 shows the form in action. (See "Displaying the Form" later in this chapter to learn how to run a form.) Figure 13.6. Clicking each tab displays the data for the appropriate division.  The MultiPage Control The MultiPage control is similar to a TabStrip in that it displays a series of tabs along the top of the form. The major difference, however, is that each tab represents a separate form (called a page ). Therefore, you use a MultiPage control whenever you want to display a different set of controls each time the user clicks a tab. You add a MultiPage control to your form by clicking the MultiPage button in the Toolbox and then clicking and dragging the mouse until the control is the size and shape you want. It's important to remember that each page in the control is a separate object (a Page object). So each time you select a page, the values that appear in the Properties window apply only to the selected page. For example, the Caption property determines the text that appears in the page's tab. Also, you set up a page by selecting it and then drawing controls inside the page. (If you have controls on the form already, you can put them inside a page by cutting them to the Clipboard, selecting the page, and pasting the controls.) Working with a MultiPage control in code is very similar to working with a TabStrip : -
The Pages collection represents all the pages inside a MultiPage control. You refer to individual pages using their index numbers. -
Use the Pages.Add method to add more pages to the control. -
When the user selects a different tab, the MultiPage control's Change event fires. | 