Controls - Part 2

LESSON WORKBOOKS:  UserForms Lesson 3 2007.xlsx

This lesson will introduce the common ActiveX controls CheckBoxes, OptionButtons and Frames, SpinButton, TabStrip, MultiPage, and Image.

Download the UserForms Lesson 3 2007.xlsx  file for examples of using the various controls.  The Lesson 3 sheet has the examples for this lesson.

For reference (Controls in bold are covered in this lesson):

Control

Short Description

Default Property

Default Event

Label

Displays fixed text

Caption

Click

TextBox

Displays entered text or for edit

Value

Change

ListBox

Displays a list of selectable items

Value

Click

ComboBox

Combines TextBox and ListBox.

Value

Change

CheckBox

For selecting any number of items

Value

Click

Frame

Used to group OptionButtons

(none)

Click

OptionButton

For selecting only one item

Value

Click

CommandButton

Click to execute code

Value

Click

ToggleButton

A two-state CommandButton

Value

Click

TabStrip

Displays tabs of the same limited info

SelectedItem

Change

MultiPage

Splits large amounts of info into pages

Value

Change

ScrollBar

Displays a vertical or horizontal scrollbar

Value

Change

SpinButton

Increments and decrements numbers

Value

Change

Image

Displays an image.

(none)

Click

 OptionButtons and Checkboxes

OptionButtons and CheckBoxes allow the user to select items from a list.  The difference is that more than one CheckBox can be checked, while only one OptionButton can be selected.  Once an OptionButton is checked, checking another one automatically turns off the one previously checked.  If there is only one item in the list, a CheckBox has some use, but an OptionButton probably has none.  Since an OptionButton is turned off by clicking another one, a single OptionButton, once clicked, will remain selected for the duration of the UserForm.  If a once-only function is needed, then a single OptionButton would fill the need.

CheckBoxes

A CheckBox simply returns True or False.  Besides selecting from a list, it can also select between two conditions, as with a ToggleButton.  For example, if a UserForm button initiates a time-consuming procedure which would run faster if calculation is set to manual, the calculation mode can be controlled by CheckBox.

EXAMPLE

From the Lesson 3 sheet of the workbook attached, click the “CheckBox and OptionButton Demos” button for examples.

Run the UserForm and select/deselect each CheckBox.  Checking one CheckBox has no effect on the others, so more than one CheckBox can be checked.  Checked CheckBoxes can also be unchecked.  The “See” CheckBox cycles through a gray state, which will be explained later in the TripleState section.  The CheckBoxes are linked to cells D12:D14.  Their values can be seen there at all times.  D12 is blank when the “See” CheckBox is gray.

EXERCISE

Place a CheckBox1 on a UserForm and change its Caption to “Turn Off Calculations.  Insert this code in the Private Module of the UserForm:

Private Sub CheckBox1_Click()

    If CheckBox1.Value = True Then
        Application.Calculation = xlCalculationManual
    Else
        Application.Calculation = xlCalculationAutomatic
    End If

End Sub

Run the UserForm and check the CheckBox.  Close the UserForm, go to Tools>Options>Calculation and note that the calculation mode for Excel has been switched to manual.  Don’t forget to click the CheckBox again, to change it back to automatic, or a very long time can be spent looking for why a spreadsheet has stopped working.

For this reason, before changing a setting on a user's PC, it is always very important to first save the state of the setting, so it can be restored when done.  In this example, the state is saved to a variable declared at the Module level.  The value of such a variable persists after the procedure ends, so it can be used later in another procedure.  In this exercise, the state is saved when the UserForm initializes, and is restored when the UserForm is deactivated:

Dim AppCalc As XlCalculation

 

Private Sub CheckBox1_Click()

    If CheckBox1.Value = True Then
     Application.Calculation = xlCalculationManual
    Else
     Application.Calculation = xlCalculationAutomatic
    End If

End Sub

 

Private Sub UserForm_Initialize()
    AppCalc = Application.Calculation
End Sub

  

Private Sub UserForm_Deactivate()
    Application.Calculation = AppCalc
End Sub

