March 1, 2011, 5:24 a.m.
posted by dante
Types of Form Controls
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.
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):
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.
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.
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 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.
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.
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.
The ListBox Object
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:
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
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:
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).
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.
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
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:
Figure.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.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.
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.6 shows the form in action. (See "Displaying the Form" later in this chapter to learn how to run a form.)
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: