Controls - Part 1

LESSON WORKBOOKS:  UserForms Lesson 2 2007.xlsx

 

This lesson will introduce the toolbox, controls and the common controls Label, CommandButton, ToggleButton, TextBox, ListBox, ComboBox, and Scrollbar.

Download the UserForm Control Demos in the attached file UserForms Lesson 2 2007.xlsx file for examples of using the various controls.

Open the file and select the Lesson 2 sheet.  It has three UserForms using the Label, CommandButton, ToggleButton, TextBox, ListBox, and ComboBox controls (but not ScrollBar).  Click each of the three buttons to see the three example UserForms, and try the different controls on each.

In these lessons, the notation used to show a series of selections from the menu system is Menu Item>Sub Menu Item.  Pressing and holding the Shift, Ctrl, and Alt keys is noted as <Shift>, <Ctrl>, and <Alt>, respectively, followed by another key, such as < Alt >F11.

Controls and the Toolbox

While these lessons address the UserForm, it is primarily a container.  The controls actually do most of the work.  The Control Toolbox within the Visual Basic Editor by default will hold only the 15 most commonly used controls.  Often these controls are sufficient for most projects. 

If you know how to open Visual Basic Editor (VBE) and create a UserForm, do so now, and skip this exercise.

EXERCISE

Open Excel.  Open VBE as follows:

For Excel 2003 (Excel 11.0): Tools>Macros>Visual Basic Editor.

For Excel 2007 (Excel 12.0), Developer tab > Visual Basic icon.  (To display the Developer tab, click the Microsoft Office Button  in the upper left corner of the Excel window, and then at the bottom of that window, click Excel Options.  In the left pane of the options window, click Popular, and then click the check box for “Show Developer tab in the Ribbon.”)

This first time is probably the only time the menus will be used to open VBE, since the shortcut is so handy.  For both Excel 2003 and Excel 2007, the shortcut <Alt>F11 opens VBE.

VBE is the same for both Excel 2003 and Excel 2007.

When the VBE window opens, if the left side doesn’t have the VBAProject pane, open it with View>Project Explorer.  Its shortcut is <Ctrl>R.

In the VBAProject pane, right click anywhere on VBAProject (Book1) or the list of sheets.  Right click>Insert>UserForm to create a new UserForm.  The design pane will open with a box titled UserForm1 and the Toolbox should also open.  If the Toolbox is not open, click UserForm1 and the Toolbox should pop up.  If not, View>Toolbox.

The UserForm, like any other object, has its own properties, which can be seen by right clicking on the form and selecting Properties from the pop-up menu.  There are two tabs in the Properties Window labeled "Alphabetic" and "Categorized.”  There is no difference between the two tabs except the order in which they are listed.  Notice that in the Properties Window there is a drop down box which will contain the names of all controls attached to the UserForm.  The first thing to do creating a new UserForm is to change its name from the default UserForm1 to a meaningful name.  Do this in the Properties Window. 

Another method for accessing the UserForms Properties is to double click it (to open the Private Module of the UserForm).  Simply type “Me” followed immediately by a period (full-stop), and Excel will automatically display all Properties for the UserForm.  To eliminate typing errors, always select properties from this list.

Toolbox can be resized and have its shape changed by dragging a corner.  This is a matter of personal taste, but I prefer to have it large enough to not have any scrollbars, and to be shaped more like a square than a strip.  Shape and size will be remembered from one session to the next.

The controls available to add to a UserForm are found on the Toolbox.  It contains a single page tab aptly called Controls.  It is here that we will see all the Visual Basic controls plus any ActiveX controls that may have been added.  When the toolbox has only the 15 default controls, and is resized to three rows of five controls, the controls will be arranged like this:  

Select Objects

Label

TextBox

ComboBox

ListBox

CheckBox

OptionButton

ToggleButton

Frame

CommandButton

TabStrip

MultiPage

ScrollBar

SpinButton

Image

These are certainly not the only Controls available to us.  We can see the complete list by right clicking on any Control and selecting "Additional Controls."  This list could have several hundred entries, including controls provided by non-Office programs.  However the controls mentioned above will no doubt provide us with more than enough flexibility for these lessons.

