In this article, we'll explore the Visual Basic Editor (VBE) and create a custom
dialog box. Creating your own dialog boxes, or User Forms as they are
officially called, can save you a lot of time when you need to acquire
information from users. Rather than displaying a series of input boxes to gather
information and then a series of message boxes to instruct the user, a custom
dialog box can do a lot of the work for you. And they're not as hard to create
and use as you might think.
Dialog Uses
Custom dialog boxes are not only useful for gathering information such as the
user's name or personal data. A user recently wanted to create a series of tables,
each table with a slightly varied format. He could have recorded one macro for
each type of table. But a more efficient way would be to create a custom dialog
box that gathers optional information. Then that information could be transferred
into the various format settings so that one table building macro could create
several different types of tables. We'll experiment with this idea in this article,
as well as explore the VBE in more detail.
Entering the VBE
Open Word and hit Alt/F11 to quickly enter the VB Editor. Take a look
at the Project window (which Rocky is pointing out, below). If you don't see
this window, hit Ctrl/R to bring it into view (or click View/ProjectExplorer).
The Normal.dot template is your default template and it's the default location
for macros. It holds any macros that you'd want available to you all the time.
However, some people get confused because they add a macro to a template, but
don't realize that the code is actually inside the Normal template. Then they
email the template to someone else to use and the macro won't work. This is
because you never sent the code! You must add a module to hold the macro code
into the project you are working on. Otherwise, it needs the Normal.dot to reference
the code. When we look at the project windows above, you can see that I have
a couple of forms attached to Normal, as well as several different code modules.
I've highlighted Module1 and to the right you can see that there are several
different macros showing in the code windows. These are all macros contained
within Module1. In my case, I have several modules because I write a lot of
macros for myself and other users. I use different modules to keep my code sorted
out. If you were building a project such as an AutoForm, you'd probably only
want one module to keep all the code together. NOTE: Recorded macros are automatically place in the NewMacros module.
I'm going to add a new form (Insert/UserForm) and a new module (Insert/Module) so I have a clean place to work with only the code that will be
used for this Custom Dialog project.
However, notice that when I added the new user form, I wasn't paying attention
and accidentally had the Normal project highlighted so the new form (CustomDialog01)
and new module (ModCustomDlg) were added in the Normal template. I could delete
them and re-add them to the correct project. But what if I've already done a
lot of work on them? So I'll export these out and reimport them into the correct
project. The form will have an .FRM extension. The module will be a .BAS
file.
Highlight the file you need to export and click File/Export and save
it to a folder. Then highlight the project where you want this file moved to
and click File/Import to add the file to the correct project.
Now look at the project files and you'll see that the new form and new module
are inserted into the proper project. I could now email this document out to
someone and the code and form would be contained within it. Well, that is if
I'd actually written any code. So let's get busy.
Building a Custom Dialog Box (User Form)
Click Insert/UserForm to insert a blank user form. This is what you'll
build into a dialog box. When you click on the user form, the Toolbox
will appear. The toolbox contains ActiveX controls that you'll insert into the
form to build your dialog box. These controls are the input boxes, drop downs,
command buttons, check boxes, and so forth, that you'd find on a dialog box.
Notice that Rocky is now checking out the form's Properties. The
properties are the items you can customize to make your form look the way you
want. Each control has it's own properties. When you click on a control, the
property box changes to display the properties for that control. There
are a few items in the property box for this user form on which you need to
concentrate. You may have noticed that the form was originally called CustomDialog01.
But there are certain naming conventions that you should learn to follow.
Such as txt for TextBox, ck for CheckBox, or opt for OptionButton.
You want to learn to code with consistency so that two years from now you'll
be able to read the code you wrote. By using the prefix frm to help identify
my form, I'll know that any code referencing this name is related to a user
form (dialog box). It may sound simplistic now, but after you've written a few
thousand lines of code, you'll learn to appreciate this type of consistency!
Note that I've clicked in the Name property box and now renamed the user
form to frmCustomTables.
Note, too, that the user form itself still has a caption entitled UserForm3.
Obviously, we'll want to rename the dialog box to something that better represents
its purpose. So I'll click in the Caption property and rename this dialog
box to display Custom Tables, since this is what the information captured
by this dialog box will be used to create. Although there are several other
property items you can customize here, and I do encourage you to experiment
with each property, remember that the user interface…the look of
the dialog box…will be most efficient if you keep the look consistent to
what users are used to seeing. So don't go nuts and create a custom dialog box
that looks like it just escaped from a circus! Look at the two dialog boxes
displayed below. Which would you prefer to use?
 
