Excel VBA Macro Programming

The following sections discuss the controls that initially appear in the toolbox.

Label

The Label control displays text on the form. You can enter text by entering it in the Caption property in the Properties window at the bottom left-hand corner of screen.

Other properties that can be set for the label include BackColor , ForeColor , Font , TextAlign , and WordWrap . This is not a complete list of properties; many more can be set. You can see other properties available within the Properties window for that control.

TextBox

The TextBox control is similar to the label control, but it allows the user to input text at runtime. You can also reference it to a control source (via properties), such as a spreadsheet cell, like sheet1!a1. If you do this, not only does the text box take the value of that cell, but anything typed into the text box is written back to that cell ‚ the cell effectively acts as a variable in storing the data.

Properties that can be set include BackColor , Enabled , ForeColor , Font , Locked , TextAlign , MaxLength , Mulitiline , PasswordCharacter , and WordWrap .

ComboBox

The ComboBox control is the familiar horizontal box in which a list of values appears when you click the downward pointing arrow. It shares the same properties we discussed for the text box, apart from the password character. There are also some new properties:

ListBox

The ListBox control shows a permanently displayed list of optional values. It has similar properties as the combo box but there are some extra ones:

In the example in Figure 9-6, the RowSource property is set to sheet1!a2..b4, the ColumnCount property is set to 2 (to give two columns of data), and the ColumnHeads property is set to True (meaning that the first row above the row source data will provide the column headings).

Figure 9-6: A list box on a UserForm

To read multiple selections, double-click the list box to get to the module and then click the Exit event on the drop-down on the right-hand side. Enter the following code:

Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) For n = 0 To UserForm1.ListBox1.ListCount - 1 If UserForm1.ListBox1.Selected(n) = True Then MsgBox UserForm1.ListBox1.List(n, 0) End If Next n End Sub

When the form is closed, this fires off the Exit event on the list box that then looks through each value in the list to see if it was selected. Note that it starts at index 0, so you need to subtract 1 from the ListCount property for the For..Next loop. This will then display all rows selected. The second parameter (0) is optional ‚ this denotes the column. Note that it starts at 0, not 1. You know what has been selected because the Selected property is set to True.

Refer back to Figure 9-5 for an example screen of a multicolumn list box using data from a spreadsheet.

CheckBox

The CheckBox control allows the user to check or uncheck a check box. The text can be set by double-clicking the control or by setting the Caption property. It can be linked to a cell by setting the ControlSource property to point to a cell such as sheet1!a1. If the cell contains no value, the check box will contain a check and therefore a True value by default. Note that the control source cell will display the value True or False to reflect the state of the check box.

The code to interpret the check box would be the same as for the ListBox control but with the check box Change event:

Private Sub CheckBox1_Change() MsgBox UserForm1.CheckBox1.Value End Sub

Every time the check box is checked or unchecked, you will get a message box showing its value.

OptionButton

Option buttons are sometimes known as radio buttons when there is a group of them because they behave like the pushbuttons on an old car radio. You need to have at least two of these on your form for them to work properly because as you switch one on, the others are automatically turned off. If you have only one, you can never turn it off!

Option buttons can be linked to a cell using the ControlSource property, such as sheet1!c1. The cell holds a True or False value dependent on whether the option button has been clicked.

You can use code to interpret the value of each radio button. Try the following code on the option button Change event:

Private Sub OptionButton1_Change() MsgBox UserForm1.OptionButton1.Value End Sub

ToggleButton

A toggle button changes from raised to sunken and back each time it is clicked. Its operation is similar to a check box: you change the caption on it by setting the Caption property in the properties box. It can be linked to a cell by setting the ControlSource property, such as sheet1!d1.

You can use code to interpret the value of the toggle button. Try the following code on the toggle button Change event:

Private Sub ToggleButton1_Change() MsgBox UserForm1.ToggleButton1.Value End Sub

Frame

Frames allow you to put a frame around a set of related controls to describe what they do. You can only set the caption of the frame by setting the Caption property ‚ you cannot double- click it as with the check box and the option button. One of the problems with a Frame control is that it overlays previous controls, even at runtime. When using a Frame control, you should define the Frame control first and then make the other controls sit inside the frame on top of it. If you do it the other way around, the controls will not be visible.

CommandButton

The CommandButton control is a powerful control that frequently is used on forms. You can alter a command button's caption by double-clicking it or by setting the Caption property. You can also make a button the default button on the form by setting the Default property to True. This means that when the form is loaded, this button has the focus ‚ if the user presses Enter , then the code for that button will run.

To make the button do something, you need to put code in an event. Double-click the form to enter the module and then select your button control from the drop-down on the top left-hand side. Select the Click event and enter the following code:

Private Sub CommandButton1_Click() MsgBox "You pressed the button" End Sub

TabStrip

