|
|
||
|
Menu Bar (Click
on the buttons below to discover VBA) |
VBA Part 23 of 25: Creating Userforms and Adding ControlsThe form or userForm is also known as a GUI (Graphical User Interface). The form is used to require values, parameters and information from the user to feed the VBA procedure. Twelve different controls can be added to the userform they are called: Label, TextBox, ComboBox, ListBox, CheckBox, OptionButton, ToggleButton, Frame, CommandButton, TabStrip, MultiPage, ScrollBar. Creating a userForm in VBA for ExcelCreating a form in VBA is quite easy. In the VBE (Visual Basic Editor ) you right click anywhere on your project in the VBAProject window and select "Insert/UserForm" and here is what you see:
In
the list of components of your project the userform name appears You
can close the toolbar by clicking on its "X" and call
it back by clicking on the toolbox icon on the toolbar Testing the Userform Anytime that
you want to see your userform (finished product
or work in progress) at work or when you want to test it click on the "Run"
button on the toolbar Tab Order The user can use the mouse to move from one control to the other but he should be able to move from one control to the other by entering a value in one and clicking "Enter" or "Tab". To make sure that the user moves from a control to the next one you need to set the tab order. To do so, right click on the form itself and select the "Tab Order" item. Follow the instructions. The first control in the list will be the one that is active (flashing cursor) when the form is activated. Bring the controls that are not to be used by the user (labels) at the end of the list. In step 5 see how to "tab deactivate" a control. The Userform Properties When you double click on the form's name in the VBAProject window of the VBE the form appears and the Properties window shows you the properties of the form. In the Properties window of the VBE you can change the name "(Name)" of the form, its caption (the name in the blue band at the top) and any of the 32 other properties of the form. When you name a form always use the prefix "frm" like in "frmDatabase" and be as descriptive as you can be so that your code will be easy to read. Always use one or more upper case letters in the name. When you write "frmdatabase.Show" in lower case letters Excel will capitalize some letters letting you know that the name is spelled correctly. There are 5 properties to help you set colors, font and border style. You can disable the corner "X" of the form with the property "Enable" set at "False". You can have the mouse pointer change when it is over the form with the "MousePointer" property. By default the userform appears in the center of the screen. If you want it to show somewhere else set the "Start" property to "0-Manual" and use the "Top" and "Left" properties to set a new position. Adding Controls The tool box offers you 15 controls. They are (starting with the most used): Label, TextBox, CommandButton, ComboBox, ListBox, CheckBox, OptionButton, ToggleButton, Frame, TabStrip, MultiPage, ScrollBar, Image and RefEdit. To add controls to the form you left click on whatever control that you want in the Toolbox , hold the button down and drag the control on the form. You can now expand the control to the desired size. Once all your controls are on the form you left click on them and in the properties window you can change the properties of the selected control. You can also right click on them and select "Properties". Managing Controls You can move the controls by clicking on them holding and moving them around. You can resize them by selecting them and using the different handles around them. You can copy or cut them by right clicking on them and choosing the right menu item. Once you have added your controls you might want to align a few of them or resize a few so that they are all the same size. To do so you first need to select many controls at the same time. To do so left click on the form near one of the controls that you want to select. Hold and drag drawing a frame that includes many controls. When you let go of the button all the controls that are touched by the frame are selected.
Right click on any of the controls and this contextual menu appears:
You can then align the controls or make them the same size. The Controls and their properties There are 12 different controls that can be added to the userform or to a regular worksheet. Here are the most important ones and their properties. When you select a control on the userform its properties are shown in the Properties window. The properties and the number of properties differ depending on the type of controls that you have selected. These properties can be changed in the Properties window of the VB editor or can be changed programmatically in a VBA procedure. Set the "Name"
property of all the controls that you will refer to in your VBA
procedures. I encourage you to use prefixes and some upper case
letters in these names (cbxCitx, txbCityName). Be as descriptive
as possible to make your code clearer. The prefixes that I use are:
command button (cmb), labels (lbl), combo boxes (cbx), text boxes
(txb), list boxes (lbx), check boxes (ckb), radio button (rdb),
toggle buttons (tbt), frames (fra), tab strips (tsp), multi pages
(mpg), scroll bars (scb), spin buttons (spb), images (img) and ref
edits (rfe). The controls that particularly need to be well named
are the controls which eventual values you will be using in your
procedures like the text boxes, the list boxes, the combo boxes,
the option buttons and the check boxes. For example: For most controls there are these general properties that allow you to set the font, the color of the font, the color of the background, the type of background, the type of border and other design features. The "Caption" property contains the text shown on a label, a command button, a check box, an option button, a frame, a tab strip or a multi page. Below are the other interesting properties that I use with different controls. Labels The label is a passive control meaning that the user never really acts on it. It is there to inform the user and to label other controls like text boxes, combo boxes or list boxes. The other interesting properties of the label are: - TabStop
to make the control invisible for the "Tab" and "Enter"
key (See Tab Order above). Command Buttons The command buttons are usually placed at the bottom of the form and serve to complete the transaction for which the form has been created. The caption of these buttons are usually "Go", "Run", "Submit", "Cancel". The other interesting properties of the command button are: - WordWrap
to be able to write more that one line on a button, Text Boxes The text box is the simplest control to require an entry from the user. The user types something in it and this value can then be used in your VBA procedure. The other interesting properties of the text boxes are: - WordWrap
to be able to write more that one line on a button, Frames, Frames are also a passive control. Frames are used to improve the layout of the userform. You can use them around a group of option buttons or check boxes or around a group of text boxes or combo boxes that have something in common. If you have two sets of 3 option buttons on a userform and you don't place them within a frame they all work together and you can choose only one in six. If you put each set within a frame you can choose one of three in each set.
Check Boxes and Option Buttons The check boxes and the option buttons are both used to offer the user a choice. The main difference between check boxes and option buttons is that if you have 5 of each on a form a user can check all 5 check boxes but can only select one of the option buttons. See the note above on frame and option buttons. If you don't want to use frame to create groups of option buttons you will need to use the "GroupName" property of the option buttons. All option buttons with the same GroupName work together. The other interesting properties of check boxes and option buttons are: - WordWrap
to be able to write more that one line in the caption, Combo Boxes and List Boxes The difference between a combo box and the list box is that the combo box is a drop-down list and the user can submit a single value either one of the values from the drop-down list or any other value. The list box shows a certain number of values with or without a scroll bar and the user can select on or more values but not a value that is not in the list. Combo
Box List
Box Combo Boxes The interesting properties of combo boxes are: - RowSource
The values that should appear in the drop-down list of the combo
box are submitted in the RowSource property. For example
Sheet1!A1:A12 will feed the list with the values residing in cells
A1 to A12 of the sheet with the Caption "Sheet1".
The rules to submit the RowSource property is the caption
of the sheet where the list resides followed by an exclamation mark
(!), the address of the first cell, a colon and the address if the
last cell. IMPORTANT NOTE: if there is a space or a special
character in the caption of the sheet where the list resides you
must surround it with simple quotes like in 'This sheet'!A1:A12. - ColumnCount
is the number of columns of values that you want shown in the drop-down
list. For example if you want to show part number and part name
in the list you will submit a RowSource like Sheet1!A1:B12 with
the part numbers in column A and the part names in column B Cascading Combo Boxes Here is a simple form requiring the user to select a type of wood for his hardwood flooring and then a color. All the colors are not available for all the types of wood. When the type of wood is selected in the first combo box the user should only be able to select a color that is available for the selected type of wood. These are cascading combo boxes. You set the list of values for the first combo box in the RowSource property as described above. For the list of values in the second combo box some programming is needed. With the proper code you can even avoid the "Selected" button. The procedure moves on as soon as the user has selected a color. You can also validate the input to make sure that the user has selected both a type of wood and a color. See the chapter on VBA code for userforms and controls. List Boxes The interesting properties of list boxes are: - RowSource
The values that should appear in the drop-down list of the combo
box are submitted in the RowSource property. For example
Sheet1!A1:A12 will feed the list with the values residing in cells
A1 to A12 of the sheet with the Caption "Sheet1".
The rules to submit the RowSource property is the caption
of the sheet where the list resides followed by an exclamation mark
(!), the address of the first cell, a colon and the address if the
last cell. IMPORTANT NOTE: if there is a space or a special
character in the caption of the sheet where the list resides you
must surround it with simple quotes like in 'This sheet'!A1:A12. - ColumnCount
is the number of columns of values that you want shown in the list
box. For example if you want to show part number and part name in
the list you will submit a RowSource like Sheet1!A1:B12 with the
part numbers in column A and the part names in column B Multi Pages The multi pages control is used to develop more elaborate userforms where the user can see a different set of controls on each of many pages. In the example below the user can choose a set of primary parameters on one page and a set of secondary parameters in the second page.
|
|
Menu Bar (Click
on the buttons below to discover VBA) |
|
|
||
.
.
Your userform will show with Excel in the background. To return
to the VB Editor just click on the "X" of the userform.
Before you do this make sure
that the userform is selected (not one of the controls).
