Creating and running Forms

1
Creating and running Forms
2
Overview

• VBA in Excel and many other Windows applications
  can be used to create Forms
• Forms are Windows objects which can contain
  controls (buttons, edit objects, comboboxes,
  etc.)
• These objects can be used to allow user
  interaction with a macro program
• Three primary types of forms
• MsgBox
• Pre-defined form with a button(s)
• Generally has an OK/Cancel button
• InputBox
• Pre-defined form with a button(s) and one string
  input field
• UserForm
• General purpose with user defined controls

3
Creating a Message or Input Box

• Insert the following line in a macro for a
  message box
• result MsgBox("The prompt", vbOKCancel, "The
  title")
• Creates a message Box at that point in the
  program and waits for user to press button
• The prompt will be printed in the box
• The window title will be The title
• Result will be returned on exit from the line
• result is 1 if user presses OK
• result is 2 if user presses Cancel
• Other results and button types are available
  (Yes, NO, etc.)
• Insert the following line in a macro for an Input
  box
• r InputBox(" The prompt ", " The title ",
  "Default string")
• Exactly like MsgBox except result and Default
  string
• r is a string returned and is
• What the user typed into the input box
• If the user typed nothing, r is the default
  string
• If the user pressed cancel, r is an Empty string

4
Creating a user defined form

• Create a macro to run the form
• Create a user defined form by choosing
• INSERT/UserForm from the VBA menu
• Place controls on the form
• Drag controls from the Toolbox onto the form
• Set the properties of the controls
• Double click on objects that are to respond to
  user actions (events) and insert appropriate
  code for the action
• Put the following statement in the macro to open
  the form
• UserForm1.Show
• Put the following statement in the appropriate
  code to close the form (Usually the response to
  the OK or Cancel buttons)
• UserForm1.Hide

5
Example
Sub Macro1() ' ' Macro1 Macro ' Macro recorded
10/23/00 by Marvin Stone ' Example MsgBox and
InputBox functions ' Keyboard Shortcut Ctrlw '
Result MsgBox("Do you wish to enter data",
vbOKCancel, "Data Entry Query") If Result
1 Then st InputBox("Enter a string",
"The Enter a string box", "Nothing") Else
st "Nothing" End If If st "" Then
Result MsgBox("You Canceled! ", vbOKCancel,
"What?") Else Result MsgBox("You
entered " st, vbOKCancel, "Great!") End If
End Sub
6
Setting-up a UserForm
Macro to run the user form
Sub Macro2() ' ' Macro2 Macro ' Macro recorded
10/23/00 by Marvin Stone ' ' Keyboard Shortcut
Ctrlq ' UserForm1.Show (Must create
UserForm1) End Sub
Macro to close the user form
This subroutine is called by the event of the
user pressing CommandButton1 After UserForm1 is
created insert a button and double click on it.
This will insert the following routine. Add
the Hide method. Private Sub CommandButton1_Clic
k() UserForm1.Hide End Sub
7
UserForm Example
Module1 Code
UserForm1
Sub Macro2() UserForm1.Show End Sub
UserForm Code
Private Sub OKButton_Click() ActiveSheet.Range(R
efEdit1.Value).Select With Selection.Interior
.ColorIndex xlNone If RedButton
Then .ColorIndex 3 If BlueButton Then
.ColorIndex 5 If GreenButton Then
.ColorIndex 10 .Pattern xlSolid End
With UserForm1.Hide End Sub Private Sub
CancelButton_Click() UserForm1.Hide End Sub