user forms
Skip this Video
Download Presentation
User Forms

Loading in 2 Seconds...

play fullscreen
1 / 13

User Forms - PowerPoint PPT Presentation

  • Uploaded on

User Forms. 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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' User Forms' - althea-kelly

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
designing user forms
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.
available controls
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.
functionality of frequently used controls
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
adding controls to a user form and setting properties
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.
commonly used control name prefixes
Commonly Used Control Name Prefixes
  • CommandButton-cmd
  • Label-lbl
  • TextBox-txt
  • ListBox-lst
  • ComboBox-cbo
  • CheckBox-chk
  • OptionButton-opt
  • Frame-fra
  • Form-frm
points of interest
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.
writing event code for user forms
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.
writing event code for user forms1
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.
object browser and controls
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.
avearge userform code example
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
looping through the controls on a user form
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