The first control (Select Objects) is not like any of the other controls as it cannot be placed (drawn) on the UserForm.  Its only purpose is to allow us to move or resize a control that has been placed on the UserForm. 

If you already know how to pick and place controls, skip this exercise.

EXERCISE

Click the lower right corner of UserForm1 and drag it down and to the right to fill most of the pane.

In turn, click each of the controls (except Select Object, of course).  When the cursor is moved over UserForm1, a small “+” appears.  A left click will create the control with a default size, with the upper left corner at the +.  If the left mouse button is pressed instead of clicked, the control may be immediately dragged to any size and in any direction, with the initial corner at the +.

Try creating several controls with default sizes, and also left press and drag to create with custom sizes.

After it is created, a control may be resized with the corner and side buttons.  It may be repositioned by clicking anywhere but the resize buttons and dragging. 

The default is for size and location to align to the background grid of dots, but additional control over size and location is available.  Click in the space between control and drag the outline to include more than one control.  When the left button is released, all enclosed controls will be selected.  Controls can be added to the selection with <Ctrl>left click.  The same may be used to remove a control from the group selection.  On one of the group selected controls right click>Align>Lefts.  Fortunately, <Ctrl>Z is available to undo group actions.

Right click>Make Same Size also is useful for quickly achieving a uniform look.

Place a control overlapping another control.  Try right click>Bring Forward and right click>Send Backward to control how the one overlaps the other.

Topics Common to ActiveX controls

All controls have a default property and a default event.  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

Once a control is created, the default event handler can be accessed simply by double-clicking it.  The Private Module will be brought up and the default event handler created.

All controls can be included in the sequence of controls selected in turn by the Tab key.  If TabStop is True, then the control is included.  The TabIndex specifies the sequence of the Tab enabled controls, beginning with 0.

All controls have location, sizing and display properties such as Left, Top, Height, Width, BorderColor, BorderStyle, and BackColor (background fill color).  All controls can have ControlTipText set, which is displayed during Run Time when the mouse is hovered over the control.  All controls can have Disabled set to False, where the user can see the control, but can’t click it., and can have Visible set to False, where the user can’t even see it.

Controls with a value can keep the information within the control, or link to a cell via the ControlSource property.  If ControlSource is set, then editing the displayed Value changes the cell’s contents and changing the cell contents changes the displayed Value.

Information kept only within a control is transient, and is lost when the UserForm is unloaded, while information linked to cells is retained until the next use of the UserForm, and if the file is saved after the usage, is retained until the next use of the file.  Transient data can also be lost in the case of an error, which may complicate error recovery.  Transient data will be retained if the UserForm is hidden with the Hide method, while it is lost if the Unload method is used.

If there will be multiple instances of a control, create the first one and copy it to repeat all the properties, such as size, caption, and 3D effects.  It is therefore most efficient to design the first one as completely as possible before copying.  When a control is copied, then pasted, the pasted instance will be placed centrally on the UserForm.  It is better to copy by dragging, so the placement of the new instance is handled at the same time.  To do this, press <Ctrl> while left-clicking and dragging.  Pressing both <Ctrl> and <Shift> while left-clicking and dragging will result in a placement that is aligned either horizontally or vertically with the first control.

Except for Frame, TabStrip, and Multipage, additional information is available from Microsoft Excel Help, as links from the help page for ActiveX Controls.  Additional help for the exceptions is found under TabStrip Control, MultiPage Control, and Frame Control.

Labels

Labels are the simplest control, only used for giving information to the user.  They are typically set up at design time.  The default property for a Label is the Caption property, and the default event is the Click event.  The caption can be changed by code but not by the user.  Label events are seldom used, since users are accustomed to read them but not interacting with them.

EXAMPLE

From the Lesson 2 sheet of the demo file, click the “Start Label and TextBox Demos” button for Label, TextBox, and CommandButton examples.  The caption texts explain themselves.

TextBoxes

TextBoxes are used to receive input from the user, or to allow the user to edit data.  Though it is called a TextBox, it is not limited to text.  While the default is to return text, it can just as easily return numeric values.  The validation of user input will be covered at length in Lesson 9.

Using VBE (Visual Basic Editor)

VBE Toolbars

EXERCISE

If you know already how to use the standard VBE toolbars, this exercise is optional.

Toolbars

