CSE 123
Introduction to Computing
Lecture 10
Dialogue boxes and User forms
SPRING 2012
Assist. Prof. A. Evren Tugtas
Communicating with the User
User forms, message boxes, and input boxes are used to
communicate with the user.
There are five ways to communicate with the user in VBA
Displaying messages on the status bar at the bottom of the
window bit limited but effective
Displaying message box
Displaying an input box
Displating a user box
Communicating directly through the applications interface
Displaying Status Bar in Excel
You can prevent user to think that the procedure is frozen
or chrashed by displaying a message on the status bar.
Displaying Status Bar in Excel
Sub bar()
[Link] = "Program is still running,_
please be patient....."
End Sub
Displaying Status Bar in Excel
Progress indicators can be written in various ways
[Link]=Program is calculating the
grade of 9th student
.......10th student
.......11th student etc.
You need to use a loop to display the progress
Message Box
Basics of Message box use was covered in earlier
lectures
To break string into more than one line;
vbCr or Chr(13)
vbLf or Chr(10)
vbCrLf
vbnew line
comments can be used
6
Message Box
Sub message()
MsgBox ("This is an example of" & vbCr & vbCr & "how you can_
open a new line in your" & vbNewLine &_ "message box")
MsgBox ("Another example of " & vbLf & "creating a new line using_
vbLf")
MsgBox ("Another example of " & vbCrLf & "creating new line using_
vbCrLf")
End Sub
Message Box
Add a tab to a string
vbTab or Chr(9)
To add bullets
Chr(149)
Message Box
Sub message()
MsgBox ("If you want to add a tab" & vbTab & "to a string,_
you need to use" & vbTab & "vbTab character")
End Sub
Message Box
10
Message Box
MsgBox ("This program; " & vbCr & vbCr & Chr(149) & _
calculates the roots of the linear equation, which specified by the
user_ & vbCr & vbCr & Chr(149) & _
prompts appropriate messages to guide the user" & vbCr & vbCr & _
Chr(149) & prints the results to cells A1 to D20")
11
Message Box Buttons Argument
Ref: Mansfield R. Mastering VBA for Microsoft Office 2007.
Wilwy Publishing. 2008
12
Message Box Icons
Ref: Mansfield R. Mastering VBA for Microsoft Office 2007.
Wilwy Publishing. 2008
13
Message Box Icons
Sub Message2()
a = MsgBox("Do you want to exit ?", vbQuestion + vbYesNo)
.....
b = MsgBox("This action will stop the program", vbCritical)
End Sub
14
Setting a Default Button for a Message Box
If the procedure can destroy someones work if
they run it inadvertently then it would be a good
idea to set a default button of No or Cancel
Ref: Mansfield R. Mastering VBA for Microsoft Office 2007.
Wilwy Publishing. 2008
15
Setting a Default Button for a Message Box
y = MsgBox("Do you want to erase all the data ?", _
vbYesNo + vbCritical + vbDefaultButton2)
16
Adding Help Button to your Messagebox
vbMsgBoxHelpButton constant is used
y = MsgBox("Do you want to erase all the data ?", vbYesNo + _
vbCritical + vbDefaultButton2 + vbMsgBoxHelpButton)
You need to specify the help file as well
y = MsgBox("Do you want to erase all the data ?", vbYesNo + _
vbCritical + vbDefaultButton2 + _
vbMsgBoxHelpButton,c:\Windows\Help\My_Help.chm )
17
InputBox
Basics have been covered
y = InputBox("please enter your name")
18
Dialogue Boxes
Most of the time message boxes and/or input
boxes will not be enough, becuase
You can only use limited amount of buttons
You can present only certain amount of information
Custom dialogue boxes are created instead
19
Dialogue Boxes
User forms are used to implement dialogue boxes
A user form is a blank sheet on which you can
place controls (buttons, check boxes etc.)
A code is attached to the controls in the form
Each user form is an object and contains number
of other objects that you can manipulate
20
Inserting a User Form
Open the Visual Basic Editor
Insert UserForm
21
User Forms
Grid Settings: ToolsOptionsGeneral
22
Renaming User Form
Default name for the first user
name you have created is
UserForm1
Properties window
You can change the name of
the user form, color, font etc.
from the properties window.
23
Adding Controls to the User Form
View Toolbox
24
Adding Controls to the User Form
Ref: Mansfield R. Mastering VBA for Microsoft Office 2007.
Wilwy Publishing. 2008
25
User Forms
26
User Forms
Double clicking to form opens the code window
27
Private Sub OptionButton1_Click()
Dim t(1000), y(1000), Ka(1000)
......
......
End Sub
28
User Form
Private Sub well_Click()
k = Val([Link])
rad = Val([Link])
H = Val([Link])
m = Val([Link])
[Link]
......
End Sub
29