Excel VBA: Layout and Startup Part 2. Excel VBA 2 Lesson 9

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

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:

5. Select one of the labels.  Press <Ctrl> and click to select the other two.
6. 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.
7. 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:

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

Position labels and ListBoxes:

10. Select all the CommandButtons and drag them to the space on the right side.
11. Select all the labels and ListBoxes.  Format>Vertical Spacing>Make Equal.
12. Using Format>Vertical Spacing> Increase and Format>Vertical Spacing>Decrease until the labels are a little above their ListBoxes.
13. Select the top label and ListBox.  Format>Group.  Repeat for the middle and bottom pairs.
14. Drag the middle and bottom groups to give some separation.  Format>Vertical Spacing>Make Equal.
15. Select each group and ungroup.

Size and position the buttons:

16. Format>Make Same Size>Both, then change the width to encompass the longest caption.
17. Drag the bottom ListBox down far enough to make room for a row of buttons.
18. Position the first button to the right of the upper right corner of the top ListBox.  Select both, then Format>Align>Tops.
19. Position the second button to the right of the lower right corner of the top ListBox.  Select both, then Format>Align>Bottoms.
20. Repeat steps 17 and 18 for the middle list box and the third and fourth buttons.
21. Drag the fifth and sixth buttons to the space between the middle and bottom ListBoxes, with a small separation between them.
22. 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
23. Repeat steps 18 and 19 for the bottom list box and the seventh and eighth buttons.
24. Select the Close button.  Format>Center in Form>Horizontally.

Swap the middle and bottom ListBoxes:

25. Select the top ListBox and its label and 2 buttons.  Format>Group.
26. Repeat for the middle ListBox and its label and 4 buttons and the bottom ListBox and its label and 2 buttons.
27. Drag the middle group down and the bottom group up.
28. Select all three groups.  Format>Align>Lefts.  Format>Vertical Spacing>Make Equal.  Format>Align>to Grid.  Select each group and Format>Ungroup.

Establish Tab order:

29. Select each label and confirm the TabStop property is False.  TabStop defaults to False for labels and to True for most other controls.
30. 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.
31. Set TabIndex for the middle ListBox and its buttons to 3, 4, and 5
32. Set TabIndex for the middle ListBox and its buttons to 6 through 10
33. Set TabIndex for the Close button to 11.

Resolve a naming problem:

34. 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:

35. 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.
36. In the UserFormLessons1thru5 standard module, locate Sub StartLayoutUserForm.  Uncomment the first line and comment the second line.
37. Save the file with File>SaveAs, and change the name, so the original file is preserved.
38. On sheet Lesson 4, click the Layout Demo button.
39. 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.

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.

2. 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%

3. 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

4. Initializing either by AddItem or by an array is in place of setting RowSource, so neither method will work with ColumnHeads.
5. 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

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

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