1 / 13

User Forms

Learn how to design user forms in Excel using VBA. Understand control types, properties, and event handling. Add controls to forms and set properties.

dseibold
Download Presentation

User Forms

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related