It is important to place the code in the correct event handler, and that no code changes the variable.  Still, there are a number of operations that can interrupt before the cleanup code is run, it would be even better to store the setting in a cell on a hidden worksheet.  Interrupting events could include user intervention or an unexpected error.

OptionButtons

One OptionButton by itself is useless.  It is important to associate members of a group of two or more that will be mutually exclusive.  The two ways of grouping OptionButtons are the GroupName property of the OptionButton, and the Frame control. 

The Frame control is handy because its border visually sets apart the OptionButtons and its title identifies the condition being selected.  To use it, place a Frame control on the UserForm and expand it to a size somewhat larger than might be needed to hold that set of OptionButtons.  Place the OptionButtons within the frame control.  If the UserForm will have multiple sets of OptionButtons for different purposes, the Frame control is one way to differentiate sets.

The second way to group OptionButtons is with GroupName.  If the OptionButtons will be arranged in an irregular pattern, or will be added to the UserForm dynamically through code, then Frames will not work and the GroupName variable must be used.  It is normally a one word string, though if a UserForm has only one group of OptionButtons, it may be left blank.

A GroupName must only be unique to a container.  Besides UserForm and Frame, there are other containers such as the pages of a MultiPage control.  For example, a MultiPage control with three pages could have OptionButtons with the same GroupName on each page, and still function as expected. 

Each OptionButton on a UserForm which has the same GroupName, or is within a Frame, results in the creation of a group of mutually exclusive OptionButton controls.  To be mutually exclusive means that when one of the group is selected, selecting a different optionbutton of the group will de-select the first one.  There is no exception to this rule. 

Some advantages of using the GroupName property instead of a Frame control:

·         Fewer controls on the UserForm slightly enhances performance and reduces the size of the UserForm.

·         More layout flexibility, in that use of a Frame requires all the OptionButtons to be within the Frame's boundary. 

·         OptionButtons with transparent backgrounds can enhance the visual appearance of the userform.  The Frame control cannot be transparent. 

Regardless of which grouping method we choose, an OptionButton simply returns a Boolean value of True or False.

EXERCISE

Insert a new UserForm into a Workbook.  Add two OptionButtons named OptionButton1 and OptionButton2.  In the Private Module of the UserForm object, add this code:

Private Sub OptionButton1_Click()

    Debug.Print OptionButton1, OptionButton2

End Sub

Private Sub OptionButton2_Click()

    Debug.Print OptionButton1, OptionButton2

End Sub

Click OptionButton1, then OptionButton2, then OptionButton2 a second time.  In the Immediate Window, see that True is returned for the OptionButton that is clicked, and False is returned for the other, except the second time OptionButton2 is clicked.  The Click Event only runs for an OptionButton having a False value.  Note also that no OptionButton property was specified, so the code used the default Value property.

OptionButton with a Control Source

OptionButton can behave slightly differently if ControlSource is set to a cell address.

EXERCISE

            Using the same OptionButtons as in the above example:

·         Remove the Click Event code for both OptionButtons from the UserForm Private Module.

·         Ensure cells A1 and A2 are empty on the active worksheet

·         Assign OptionButton1's Control Source to A1, and OptionButton2's Control Source to A2.  This is also called binding.

·         Set the Value Property of OptionButton1 to True.

·         Run the UserForm and move it out the way so that cells A1 and A2 can be seen on the active worksheet.  Even though the Value property of OptionButton1 is set to True, its Control Source will still be empty while the Control Source for OptionButton2 will show False.

·         Click each button and see the button Value reflect in the bound cell.  Note that selecting an OptionButton that currently is False, or unchecked will first deselect the other OptionButton (leaving both OptionButtons unchecked) and it is only when the OptionButton is clicked for a second time that it returns True. 

EXAMPLE

From the Lesson 3 sheet of the attached file  UserForms Lesson 3 2007.xlsx, click the “CheckBox and OptionButton Demos” button.

Run the UserForm and click each OptionButton.  The OptionButtons within Frame1 are bound to cells D16:D18, and their values can be seen there.  The “See” OptionButton with Frame1 is initialized to a gray state, which will be explained next in the TripleState section.  D16 is blank when initialized.

TripleState Property