View>Toolbars will open a menu that lists four toolbars:  Debug, Edit, Standard, and UserForm.  The first three should be checked.  Whether they are docked and where they are located is largely a matter of preference.  A frequently used configuration is for Standard to be docked at the top, just below the main VBE tool bar, and the Debug and Edit toolbars to be docked at the bottom, with Debug on the left.

There are many handy icons on each toolbar, but some frequently used ones are:

Standard: Save, Run Sub/UserForm, Reset

Debug: Run Macro, Reset, Toggle Breakpoint, Step Into, Step Over, Step Out

Edit: Toggle Breakpoint, Comment Block, Uncomment Block, Toggle Bookmark, Next Bookmark, Previous Bookmark

Locate each of these on their respective toolbars by pausing the cursor over each icon in turn and checking the control tip.  There are other ways to do most of these functions, including short cut keys and drop-down menu items.  Which method used is also a matter of preference.

For general knowledge, investigate all the icons.

VBE Windows and Panes

EXERCISE

If you know already how about VBE windows and panes, this exercise is optional.

Navigating between Object View and Code View

The left pane of the VBE window is VBAProject and the right pane is either the Code Pane or the Object Pane.  Right-click “UserForm1” in VBAProject and note the top two entries are View Code and View Object. These are for navigating between the two views.  Double-clicking “UserForm1” also will return from Code View to Object View.  Depending on the settings, there may also be a bar at the top of VBAProject which has icons for navigating to Code View and Object View.

View>Immediate Window.  This may also be docked or not.  A common docking spot is at the bottom of the right pane, below the Code View or Object View.  Debug.Print is the line of code that writes to the Immediate Window.

If F2 is accidentally pressed, the Object Browser will be displayed in the right pane.  It will probably be maximized in the right pane, and is best closed by clicking the small “x” just below the large “X” in the upper right corner of the VBE window.

TextBoxes (continued)

EXERCISE

Create a textbox named TextBox1

Double-click it.  The default Change event handler will be created in the View Code Pane.

Private Sub TextBox1_Change()

 

End Sub

Enter the two lines of code shown below, and then enter the DataValidationMacro and UserForm_Initialize procedures.  It may be typed, or copied to the clipboard and pasted.

Private Sub TextBox1_Change()

    Debug.Print TextBox1.Value         ’ writes to the Immediate Window

    Call DataValidationMacro

End Sub

Private Sub DataValidationMacro ()

    Debug.Print “DataValidationMacro”  ’ writes to the Immediate Window

End Sub

Private Sub UserForm_Initialize()

 

End Sub

Place the cursor in UserForm_Initialize and click one of the Run icons.  The UserForm will open.  

 

Besides the Enabled and Visible properties, TextBoxes can have the Locked property set, which prevents the user from editing the Value.

If the user will be restricted to a certain number of typed responses, other controls may be better choices, such as ListBox for text and SpinButton for numeric values.demo

EXAMPLE

From the Lesson 2 sheet of the file UserForms Lesson 2 2007.xlsx click the “Start Label and TextBox Demos” button for Label, TextBox, and CommandButton examples.  The caption texts explain themselves.

CommandButtons

CommandButtons are clicked by the user to initiate actions.  Like labels, they also are set up at design time.  Run, Go, Exit, Clear, and OK are typical button functions.

Sometimes it is expedient to have the event handler just call a macro located in a standard module.  This is easier to maintain if the same macro is to be called from different UserForms.

In the UserForm module:

Private Sub CommandButton1_Click()

    Debug.Print CommandButton1.Caption

    Call RunComplicatedMacro

End Sub

 

In a standard module:

Private Sub RunComplicatedMacro ()

    Debug.Print “ComplicatedMacro”

End Sub

ToggleButtons

A ToggleButton is a special case CommandButton, used to choose between two or three conditions and display which condition is active.  ToggleButton has a state associated with it, in the Value property.  The state is typically Boolean, but a TripleState property is also available.  If TripleState is True, then the three values of the Value Property are False, True, and Null.

The appearance of the ToggleButton changes when it is pressed, but code is typically used to change the Caption as well.  The UserForm_Initialize code sets Caption to “Done” to start the button with the correct text.