Adding Controls
We need to add controls to our dialog box so we can ask the necessary questions
and acquire the necessary information. We'll add text (labels) and input boxes
(text boxes) so the user will know where to type in information, as well as
some command buttons to activate the form. And just to demonstrate other controls,
I'll also add option buttons and check boxes.
To add controls to your form, you just click on the control you want and click
on the form to add it. A default sized control will be added to your form. From
there, you can click on the control and use the handles around the control (the
little black squares) to click and drag the control to adjust the size. If you
need to add multiple controls of the same type, you can double click the control
in the toolbox. This will keep the item selected so you can click multiple times
on the form to add several of the same control. This comes in handy when you
need to add a bunch of similar controls, such as options buttons.
Similar to a graphics program, there are also several design tools that you
can use to adjust the layout of your controls. I've decided to reorganize my
form (above) so that there's more room for more controls. To make sure the controls
line up properly, I click on one item and then hold down the Ctrl button
to click and capture more controls. I then click Format/Align/Bottoms to
have all the controls adjust and line up.
Notice that there are many other tools that you can use to help create a professional
looking dialog box…such as spacing, sizing and centering options. You can
also select several items and click Format/Group so you can more easily
work with many items as if it were one item. This can make moving or centering
items on a form easier. You can also add a Frame to bring attention to
certain items as a group. Experiment with the frame's Special Effects property
to see its different looks.
Thinking About the User
Now that all the controls are on the form, and the Name properties are
set to consistently identify each control with its proper naming convention…such
as optAlignRight for the right align option button and ckBorder for the border
remove checkbox…we're ready to think about how the user will use this form.
There are a few properties that we'll want to set to make the dialog box more
user friendly. Users who are accustomed to using the keyboard will appreciate
a hotkey so they can use the Alt + [character] shortcut to access
the command buttons rather than having to reach for the mouse. By entering a
letter in the Accelerator property of the control, you'll be setting
the hotkey command.
We'll also want to set the Tab Index sequence. This is the sequence
by which the controls will receive focus (will become active) when the
user uses the Tab key to cycle through the dialog box. Set the first
input box as Tab Stop # 1, the second input box as Tab Stop # 2, then number
each option and checkbox in similar order, finally setting the OK button
and then the Cancel button. This way the user can tab through the user
form in proper order.
Make Your Dialog Work!
Our user form design looks great! We've set all the Name properties so
we can recognize the controls. And we've set the Tab Index properties
so the users can quickly move through the dialog box. It's time to write some
VBA code so the dialog box will work!
One of the first lines of code you should write, especially if you're new to
writing VBA, is Option Explicit. This line goes into the General Declarations.
This tells the VB Editor not to let you get away with sloppy coding, such as
not declaring variables. This is important because it will help you catch typos.
If you declare a variable and later spell the variable wrong, this checking
feature will recognize that the variable you just typed hasn't been declared
because the words don't match due to the typo. That will force you to verify
it and you'll have a good opportunity to catch the misspelled word. If you don't
use this feature, you'll drive yourself nuts trying to figure out why your variables
aren't working! This is just one of the reasons why Option Explicit is valuable.
Let's deal with the easiest code first. Since the Cancel button simply
means…stop. Let's set a routine to handle a click event for that button.
VBA code reacts to actions the users make. So we'll want to enter code into
the Click Event for the Cancel button. Double click the Cancel
button on your user form. This will open the code windows for the form which
will handle the click event for this button. VBA will enter the first and last
line of the code for you. You'll enter your additional code between these two
lines. For this button, the code is simple. We just tell it to make the dialog
box go away and get out of the code!
Private Sub cmdCancel_Click()
frmCustomTables.Hide
Unload frmCustomTables
Exit Sub
End Sub
We also want to be sure to Unload the form from memory. Failure to remember to unload the form could interfer with further editing and code testing. This is handled by the Unload line in the code above.
With this code finished, you can now test your dialog to see how it looks. Hit the F5 key to run the form. Click the Cancel button to close the form.
Next we'll add code to the OK button's click event. Since all the control
values will be captured and the code to create and format the table will be
executed when this button is clicked, this will be the majority of our code.
If the form is not still visible, double click the form name in the left Project window to have the form display again. Then double click the OK button to open the code window for the OK button's
click event.
First you'll want to declare your variables to create virtual buckets which
will later contain the information we get from the user. By declaring the variables,
you set aside bytes of memory. There are different data types for different
types of data that you'll capture. This is another important reason to declare
variables properly. If you don't, tiny numbers can take up a large chunk of memory
because VBA will default all variable to a variant type, which is the
largest variable type using the most memory.
And one more advantage to properly declaring everything is to get the code's
IntelliSense feature (auto code complete) to become active, providing
you with hints about what the next part of the code should be. If you don't
make your object and variable declarations properly, this won't work and you'll be on
your own! Note below that when I type the period and start typing "txt", VBA knows
to call up my txt variables so I can pick the right one by hitting the Tab
or Return key to enter that info.
Help!
Don't know the correct data type to use? Humm…how about checking the VBA
Help files? The VBA help files are not installed by default. If you didn't previously
install them via the custom install option, you'll have to insert your Office
CD, click the Add/Remove button that will appear and navigate to the Word Help
file option to dig deeper into this category to select the VBA Help files.
Check that option to have it installed. NOTE: Don't uncheck any installed
items or they will be uninstalled!
Once VBA Help is installed, you can access it while in the VBE. And if you
position your cursor in any word you want to reference and hit the F1 Help key,
the text sensitive help files will go directly to that item so you can read
more about what it does and how to use it.
Now we'll tell VBA where to get the values for the variables. We do this by
setting the variables equal to the results of each control value. Read the code
below and you'll see that each variable is to become equal to the form's controls
values.
How it Works
When the user clicks the OK button on the form, each value from the form's controls
is transferred into it's respective variable. We can then add a table creation
code by using these variables and passing the value to the location where this
value is needed. Consider the code below. If you were to record a simple macro
that inserts a table that has, say, 3 rows and 3 columns, you would have code
as shown below.
But rather than using the NumRows:=3, we remove the number "3"
from our recorded macro and insert the variable name which holds the value
our user typed into our custom dialog box. Note the NumRows code for
our table below is now equal to the value of the vNumRows variable. And
as we set in the code before that, the vNumRows variable is equal to
the value that was typed into the frmCustomTables form in the txtNumRows
textbox!
So you can now create a custom dialog box which asks the user how many rows/columns
to have in a table. Then you can pass that value into the table insertion code,
and pass the value through the use of the variable.
Making Choices
Also in our dialog box, we asked the user to choose whether they want the paragraph
formatting to be LeftAligned, Centered or RightAligned.
Now we need to decipher what the user selected and make the appropriate formatting
via the code to whatever the user selected. In the code below, we make that
choice by using an If...Then statement to evaluate the value of each alignment
variable. Read the code below and you'll see how it checks for the True
value and then formats the columns for that alignment. Since we used Option
buttons, only one option can be selected, which means only one
choice can equal True.
Similar choices are made to decide whether to set a shadow border around the
table or whether to remove the border altogether.
And finally, we cycle through the columns and set the width to whatever value
was set and is now contained in the width choice variable. Note in the code
below that the .PreferredWidth is set to use the vColWidth variable value. And
as we now know, this variable holds the value of the width text box on our dialog
box.
Downloading this Project
We haven't spent as much time examining the table formatting code itself...as
we did building the custom dialog box, but then this article wasn't called Table
Code, it was called Creating Custom Dialog Boxes! Some future articles will concentrate
more on code. But assuming you might want to use this Custom Table code and/or experiment
with it, we've loaded it into a CustomDialog.frm file that can be downloaded
from the MouseTrax download page. Go to http://www.mousetrax.com/downloads.html.
Download the file. Then insert the .frm (dialog box form) in your Normal.dot
or a special template file by using the File/Insert command in the VBE, as described at the beginning of this article.
NOTE: Since all the code is contained under the OK and
Cancel buttons on the form, the code is contained right within the form.
We did not need to use a separate module for this project. So you'll only have
this one file for both the user form and the code. Future articles will
discuss modular code and you'll learn more about using separate modules
for calling reusable code.
Need more help getting your VBA code working right? Join our free VBA support
groups. See these links for details: http://groups.yahoo.com/group/Word_VBA/ and/or
http://groups.yahoo.com/group/ExcelVBA/.
|