CheckBoxes and OptionButtons both have the TripleState property, which allows the Null value in addition to False and True.  Null is a value indicating that a variable contains no valid data.  CheckBoxes and OptionButtons are shaded gray when Null.

TripleState defaults to False and can be changed in the Properties window.  A TripleState CheckBox will cycle from False to Null to True as it is clicked multiple times.  An OptionButton can only be Null through code or at initialization.  Once another OptionButton is clicked, the Null is gone until the next time it is set by code.  As such, it is not very useful, so TripleState is normally used only for CheckBoxes.

The Null state of a CheckBox or OptionButton does not fire the Click Event.

EXAMPLE

From the Lesson 3 sheet of the attached file  UserForms Lesson 3 2007.xlsx, click the “CheckBox and OptionButton Demos” button for examples.

As mentioned above, the “See” CheckBox cycles through the gray state.  To see that the Null state does not fire the Click event, click the “See” CheckBox several times while watching the immediate window.  Only the values False and True run the click event handler.

The “See” OptionButton within Frame1 is initialized to the Null state.  Click it, and it changes to True.  Close the UserForm and restart it.  This time click either “Dick” or “Run.”  “See” goes from Null to False.

SpinButtons

The SpinButton Control simply increments and decrements a number.  The default property for a SpinButton is the Value property, while the default event for a SpinButton is the Change event.  Some default properties are Min 0, Max 100, and SmallChange (step size) 1.  These can be changed to any value that fits within the scope of a Long, ie -2,147,483,648 to +2,147,483,647, though the Excel Helps recommend a range of values is from –32,767 to +32,767.

EXERCISE

·         Insert a UserForm .

·         On it place a TextBox.

·         To the right of the Textbox place a SpinButton.

·         Right click on the SpinButton and select Properties to display the Property Window for the SpinButton

·         Change Min to -3, Max to 4 and SmallChange to 2.

·         Double click the SpinButton Control and within the default Change Event type TextBox1.Value=SpinButton1.Value

·         Run UserForm and use SpinButton to increment or decrement the number.  Note that when there is not enough left to accommodate the full step size, the number goes to Max or Min

Many users find it more convenient to change a number with a SpinButton rather than type it.  It’s easier for the programmer, too, since the number is known to be valid without lengthy validation code.

EXERCISE

Assume a TextBox defaulted to today's date and the user is to be able to increment that date by one day up to any day one year ahead.  To allow the user to simply add say 21 days to a date, is fraught with potential disasters.  This example shows how the SpinButton can do this safely.

·         Double click the UserForm

·         Change the default Click Event to the Initialize Event and within here place TextBox1.Value = Format(Date, "ddd d mmm yyyy")

·         Above the TextBox add a Label control and change its caption to today's date. 

·         Now within the SpinButton1_Change Event change our existing code to TextBox1.Value=Format(Date + SpinButton1.Value, "ddd d mmm yyyy") and below this type Label1.Caption=SpinButton1.Value & " day(s) from now"

·         Change the Min Property of the SpinButton to 0 and the Max Property of the SpinButton to 365

·         Increase the TextBox width so it will display the date in the format chosen

·         Run UserForm

The UserForm starts with the TextBox displaying the current date, including the name of the day of the week.  Use the SpinButton to increment the date.  Note that the date in the TextBox will increment by one day each time, to the maximum of today + 365 days.  Using this method ensures that the user will not type an invalid date nor will the program miscalculate the date.  In fact, it would be impossible to do so.

TabStrips and MultiPages

Both TabStrips and MultiPages are useful for displaying records from a database where each record has many fields. 

TabStrips display a record for each tab.  Each tab displays the same fields.  The TabStrip is best where some small number of fields is to be displayed, because of the limited number of controls which fit on a UserForm before it looks cluttered.  The big advantage of TabStrips is that all the records available can be seen at a single glance.  Additional tabs can be created by code, but the maximum is somewhat limited by the number of tabs which can be displayed.  Depending on the width of the control and the text length of the tab captions, perhaps 6 to 10 records can be displayed on a single row across the top or bottom, and up to 20 records by turning on the MultiRow property.  More can be accommodated by showing the tabs on the left or right.

