LESSON WORKBOOKS:
UserForms Lesson 4 2007.xlsx
The examples in this lesson are found in the attached workbook, UserForms Lesson 4 2007.xlsx on the Lesson 4 sheet and in the code module of UserForm_Lesson04.
Beginning the Layout Design
Sometimes the hardest task is dealing with the clean slate. Writers with a blank sheet of paper, painters with a blank canvas, and sculptors with a chunk of stone all face this problem. One way to deal with it is to begin with one simple task. For UserForms, that can be the Close button. Create a new UserForm. Open the Properties window and rename it. Add a CommandButton and rename it CloseButton. To the code pane and add the UserForm_Initialize and the CloseButton_Click procedures: Add the Option Explicit line at the top, which requires every variable to be declared. This will slow code writing but speed up code debug and test.
Option Explicit
Private Sub UserForm_Initialize()
End Sub
Private Sub CloseButton_Click()
Unload Me
End Sub
Click Run from the Standard or Debug Toolbar, and the UserForm will open. Click the Close button to unload the UserForm. Once basic functionality is established, the rest of the design can be implemented in stages, with interim checks that the UserForm still opens and closes.
First Stage – Rough Layout
Resize the UserForm to nearly fill the right pane. A UserForm larger than necessary, such as large controls or too much white space will require the user to make big movements to move from control to control. Also, if the user has multiple windows visible on the desktop, a large UserForm will cover up more of the screen, and more of the documents the user wants to have visible. On the other hand, it will be far easier to make it smaller and more compact later in the process than to enlarge it every time a control is added. Squeezing the design to include too many controls results in a cluttered look and requires small controls that are difficult to use.
Big Decision – How Many Pages?
This is where time spent gathering requirements and developing a vision for the operation pays big dividends.
The first decision required is to estimate whether all the needed controls will fit on a single UserForm, or whether MultiPage will be needed. The developer must always keep in mind the tradeoff between compactness and ease of use. If several ComboBoxes are serial, so that a selection in one affects the choices available in the next, then every effort should be made to cluster those ComboBoxes on one page, along with labels and any CommandButtons such as Accept, Clear, Save, or Go.
Functions that aren’t used as often, perhaps implemented with CheckBoxes and OptionButtons, can be placed on a separate sheet.
Place initialize buttons near the top or on the left, and Close, Cancel, OK, Submit, and Run buttons near the bottom or on the right..
Flow should be down a column rather than across in rows.
In general each UserForm should have one topic or purpose.
Using the toolbar, quickly pick and place each control. Make a modest effort to get the correct size, especially if the control will be copied and pasted multiple times. As
The Right Controls for the Right Task
Excel has a control for every task, and often two or more. There are no hard and fast rules for choosing which to use. For each task, one control may have an advantage over another, including ease of use for the user and ease of use for the developer. Controls are customizable. Size, border, font, and color are the most common custom changes, but the expert developer can add function to a control.
User input free text – may require validation of data
User input single choices
User input multiple choices
Display to user
Control grouping
Initiate actions
Whether to Use ComboBox or ListBox
Both controls display a list. The list may have multiple columns, and may have headers. A ListBox shows multiple rows, and the ComboBox only the selected row.
A ListBox can have multiple rows selected by setting the MultiSelect property, and the ListStyle property can display CheckBoxes to help show selections. If the ListBox does not display all the list rows, the additional rows are accessed with a vertical ScrollBar.
A ComboBox with no selection is blank. This is actually a built-in TextBox, in which the user may enter text if the Style property has the default value. If the ComboBox has more than one column, then the TextBox is connected to the column identified by the BoundColumn property. The ComboBox displays all the list rows when the drop-down on the right end of the control is clicked. If the number of rows to display exceeds the ListRows property, a ScrollBar appears. The drop-down appearance and function are controlled by the DropButtonStyle and ShowDropButtonWhen properties. When a row is selected from the list, the first column appears in the text box.
Use ComboBox if space on the UserForm is limited, or if user entry is allowed. Use ListBox if multiple selections are required, with the ListStyle property set to fmListStyleOption to make it easier to select scattered items.
Editing the Layout
The default is to edit one control at a time. For example a single control can be moved around on the UserForm layout simply by dragging and dropping it, or it can be resized by dragging one of the side or corner squares.
Grouping Controls
Often, several controls need the same edit. This can be done by selecting all the controls to edit. They need not be of the same type, as long as they all have the property to be edited. Select scattered controls by clicking any control, and pressing and holding the Ctrl key while clicking each of the other controls in turn.
A block of controls may be quickly selected by clicking a control on one edge of a group, then using the Shift key while clicking a control on the other edge of a group. Another multiple-select method is to left-click and hold on the UserForm outside of any control and dragging the cursor to some other point. A dotted-line rectangle will appear. When the mouse button is released, any control inside the rectangle or touched by it will be selected.
Once a block of controls is selected by either method, controls may be added to or removed from the selection by pressing the Ctrl key while clicking the individual control. A selected set of controls can be moved around the layout as a group.
The Format Menu
The Format menu at the top of VBE has a number of useful features to aid layout.
The programmer needs to investigate each menu item to see all the capabilities.
Align – aligns edges or centers of the selected controls. Align to Grid aligns the upper left corner of selected controls to the grid, which is the manual method for the Options setting Tools>Options>General>Align Controls to Grid
For any block of controls multi-selected by one of the techniques in the Grouping Controls section, Group ties the locations of the selected controls together with respect to each other. They can then be moved around the layout as a unit, until Ungrouped. A block of controls may be resized without grouping by dragging the edge of any of them, but the results may be unexpected. It is better to group them before resizing.
Order is handy if one control is to partially or completely cover another. If several selected controls are envisioned as a stack, the commands allow sending a control to the bottom or top of the stack, or up or down one position.
The other options are self-explanatory.
Tabbing
Users often navigate to the next control with the Tab key.
From the VBE select the UserForm, then the View Object view. Press F4 to bring up the Properties window. Select each control and set TabStop to False for those controls not in the Tab sequence and to True for those controls to be in the Tab sequence. Select the controls in the Tab sequence in order, beginning with the first one. Set TabIndex to 0 for the first control, and increment by 1 for the rest of the tabbed controls. The controls not in the Tab sequence will have TabIndex set to numbers greater than the last control in the sequence, but it won’t matter because TabStop is set to False.
EXERCISE
This exercise will create a UserForm, add several controls, rename and re-label many of them, then arrange the layout using the various tools. The code to make the controls functional will be provided.
Size the labels:
Size the ListBoxes:
Position labels and ListBoxes:
Size and position the buttons:
Swap the middle and bottom ListBoxes:
Establish Tab order:
Resolve a naming problem:
Add code and try it:
For comparison, click the “Initialization Demo” button. Form and function should be the same as the above exercise, though the look will be different because a different developer did the layout.
Starting and Stopping UserForm
There are two methods to start a UserForm and two methods to stop it. The difference is whether the UserForm is in memory or not.
Load will create the UserForm in memory but leave it hidden to the user. Show will unhide the UserForm if it is already loaded, and will also load it if it is not already in memory.
Unhide just hides the UserForm from the user, but leaves it in memory. Unload also removes it from memory.
The Initialization event only occurs with Load, whether by itself or as part of Show.
A UserForm that is in memory can be accessed by code even though it is hidden from the user. If it has been kept in memory by Hide, it will not be re-initialized by Show.
Initializing UserForm
The default behavior when a UserForm is started is for it to retain focus as long as it is open. Since Excel 2000, UserForms can be set to allow shifting focus to the Excel file or even to other windows on the desktop. This feature is enabled when the UserForm is started, by setting
The default behavior is initiated by
Sub StartUserForm ()
UserForm1.Show
End Sub
While the feature to allow changing focus is initiated by
Sub StartUserForm ()
UserForm1.Show vbModeless
End Sub
Initializing UserForm Controls
Generally all controls are initialized the same each time the UserForm is started. The most important property is the Value property, but any property which may change during the use of the control must also be initialized. For example, location, size, background color, font, and the Visible property are often manipulated during use. Each property is set to match the vision of the application as described in Lesson 1.
Except for ComboBox and ListBox, the Value property can be set either by setting the control Value property or by changing the ControlSource cell. There may be a situation where the UserForm needs to be initialized to the values of the previous usage. This can be done simply by not updating the ControlSource cells.
Control |
Initialize Property |
Code Statement |
Label |
Caption (Usually set at design time) |
(.Caption = "x") |
TextBox |
Value or Text (Blank or <comment telling what to enter>) |
.Value = "x", or .Text = ”x” |
ListBox |
List of selectable items |
see below |
ComboBox |
List of selectable items |
see below |
CheckBox |
Value (Typically all are unselected with False |
.Value = False or True |
Frame |
Caption (not initialized) |
(.Caption = "x") |
OptionButton |
Value (Select none or one item with True) |
.Value = False or True |
CommandButton |
Caption (not initialized) |
(.Caption = "x") |
ToggleButton |
Value (False for not pressed or True for pressed) |
.Value = False or True |
TabStrip |
Initialize each tab (select with Value) |
.Value = 0 (first tab) |
MultiPage |
Initialize each page (select with Index) |
.Index = 0 (first page) |
ScrollBar |
Min, Max, Value |
.Min = , .Max = .Value = |
SpinButton |
Min, Max, Value |
.Min = , .Max = .Value = |
Image |
Picture (Usually set at design time) |
(.Picture = LoadPicture(fullpath)) |
Initializing ComboBox and ListBox Lists
Initializing ComboBox and ListBox are more complex because of the multi-row list and the possibility of multiple columns. Initialization is needed both for the list, and what items in the list are selected, if any.
ListBox1.RowSource = "'Lesson 2'!C14:E20"
If ColumnHeads is set to True, then the list headings are taken from the row above the RowSource range.
Headings in combo boxes appear only when the list drops down.
For x% = 1 To 3
ListBox1.AddItem ("Item " & x%)
Next x%
The array can be populated by code, or can be drawn from a range of cells.
Dim ary ' ary must be data type Variant.
' Variables not explicitly typed are Variant by default
ary = Array("x", "y", "z")
' or
ary = Sheets("Lesson 2").Range("C14:E20")
LIST EXAMPLE
Dim ary as Variant
ary = Array("x", "y", "z")
ListBox1.List = ary
results in ListBox1 populated with a single row
x y z
COLUMN EXAMPLE
Dim ary as Variant
ary = Array("x", "y", "z")
ListBox1.Column = ary
results in ListBox1 populated with a single column
x
y
z
ListBox1.List(0, 1)
refers to the first row, second column, and
ListBox1.Column(0, 1)
refers to the first column, second row.
Initializing ComboBox and ListBox Selection
Where a single selection is allowed, the ListIndex property identifies a selected item. ListIndex begins with 0, and the maximum is Count – 1.
Where multiple selections are allowed, the Selected property of each item identifies all the selected items. ListIndex only identifies the item with the focus.
Formatting
Formatting a UserForm to look great is not that hard! This is a bit like those fantastic looking charts that really tell us nothing. The polishing of a UserForm to look nice can be time-consuming, so don’t waste time doing it before functionality has been verified.
Colors, borders, shadows, and other appearance variations should be used sparingly. Pick a standard for the project, and vary from it only for a purpose, such as highlighting problems. In general, default to common Excel practices. The client is accustomed to seeing a certain user interface, and the closer the design can be to the user’s experience, the easier and more intuitive it will be to use. For example, Excel menus don't present a multitude of colors. So start with plain grays and other defaults.
Initializing TabStrip and MultiPage
Since TabStrips and MultiPages have their own sets of controls, they can be thought of as mini UserForms embedded in a top-level UserForm. Each control on them must be initialized just it would have if it were on a UserForm.
Both these controls display records from a database. Populating occurs every time there is a change of record. All the code to populate a page or a tab must be in a self-contained procedure, which is called each time. Initialization is simply selecting the first page or tab and calling the populating procedure.
Examples of code to handle TabStrip and MultiPage are found in the code for UserForm_Lesson03b called by the TabStrip and MultiPage Demos button on the Lesson 3 sheet of UserForms Lesson 4 2007.xlsx
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.