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