User forms
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