Layout and Startup

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.

  1. In the VBAProject pane, right-click on UserForms Lesson 4 2007, select Create>UserForm, then enlarge it to mostly fill the pane.
  2. If the Properties box is not open, press F4.  Rename to UserForm_Lesson04a.  (Save typing by selecting the name in the previous sentence, copying it with either right-click>Copy or <Ctrl>c, then selecting name in the Properties box and pasting with right-click>Paste or <Ctrl>v.  The programmer can use whichever menu is found most convenient.)  Changes in the Properties box are finalized with <Tab> or <Enter>.
  3. If Toolbox is not open, open with View>Toolbox
  4. Starting at the top, without paying too much attention to size or location, create:
    1. A label.  Click to select the control, then click again to edit the Caption text.  Change the text to “ListBox AddItem and DeleteItem.”
    2. A ListBox.
    3. A CommandButton.  In the Properties box, change Name to “AddButton” and Caption to “Add Item.”
    4. A CommandButton.  Change Name to “DeleteButton” and Caption to “Delete Item.”
    5. A label.  In the Properties box, change Caption to “Init ListBox List from array.”
    6. A ListBox.  Set ColumnCount to 2
    7. A CommandButton.  Change Name to “ToggleRow3Button” and Caption to “Toggle Row 3.”
    8. A CommandButton.  Change Name to “ToggleRow2SelectButton” and Caption to “Select Column 3.”
    9. A CommandButton.  Change Name to “Select Row 3” and Caption to “Select Row 3.”
    10. A CommandButton.  Change Name to “Rows1and4SelectButton” and Caption to “Select Rows 1 & 4.”
    11. A ListBox.  Change ColumnCount to 4
    12. A CommandButton.  Change Name to “ToggleColumn3Button” and Caption to “Toggle Column 3.”
    13. A CommandButton.  Change Name to “ToggleRow1SelectButton” and Caption to “Select Row 1.”
    14. A label.  Change Caption to “Init ListBox Column from array.”
    15. A CommandButton.  Change Name to “CloseButton” and Caption to “Close”

 

Size the labels:

  1. Select one of the labels.  Press <Ctrl> and click to select the other two.
  2. Format>Make Same Size>Both.  This command is also available on the right-click menu, along with Align, Group, and Ungroup.  The programmer can use whichever menu is found most convenient.
  3. Using the resizing handles, change the width to encompass the longest caption, and change the height to barely but completely expose the caption text.

 

Size the ListBoxes:

  1. Select the three ListBoxes.
  2. Format>Make Same Size>Both, then change the width to leave some space on the right side.

 

Position labels and ListBoxes:

  1. Select all the CommandButtons and drag them to the space on the right side.
  2. Select all the labels and ListBoxes.  Format>Vertical Spacing>Make Equal.
  3. Using Format>Vertical Spacing> Increase and Format>Vertical Spacing>Decrease until the labels are a little above their ListBoxes.
  4. Select the top label and ListBox.  Format>Group.  Repeat for the middle and bottom pairs.
  5. Drag the middle and bottom groups to give some separation.  Format>Vertical Spacing>Make Equal.
  6. Select each group and ungroup.

 

Size and position the buttons:

  1. Format>Make Same Size>Both, then change the width to encompass the longest caption.
  2. Drag the bottom ListBox down far enough to make room for a row of buttons.
  3. Position the first button to the right of the upper right corner of the top ListBox.  Select both, then Format>Align>Tops.
  4. Position the second button to the right of the lower right corner of the top ListBox.  Select both, then Format>Align>Bottoms.
  5. Repeat steps 17 and 18 for the middle list box and the third and fourth buttons.
  6. Drag the fifth and sixth buttons to the space between the middle and bottom ListBoxes, with a small separation between them.
  7. Select the fifth and sixth buttons.  Format>Group.  Add the middle TextBox to the selection.  Format>Align>Centers.  Select just the group and Format>Ungroup
  8. Repeat steps 18 and 19 for the bottom list box and the seventh and eighth buttons.
  9. Select the Close button.  Format>Center in Form>Horizontally.

 

Swap the middle and bottom ListBoxes:

  1. Select the top ListBox and its label and 2 buttons.  Format>Group.
  2. Repeat for the middle ListBox and its label and 4 buttons and the bottom ListBox and its label and 2 buttons.
  3. Drag the middle group down and the bottom group up.
  4. Select all three groups.  Format>Align>LeftsFormat>Vertical Spacing>Make EqualFormat>Align>to Grid.  Select each group and Format>Ungroup.

 

Establish Tab order:

  1. Select each label and confirm the TabStop property is False.  TabStop defaults to False for labels and to True for most other controls.
  2. Select the top ListBox and set TabIndex to 0.  Set TabIndex for its two buttons to 1 and 2, and verify the TabStop property is True.
  3. Set TabIndex for the middle ListBox and its buttons to 3, 4, and 5
  4. Set TabIndex for the middle ListBox and its buttons to 6 through 10
  5. Set TabIndex for the Close button to 11.

 

Resolve a naming problem:

  1. Select the middle ListBox.  Change Name to ListBox2.  “Could not set the Name property.  Ambiguous name.”  Change Name to ListBoxX.  Select the bottom ListBox and change the name to ListBox3.  Now select middle ListBox and change Name to ListBox2.  The purpose of this little demonstration is to show what to do when a renaming exercise fails.  Also, it is necessary to match the code that will be provided.

 

Add code and try it:

  1. Select the code module for UserForm_Lesson04b by right-clicking it in the VBAProject pane and clicking View Code.  <Ctrl>a to select all the code and <Ctrl>c to copy it.  Select the code module for UserForm_Lesson04a, and <Ctrl>v to paste all the code.
  2. In the UserFormLessons1thru5 standard module, locate Sub StartLayoutUserForm.  Uncomment the first line and comment the second line.
  3. Save the file with File>SaveAs, and change the name, so the original file is preserved.
  4. On sheet Lesson 4, click the Layout Demo button.
  5. There should be no errors.  If there are, the error messages may help resolve the problem.  Otherwise debugging code will be covered in Lesson 7.  Errors occurring during UserForm initialization will leave the code stopped at the calling routine.

 

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.

  1. The simplest list initialization is if the list is in cells.  Simply set RowSource to the range.  To be safe, qualify the range address by preceding it with the sheet name and a “!” separator character.  If the sheet name contains a space, put apostrophes around it.

    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.

  1. Lists can be initialized by code by successive use of the AddItem method.  AddItem is only for single column lists.  A loop can used to enter sequential items such as Week 1, Week 2, etc.  If the entries are more complex, then RowSource or using an array as described next is a better choice.  Programming to populate the list with AddItem can be tedious, and prone to error.  This method should be the last choice, and only used for very simple cases.

    For x% = 1 To 3

        ListBox1.AddItem ("Item " & x%)

    Next x%

  1. Lists can also be initialized by code by setting either the List property or the Column property equal to an array.  Setting the List property from an array results in the expected rows and columns.  The list displayed in the ListBox will be the same as if the array was put to cells.  The Column property transposes the array, interchanging rows and columns.

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

  1. Initializing either by AddItem or by an array is in place of setting RowSource, so neither method will work with ColumnHeads.
  2. Individual items in the ListBox can be read or written using the List and Column properties with two integer indices.  The integers begin with 0 so

    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.