MultiPages display multiple pages for each record.  Each page displays different fields, so it is the control of choice when many fields are to be displayed for each record, or if the database has too many records to display each on its own tab.  Another control such as ComboBox or ListBox is then required to select the record to display.

These are the most complex of the standard controls because they require extra steps in initialization and when switching records.  Fields for both controls are usually set at design time.  Unless the number of records to be displayed is known at design time, then adding more tabs at run time might make TabStrips a little more difficult to use.

TabStrip is a collection of tab objects and MultiPage is a collection of page objects.

TabStrip Control

To designing a TabStrip, two questions must be answered:

1.      How many tabs are needed?

2.      Will the number of tabs vary from one time to the next?

If the number can vary, then design for the minimum number of tabs, and have the initialize procedure add the rest.  Each tab object is populated when its tab is clicked.

Private Sub UserForm_Initialize()
    ‘ set required tab count
    ‘ for count to required tab count

    ‘    add one tab
    ‘ for 1 to required count

    ‘    set each tab caption
End Sub


Private Sub TabStrip1_Click()
    ‘    set fields
End Sub

EXAMPLE

From the Lesson 3 sheet of the demo file, click the “TabStrip and MultiPage Demos” button for an example.  It is initialized with the first four records of the Lesson 2 Demo Database.  Clicking a tab displays the fields for that record.

MultiPage Control

A common use of MultiPage is to display a record with enough fields that they look cluttered on a UserForm, or with so many fields that they won’t even fit.  A problem, of course, is dividing the fields to be displayed into pages.  Grouping fields by function, or by topic, or by order to be entered, will help the user to intuitively understand what is being shown.  Many fields will group naturally, but there will always be some that are one-of-a-kind.  Frequent use of use of labels will help make clear what is being shown on each page.

Using MultiPage to display records of a database requires a method to quickly locate the record to be displayed.  For smaller databases, this could be a ComboBox or ListBox.  The automatic ScrollBar will allow quick navigation bring the record into view where it can be clicked.  For larger databases, the choice depends on whether the main field is sorted or not.  If sorted, then use a TextBox to enter and a ListBox to display.  Typing characters into the TextBox causes the ListBox to be loaded with all the records where the beginning of the field matches the characters so far.  Eventually enough characters will be typed to shorten the list to where the user can locate the record.  If the main field is not sorted, then the contents of the TextBox will be used as a search parameter to locate records.  The search text can include wild cards such as “*” and “?” and the search method can even be expanded to include values from multiple fields.

Picking the method of selecting a record often draws heavily on the experience of the designer, but must also consider the preferences of the client.  Searches are more powerful, but are more difficult to use (and to code!) and not likely to serve well an inexperienced client.

EXAMPLE

From the Lesson 3 sheet of the attached file  UserForms Lesson 3 2007.xlsx, click the “TabStrip and MultiPage Demos” button for an example. The ComboBox used for selecting a record is initialized with the first four records of the Lesson 2 Demo Database.  Clicking a record displays the fields for that record.  Clicking each Page shows the data of that Page, in this amazingly simply example is just one field.

Besides displaying records from a database, MultiPage can be used to behave in the same manner as any one of Excel's standard Wizards.  A Wizard by definition is an aid that steps through a particular process.  An example of this would be the Pivot Table Wizard or Chart Wizard.  A significant exercise implementing a wizard with MultiPage is included in Lesson 4.

Entering or Editing Records with MultiPage

Typically databases have three user functions: add, delete, and display/edit.  A useful variation of display/edit is to add a new record based on an old record.  To do this after the edit is complete, the user must be offered an add function as well as a save function.

Image

The image control allows display of image files.  All the common file types are supported, including JPG, BMP, and GIF.  The displayed file can’t be edited, but if the file doesn’t match the size of the image control, it has a variety of properties to control how the file is displayed.  These properties are AutoSize, PictureAlignment, and PictureSizeMode.

EXAMPLE

From the Lesson 3 sheet of the attached file  UserForms Lesson 3 2007.xlsx, click the “SpinButton and Image Demo” button for examples.  A SpinButton cycles through 3 sample files, and ComboBoxes allow effects of the properties to be investigated.

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.