Tip:
Highlight text to annotate it
X
In this lesson, you will learn how to create custom dialogue boxes, which are often called UserForms.
Most of the previous lessons have discussed concepts that are common to all computer languages.
All languages have variables, If structures, loops, and so on.
One advantage VBA has over many other languages is that it allows you to easily create graphical user interfaces with UserForms.
You can create UserForms in the Visual Basic Editor by clicking on Insert, then UserForm.
You also can create UserForms by right-clicking in the VBA Project Window, selecting Insert, and UserForm.
A blank UserForm named UserForm1 has appeared along with the Toolbox palette.
The properties of the UserForm appear in the Properties Window and an icon for the UserForm has appeared in the VBA Project
Window.
We can customize the UserForm by changing its properties.
This particular UserForm will be used to calculate the height of a ball that is thrown vertically from a building, so the UserForm’s
Name and Caption should reflect this.
Change the UserForm’s Name to “HeightCalc” and change the UserForm’s Caption to “Height Calculator”.
When we want to refer to the UserForm later on, we use its Name.
The name, which we call HeightCalc, cannot contain any spaces.
Notice that the Name of the UserForm has changed at the top of the Properties Window and in the VBA Project Window.
The name of the Caption is Height Calculator and this is what appears at the top of our UserForm.
Currently, our UserForm is blank.
We need to add areas where the user can input values for the initial height of the ball, the initial velocity of the ball, and the time since the ball
was thrown.
There also should be labels that tell the user where to input those three pieces of information.
After our program calculates the height of the ball, we also should have a location where we can display it to the user.
And finally, we will need two buttons that will allow the user to execute the program and quit the program.
We add all these items, which are called controls, to the UserForm using the Toolbox palette.
Since the last item I clicked on was the Properties Window, the Toolbox palette is temporarily hidden.
If I click on the UserForm, the Toolbox palette appears.
Some of the most common UserForm controls are: Labels, which are messages that help guide the user.
TextBoxes, which allow the user to input data and can be used to give output to the user.
CommandButtons, which are buttons that allow the user to execute set of statements every time they are clicked.
CheckBoxes, which allow the user to select or unselect an item.
OptionButtons and ComboBoxes, both allow the user to select one item out of many options.
When using OptionButtons, all possible options will be displayed on the UserForm at the same time.
For this reason, OptionButtons are more often used when only a few options exist.
A ComboBox is a drop down menu which lists the options when clicked on.
They are more often used when many options exist.
We first will create a TextBox that will allow the user to enter the initial height of the ball.
Let’s enlarge the UserForm, go to the Toolbox palette, select TextBox, go to the UserForm, and click-and-drag on the UserForm to the
desired size of the TextBox.
Next, we enter a descriptive name for the TextBox in the Name field of the Properties Window.
I will name the TextBox: InitHeight which stands for initial height.
We can adjust the position of the TextBox by clicking on it and moving it.
We should place a Label next to the TextBox to let the user know that the initial height of the ball should be placed in this particular TextBox.
Click on Label, go to the UserForm, and click-and-drag to create the Label.
You can change the Label’s text by clicking on the Label and typing in some new text.
The font is a little small, but you can change that by going to Font and clicking the button with "..."
I will select a size 22 font.
That font is a little big. Let’s change it 18.
We still need to create TextBoxes where the user can input the initial velocity and the time.
Also, we will need to create a TextBox that will display the height of the ball to the user.
We can quickly create new TextBoxes and Labels by selecting both the TextBox and Label we just created, copying by typing Ctrl + c,
then pasting by typing Ctrl+v.
Just like with the first TextBox we created, we need to give descriptive names to the three new TextBoxes.
I will call the second TextBox: InitVel which will store the initial velocity of the ball.
The next TextBox will be called: TimeElapse which will store the amount of time elapsed
since the ball was thrown.
And finally, the last TextBox will be named: HeightAns which will display that answer to the user about
how high the ball is at that time.
I also would like to will increase the font size of the TextBox so the values input by the user will be the same size as the Labels’ font size.
I also need to adjust the Labels.
The final controls that we need to create are two CommandButtons.
The first CommandButton will calculate the ball’s height and the second CommandButton will close the UserForm.
I will increase the size of my UserForm and select CommandButton from the ToolBox palette.
Drag out the size of the CommandButton you wish to create.
I will call this first CommandButton: RunButton
This will be the button that will be clicked when I want to calculate the height of the ball.
Change the text on the CommandButton and change the font size.
To create a new CommandButton, we can simply copy and paste, and drag the second button to its new location.
I will rename the second button: QuitButton
We need to write some VBA code that will be executed when the command buttons are clicked.
If you double-click on any control on the UserForm a window will appear with a blank Sub procedure for each control clicked on.
For example, let's click on RunButton, QuitButton, and the TextBox HeightAns.
Notice that three blank Sub procedures have been created.
In front of each of these Sub procedures is the term “Private”.
This limits a Sub procedure’s scope to the module where it is written.
In the past, we have only created “Public” Sub procedures which can be used in any other module in the VBA Project.
All Sub procedures by default are Public so we did not need to explicitly write “Public” when creating Sub procedures previously.
For now leave the word Private in front of the Sub procedure.
The RunButton_Click( ) Sub procedure is the code that will be executed when the Run button is clicked.
I have inserted some code that will calculate the height of the ball every time the Run button is clicked.
Four variables are declared.
h0, which is initial height of the ball.
v0, the initial velocity of the ball.
t, which is the time since the ball has been thrown.
g, the gravitational acceleration constant.
We obtain h0 by obtaining the value from the TextBox named InitHeight using: InitHeight.Value
We obtain the initial velocity input by the user in TextBox named InitVel using: InitVel.Value
and assigning that to v0.
We obtain time from the TextBox named TimeElapse using: TimeElapse.Value
and assigning that to t.
g is assigned -9.81.
Next, the program calculates the height of the ball.
In this example, we will assume that the ball does not bounce once it hits the ground.
The equation for the height of the ball is, h0 + v0 * t + 0.5 * g * t ^ 2
If this quantity is greater than zero, it will be assigned to the TextBox named HeightAns.
If this quantity is not greater than zero, that means the ball has hit the ground and the string “ball hit ground” will be assigned to the TextBox
HeightAns.
Whenever a new value is assigned to HeightAns, that is what will be displayed in the TextBox on the screen.
When the QuitButton is clicked, we want the UserForm, which is named HeightCalc, to disappear from the screen.
We can accomplish this task by using the Unload statement.
We do not want our program to do anything every time HeightAns is changed, so we remove this blank Sub procedure
Now that we have created the UserForm, we need a way to bring it into existence.
To do this, we create a macro that will Show the UserForm to the user.
Insert a new module.
Show the UserForm by typing: HeightCalc.Show
When the macro named ball is executed, the custom dialogue box that we created will appear.
Try plugging in some values for the initial height, initial velocity, and time elapsed, then click on the run button below.
Every time we click on the Run button, the height of the ball will be calculated.
After you are finished click on the Quit button and the custom dialogue box will disappear.
If you want the UserForm to automatically appear when you open the workbook, you can add an event handler.
Click on ThisWorkbook in the Project Window.
Choose the Workbook option from the drop-down menu.
The Workbook_Open( ) Sub procedure is executed every time the workbook is opened.
We want the HeightCalc UserForm to run when the workbook is opened, so type inside the Sub procedure Workbook_Open():
HeightCalc.Show
Save the workbook to the Desktop, and close the file.
Now when we open this workbook, the UserForm automatically pops up.