User Forms

1. User Forms

2. Designing User Forms • Open Excel > Alt+F11 puts you in the VBA Editor • Make sure the Project Explorer and Properties windows are visible. • Insert/UserForm menu will add a form. • A blank user form will appear. • The Toolbox should also appear. • If it disappears it can be redisplayed using the View/Toolbox menu item. • What you need to know: • 1. Which controls are available. • 2. How to place, resize, and line up controls on the form. • 3. How to give controls properties in the Properties window.

3. Available Controls • The available controls are displayed in the Toolbox. • The arrow at the top left is used only for pointing. • First row: Label, TextBox, ComboBox, ListBox • Second row: CheckBox, OptionButton, ToggleButton, Frame, CommandButton • Third row: TabStrip, MultiPage, ScrolBar, SpinButton, Image • Fourth row: RefEdit • These controls have certain behaviors built into them. • Example: if you have several radio buttons on the form, the user will only allowed to select one at time.

4. Functionality of Frequently Used Controls • CommandButton-used to run subprocedures • Label-used mainly for explanations and prompts • TextBox-used to let the user input information to be used in the macro • ListBox-used to let the user choose one or more items from a list or output results to the control • ComboBox-similar to a list box, except that the user can type an item that isn’t on the list in a box • CheckBox-lets the user check whether an option is desired or not (any or all can be selected) • OptionButton-lets the user check which of several options is desired (only one of a set of option buttons can be checked at a time) • Frame-usually used to group a related set of options buttons, but can be used to organize any set of controls into logical groups • RefEdit-similar to a TextBox control, but used specifically to lwt the user highlight a worksheet range

5. Adding Controls to a User Form and Setting Properties • Add a control to a user form, click on control in Toolbox and then drag a shape on the form. • Once the control is on the form, you can resize it and drag it to a different location. • Depending on the control, there is generally a label that is visible to the user and a name for the actual object. These two items are found in the properties window. • Color can also be added to the control using the properties window. • You change a property by clicking on the object and the properties window becomes visible.

6. Commonly Used Control Name Prefixes • CommandButton-cmd • Label-lbl • TextBox-txt • ListBox-lst • ComboBox-cbo • CheckBox-chk • OptionButton-opt • Frame-fra • Form-frm

7. Points of Interest • Tab order is a characteristic which allows you to control how the user can tab from one control to the next. • The tab order is controlled by the TabIndex property. • The TabStop property set to False will not allow a tab action on that control. • The Run Sub/UserForm button can be used to test the form. • Note: the InputBox and MsgBox can also be used.

8. Writing Event Code for User Forms • Much of Windows programming is built around events, • An event occurs whenever the user does something (generally a click event). • The events have built-in event handling. • You can add code to the sub which will execute desired actions. • These subs are always available if you want the program to react to certain events.

9. Writing Event Code for User Forms • Code to this point have been added by creating a subprocedure in the code window. • Event code is not placed in this area but is placed in a user form’s code window. • To get to a user form’s code window, make sure you are viewing the form’s design window. • Select the View/Code menu item. • In general, the View/Code and View/Object (F7 or Shift-F7) will toggle between the form’s design and its code window.

10. Object Browser and Controls • The control sub will have the following parts: control name, underscore, event type and it must contain any arguments that are given. • If you want to know what a particular control responds to, you can use the Object Browser. • Open the Object Browser and select the MSForms library. • The library provides help for all objects in the user form. • It provides a list of controls on the left and their properties, methods, and events on the right. • The events are designated by lightning icons. • By selecting any of these and clicking on the question mark button, you can get plenty of help.

11. Avearge UserForm Code Example • Private Sub cmdClear_Click() • lstOut.Clear ‘clear the list box • txtSize.Text = "" ‘clear the text box • txtSize.SetFocus ‘set mouse focus to text box • End Sub • Private Sub cmdAverage_Click() • Dim Input1 As Integer, x As Integer, Sum As Integer, Average As Double, Array1() As Integer, _ • Size As Integer, count As Integer • Size = txtSize.Text • count = 1 • ReDim Array1(Size) • For x = 1 To Size • Array1(x) = InputBox("Enter your data here:", "INPUT") • Sum = Sum + Array1(x) • Next • Average = Sum / Size • For x = 1 To Size • lstOut.AddItem ("The Input #" & count & " is " & Array1(x)) ‘adds items to the list box • count = count + 1 • Next • lstOut.AddItem ("The Average is " & Average) ‘add the message and value to list box • End Sub

12. Completed User Form

13. Looping Through the Controls on a User Form • Dim ctl As Control • For Each ctl In Me.Controls • If TypeName(ctl) = “TextBox” Then • If ctl.Value = “” Or Not IsDate(ctl) Then • MsgBox “Enter valid dates in the text boxes.”, _ • vbInformation, “Invalid entry” • ctl.SetFocus • Exit Sub • End If • End If • Next