|
|
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 arent 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.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.