| Developing dialog boxes that can customize
and extend the power of Windows applications has been the theme of the
recent series of VBA -Visual Basic for Applications tutorials. At the
same time we have tried to use components and coding that will be useful
in a strict VB-Visual Basic context as well.
In this tutorial we conclude the creation of the Message and Meeting Form which we are developing in Excel but
also shall install in Word, Corel Presentations and Project 2000 (the key applications used in our project). In
the last tutorial we developed the Message To tab/page which allowed us to choose who we wanted to send a note
or notice of meeting to using a standard list of colleagues. This tutorial will complete the Contents and Meeting
Options tabs/pages and then install the dialog in several Windows applications.
Figure 1 shows the layout of the new pages. The Contents page is actually quite easy to do. In Excel, start VBA
(Tools | Macro | Visual Basic Editor). Note that Excel may not have a common .DOT file like in Word so you may
have to load the Excel Worksheet
Figure 1 - The last two pages of the Message and Meeting Form
| containing your macros. Click on the Contents tab/page. Then add a textbox on the top of the page. Change
the textbox's font setting to 10pt and bold. Add a label and change its caption to Subject. Also change the label's
font to the same 10pt and bold settings. Now add a second textbox (rename it Body) and stretch it so it fits the
entire page but not covering the footer. Change the Multiline property to True and the ControlTipText to "Type
CTRL+Enter for new paragraphs". With this the visual layout of the Content page of the dialog is done.
So now we will program the Send button to dispatch messages with this Subject and Body as follows:
Dim appOL As Object, itemMail As Object
Dim sTo As String, i As Integer
' Code that creates an e-mail message
' using Microsoft Outlook
Set appOL = CreateObject("Outlook.Application")
Set itemMail = appOL.CreateItem(0) 'olMailItem = 0
For i = 0 To Lb2.ListCount - 1
sTo = sTo & _
Lb2.List(i) & "; "
.To = sTo
.Subject = SubjectText.Text
.Body = BodyText.Text
Note again if the Lb2 list is empty no messages will be sent. Also note that this is code is geared towards working
with Microsoft Outlook. By using the Windows API call ShellExecute one can change this subroutine to work with
other mail programs. Check the documentation on ShellExecute plus your own mail system on how it is invoked externally.
The Windows API ShellExecute allows for passing parameters including temporay files to invoked procedures. So now
all we have to do is setup the Meeting Options page.
The Meeting Options Tab/Page
This page uses a number of different controls but the first is to use a command button as a label. Put in a command
button on the top of the form in elongated form, change the Caption to "Meeting Equipment Options". Next
add five checkboxes just below the command button, changing the Captions to Whiteboard, Flipchart, VCR, Projector,
Podium. Now we can add the programming for the Meeting Equipment Option command button as follows:
If CheckBox1.Value And CheckBox2.Value And CheckBox3.Value And _
CheckBox4.Value And CheckBox5.Value Then
Rem if all boxes are checked the clear them
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
CheckBox5.Value = False
Rem otherwise check all the boxes
CheckBox1.Value = True
CheckBox2.Value = True
CheckBox3.Value = True
CheckBox4.Value = True
CheckBox5.Value = True
The code works as follows - only if all the buttons are checked then all the checkboxes are turned off; otherwise
all the checkboxes are turned on. next add a text box just below the checkboxes for any other equipment needed
for the meeting. In the next column we will add the handouts textbox and a spinner control right next to it as
in Figure 1. Set the spinners Max value to 10 and its Min value to 0.Also add a label with the Caption, No. of
handouts. The code for the spinner is quite easy:
Private Sub Spin1_Change()
Handouts.Text = CInt(Spin1.Value)
Private Sub Handouts_Change()
Dim ii As Integer
' note if Handouts is changed directly we must make sure
'a)its value is a numeric legal
'b)its value is within the allowed range
'c)and the spinner and handouts values are updated accordingly
If IsNumeric(Handouts.Text) Then
ii = CInt(Handouts.Text)
If ii < Spin1.Min Then ii = Spin1.Min
If ii > Spin1.Max Then ii = Spin1.Max
ii = 0
Spin1.Value = ii
Handouts.Text = CInt(ii)
As soon as the spinner value changes we update the Handouts.Text value. And whenever the Handouts value
is directly changed we must upate the spinner value - the two constantly remain in sync. So now all we have to
do is add the date picker control and the meeting place combobox. The date picker is not by default in the Toolbox
so we must load it using the Tools | Additional Controls menuitem(makesure the toolbox is active otherwise the
Additional Controls menuitem will be grayed out). Scroll down to the Microsoft date and Time Picker Control and
be sure to check the box before clicking okay. the icon for the date picker control now appears in the Toolbox.
Click and add it to the page, changing its name to Meetdate. Note the size of the mscomct2.ocx is 640KB. This is
a pretty hefty price to pay for getting one date; however given the number errors associated with date fields,
all you need to have is 3 or more date fields and the ease of use and fewer mistakes quickly pays for itself.
So now we need to update the Sub SendBtn_Click() for the new Meeting Options. Here we are using the following
rule - if the Meeting Place combobox is blank than no meeting data whatsover is added to the messages sent out.
Since you control the program you can change this behaviour to whatever you like. Perhaps checking if any changes
have been made to the meeting Options and if so prompting the user for a Meeting Place if it has been left blank.
here is the new code:
' Note: we make the choice of a meeting place
' the trigger that spawns all the meeting options and notes
optionst = ""
If Meetp.ListIndex >= 0 Then
optionst = IIf(CheckBox1.Value, "Whiteboard, ", "")
optionst = optionst & IIf(CheckBox2.Value, "Flipchart, ", "")
optionst = optionst & IIf(CheckBox3.Value, "VCR, ", "")
optionst = optionst & IIf(CheckBox4.Value, "Projector, ", "")
optionst = optionst & IIf(CheckBox5.Value, "Podium, ", "")
optionst = optionst & IIf(Len(Eqpmt.Text), Eqpmt.Text & ", ", "")
optionst = optionst & IIf(Spin1.Value > 0, "Handouts=" & Handouts.Text)
For i = 0 To Lb2.ListCount - 1
sTo = sTo & _
Lb2.List(i) & "; "
.To = sTo
.Subject = IIf(Meetp.ListIndex >= 0, "Meeting in " & Meetp.Text _
& "about ", "") & SubjectText.Text
.Body = IIf(Meetp.ListIndex >= 0, "Meeting date:" & _
Format(Meetdate, "MMMM dd, yyyy") & Chr(13), "") _
& IIf(Meetp.ListIndex >= 0, "Meeting place:" & _
Meetp.Text & Chr(13), "") _
& IIf(Meetp.ListIndex >= 0, optionst, "") & BodyText.Text
Note how the Subject and Body of the mail message have been changed to reflect the Meeting Options, if any.
Also note the use of the IIf() Immediate If function. Use of IIf() is a matter of taste - some people
think it makes the code clearer than using complex If-Then-ElseIf clauses. You be the judge and code accordingly.
So after testing we have a new Message and Meeting dialog that we can export to Corel Presentations, Microsoft
Project and Microsoft Word by just choosing File | Export File in VBA and saving the form with a unique name(I
used "MnM"). Then go into any of the programs and start up VBA and using File | Import File it is easy
to add the new command to really any VBA enabled application. In our very last tutorial on VBA, we shall look at
an alternative method which also allows adding a secure digital signature to our component. But for now we have
accomplished several things: 1) developed a multi-page component, 2)made it general so it can be used in just about
any VBA application, and 3) tried some new GUI controls in the process. Congratulations - we now are ready to tackle
our big assignment, making your own Windows Wizard.