The TabStrip control allows you to put a tab strip onto your form. You can use a TabStrip control to view different sets of information for related controls. The client region of a TabStrip control is not a separate form. Instead, the region is a portion of the form that contains the TabStrip control.

You can select the tabs at design time by clicking the tab while holding down Shift . You can add pages, rename pages, and delete pages by right-clicking a tab. You need to use code to interpret the user's actions on the tab strip. Double-click the form to view the module and select TabStrip from the top-left drop-down. Select the Change event from the top-right drop-down and enter the following code:

Private Sub TabStrip1_Change() MsgBox TabStrip1.SelectedItem.Index MsgBox TabStrip1.SelectedItem.Caption End Sub

When you run the form and click the tabs, you will see the index of the selected tab (first tab is 0) and the caption on it.

You can further see the effect of the TabStrip control by putting the following code under the Change event:

Private Sub TabStrip1_Change() Select Case TabStrip1.SelectedItem.Index Case 0 TabStrip1.ForeColor = QBColor(12) Case 1 TabStrip1.ForeColor = QBColor(15) End Select End Sub

Each time you click a tab, the text color on the tabs will change. Note that both tabs change to the same color ; they are not separate forms.

MultiPage

The TabStrip control is still only one form. The MultiPage control is different forms selected by tabs and is more useful in a lot of ways. You can select each tab at design time by clicking it and right-click the tab to insert, delete, or rename pages. You can drag controls onto each individual tab page. Try putting a text box onto the first page and then putting a command button onto the second page.

When you run the form, each page behaves like a separate form, displaying the controls that you set up on it. They all behave as if they were on a separate form. Notice that when you click each page at design time, there are separate Properties windows for each page, which was not the case for the TabStrip control.

You can use code to interpret the user's actions similar to the TabStrip. Double-click the tab page, and it will enter the module at the Change event for the MultiPage control. Enter the following code:

Private Sub MultiPage1_Change() MsgBox MultiPage1.SelectedItem.Caption End Sub

ScrollBar

The ScrollBar control places a vertical scroll bar onto your form, similar to the ones that you see in many Microsoft applications. There are properties for maximum and minimum values and for small and big changes, called SmallChange and BigChange . A small change is when you click one of the arrows on the scroll bar; a big change is when you click the area between the arrow and the cursor on the scroll bar.

BigChange and SmallChange are set by default to 1, the maximum value is set to 32767, and the minimum to 0. This means that clicking the arrows or the space between arrow and cursor does not really move anything very far: BigChange needs to be about 1000 and SmallChange needs to be about 100 if the maximum is set to 32767.

You can link this control to a spreadsheet cell using the ControlSource property, such as sheet1!a1. The value of the scroll bar will appear in the cell, but it is only updated when the scroll bar has lost focus (when you click another control).

You can also use code to read the value of the scroll bar. Double-click the scroll bar, and this will take you into the scroll bar Change event. Enter the following code:

Private Sub ScrollBar1_Change() MsgBox ScrollBar1.Value End Sub

Every time the scroll bar is moved, a message box appears with the new value. You can see how it changes according to what value you set BigChange and LittleChange to.

SpinButton

The SpinButton control is normally linked to another control, such as a text box, in order to display a value.

You can link the control to a spreadsheet cell by using the ControlSource property, such as sheet1!a1. The value of the spin button will appear in the cell, but it is only updated when the spin button has lost focus (when you click another control).

The SmallChange property sets the increment of the change. The Orientation property sets whether the control is split vertically or horizontally.

You can also use code to read the value of the spin button. Double-clicking the spin button will take you into the spin button Change event. Enter the following code:

Private Sub SpinButton1_Change() MsgBox SpinButton1.Value End Sub

Image

The Image control is one that can hold a picture or an image. To insert a picture, click the Picture property and then click the ellipsis (...) box that appears. This will take you into a screen where you can select an image file. Click OK and the picture will be inserted into your control.

To delete the picture, delete the value in the Picture property box. This will be replaced with (None).

RefEdit

The RefEdit control collects details about a range of cells selected by the user. It also allows the user to manually type their range in, which means they can edit a cell reference. When you place this control on a form, it is best to size it like a text box using small height and large width.

When the form runs, the user can either type in the cell references manually or click the button on the right-hand side of the control. The form will then collapse and a range selection window will appear. Drag the cursor over a selection with the mouse, and the selection details will appear in the window. Click the button on the right-hand side with red in it, and the form will be redisplayed with the selection in the control box.

You can also use code to read the value of the RefEdit box. Double-clicking the RefEdit box will take you into the RefEdit Change event. Enter the following code:

Private Sub RefEdit1_Change() UserForm1.Caption = RefEdit1.Value End Sub

When you close the selection box and the UserForm reappears, you will see that the title bar of the form now has your selection details in it.