Private Sub ToggleButton1_Click()

    With ToggleButton1

        Debug.Print .Caption

        If .Value = False Then

            .Caption = "Done"

        Else

            .Caption = "In Process"

        End If

    End With

End Sub

 

Private Sub UserForm_Initialize()

    ToggleButton1.Caption = "Done"

End Sub

ListBox

A ListBox can display a specified number of rows of data without user intervention.  The MultiSelect property can be set so that a user can select more than one item at a time.  A user cannot directly enter any new values into the ListBox. 

EXAMPLE

Lesson 2 sheet of the file UserForms Lesson 2 2007.xlsx, click the “Start ListBox and ToggleButton Demos” button for ListBox and ToggleButton examples.

ListBox Demo

1.      ListBox is initialized with MultiSelect set to False.

2.      Click different entries to see only one record can be selected.

3.      Repeat with <Ctrl> pressed and see that selection behavior is unchanged.

4.      Repeat with <Shift> pressed and see that selection behavior is unchanged.

5.      In the selection mode ListBox, click “Multi select.”

6.      Repeat steps 2-4, noting that any combination of multiple entries can be selected and de-selected.

7.      In the selection mode ListBox, click “Extended select.”

8.      Click different entries and verify only one record can be selected.  Simple clicking has the same behavior as when MultiSelect is set to False.

9.      Click different entries with <Ctrl> pressed and see that selection behavior is the same as with MultiSelect set to True.

10.  Click the second entry, press <Shift>, and click the fifth entry.  See that it selects, along with all the entries in between.

ToggleButton Demo

11.  Click the “mode selection Enabled” button.  See that the caption changed to “mode selection Disabled”

12.  In the selection mode ListBox, click “Multi select.”  Note that the selection mode can’t be changed.

13.  Click the “mode selection Disabled” button.  See that the caption changed to “mode selection Enabled”

14.  In the selection mode ListBox, click “Multi select.”  Note that the selection mode changes.

Properties Unique to ListBox and ComboBox

ListBoxes and ComboBoxes can display multiple columns.  Properties ColumnCount and ColumnWidths control the display of the columns.  The RowSource property contains the range of cells to populate the list.  The list may be also be populated from an array and with the Add Item Method.  These will be covered later.

ComboBox

A ComboBox will only display one row of data at any one time unless the user selects the drop arrow on the right of the ComboBox.  The rows of data can then be between 1 and the total number of rows in the ComboBox.  The default is eight, and is set by the ListRows Property of the ComboBox.

A ComboBox is so called because it combines the features of two other controls, the TextBox and the ListBox. While it will allow a user to select an existing entry from a list of entries, as with a ListBox, it will also allow the user to enter a new entry, as with a TextBox.  MultiSelect is not available for the ListBox part of the control..

As with TextBox, the ControlSource property holds the cell address associated with the Value property.  If the list has multiple columns, ControlSource defaults to column 1, but may be changed by setting BoundColumn.  If the list has multiple columns, only column 1 is displayed. 

EXAMPLE

Lesson 2 sheet of the file UserForms Lesson 2 2007.xlsx has two ComboBox examples.  The first has headers on the drop-down list, and the second doesn’t.  The following will work for both examples.

1.      Click and drag the UserForm to the right to expose the Demo Database cells.

2.      Click the drop-down arrow of the unlabeled ComboBox.  Seven rows of data will be displayed, along with field headers.

3.      Select one of the records and click.  The fields will be displayed.

4.      Click Edit.  Change one of the fields.

5.      Click Cancel Edit.  The record will clear from the UserForm, and the record will be unchanged.

6.      Repeat Steps 3 and 4.  Click Save and see the changed field reflected in the list.  If “ComboBox Demo with headers” is running, then the change will also be in the cell contents.

7.      Enter something in each field (Wins field is not checked to be a number)

8.      Click Add New.  See the new record added to the list, and as above, in the cell contents.  If the cell contents changed, then the list will have been resorted.

9.      Repeat steps 7 and 8.

10.  Select the record added in step 8.

11.  Click Delete.  Verify in the list that the record is removed from Demo Database, and as above, in the cell contents.

Scrollbar

The ScrollBar is seldom used as an independent control, since both ListBoxes and ComboBoxes will automatically show horizontal and vertical scrollbars if needed to show the data they contain.

 

 

 

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.