Visual Basic 6 Textbox Properties
|VB Tutorial 12 - VBA III
Our last tutorial provided an overview of the importance of VBA as Windows macro language. In addition a VBA dialog
was developed for initializing headers and footers within Word. In the process of developing our own Word command,
it became evident that:
a)VBA developers are working with a subset of all VB features;
b)there are tricks to getting your VBA dialogs to run from a toolbar or menus;
c)each application (AutoCAD, Excel, Word, Visio, etc) has its own distinct commands best learned from recording
macros and the Object Browser.
So in this session we return to creating another VBA command dialog. But it will be done in a way so that the code
can be exported to other VBA enabled programs and even to VB itself. Finally we will use a very useful Windows
GUI feature - tabbed forms in our dialog.
VBA Textbox Properties
|But let us return to the first point for a moment - VBA is a subset
of VB. In fact, VBA is only a somewhat close subset of Visual Basic. Look at Figures 1A and 1B. 1A shows the Property Sheet for a text box in Visual Basic 6.
Note the differences with the textbox properties shown in Figure 1B for VBA in Office 2000 Excel. First, VB6's
Property Sheet has a descriptor for each property highlighted at the bottom of the sheet; VBA does not. Second,
VB6 has such textbox properties as Alignment, Appearance, DragMode, DataField, DataFormat, DataMember, Index, LinkItem,
LinkMode, LinkTimeout, LinkTopic, and several others which VBA does not have. Likewise, VBA has textbox properties
such as Autosize, AutoTab, AutoWordSelect, BackStyle, BorderColor, ControlSource, ControlTipText, DragBehavior,
EnterField Behavior and several others that are distinct to VBA. Now readers will rightly point out that some of
these properties overlap and that is true; however I have yet to find a Microsoft source that reconciles the differences.
So the bottom line on compatibility between VB and VBA is the following:
1)for the common controls, their properties, methods, and events are fairly close but by no means identically the
2)for the command syntax (For..Next; Select Case, Format(), etc) VBA is a direct subset of VB;
3)for COM objects, classes and the hierarchy of object collections every version of VBA has its unique hierarchy
- so to an extent one must learn Visio VBA as distinct from Excel VBA;
4)but you can import VBA forms into VB6 at the price of a larger runtime;
5)most VB6 forms cannot be imported into VBA; but VB6 COM components can be imported into VBA projects.
This close-but-not-quite compatibility means that users and developers will have to invest extra time in two ways:
first, users will have to learn more to master VBA even given strong knowledge of VB6; depending on the application
(Excel, Word, Visio, or others) between 20-50% more. Second, developers will have to spend 10-15% of their time
resolving runtime differences between VB and VBA when sharing forms and modules. In summary, all one can say is
that close and good enough have been winning strategies for Microsoft for quite some time and VBA appears to be
the defining case.
|Figure 2 shows an Excel VBA editor working on our Message and Meeting Form. This will be a useful exercise because
the tab control used in this form work in a very similar fashion in VB6 as well. In effect, a tab or multi-page
control (as in VBA lingo) saves screen space by fitting two or more forms into one dialog box. In our Message and
Meeting dialog we will build three forms - the "Message To" tab/page in which an Excel user can choose
which colleagues to send a message to. Then in the next tutorial we will complete the project by adding the "Contents"
tab/page which allows the subject and body of the message to be filled out plus a third tab/page, "Meeting
Options", which notifies team members and support staff of any meeting needs.
In VB Tutorial 10 , we used two comboxes to transfer field name from in the DynaCube to out and back. In a
Figure 2 - VBA Form using Tabbed Pages
|similar fashion we use two lisboxes to transfer "Group Members" to the "Message To" list (just
off screen in Figure 2). However, in our form we are using command buttons(Add>, All>>, <Remove, <<All)
to move items from one list to another. This type of list manipulation occurs quite frequently in GUI interface
programs - so we shall spend some extra time describing the programming of this tab/page of our 3 part Message
and Meeting form.
Start up Excel (97 or 2000 or any other VBA enabled app if you don't have Excel) and then choose Tools | Macros
| Visual Basic Editor. When VBA comes up choose Insert | UserForm and a blank form will appear on screen along
with the Toolbox. From the ToolBox select the MultiPage control (3 row, second icon not the TabStrip) and draw
it so it fills 4/5ths of the form. Two tabs labeled Page1 and Page2 will appear. Click on Page1 and change its
Caption to Message To. Click on Page2 and change its Caption to Contents. Right mouse click either of the tabs
and select New Page from the pop up menu. Change the Caption from Page3 to Meeting Options - note that the tab
automatically widens to fit the label in. We have now created our basic multi-page form.
Next click on the Message To tab and add two listboxes of equal size on opposite sides of the page - change their
Name in the Property Sheet to Lb1 and Lb2 respectively. Add four command buttons between the two listboxes (as
shown in Figure 2). Remember to change their Captions to Add>, All>>, <Remove, <<All. If you
want, change the names of the command buttons to something more meaningful - I used Badd, Baddall, Brem, and Bremall.
Now add two more command buttons at the bottom strip and to the right (make sure the command buttons are not on
the multi-page part of the form. Change the command buttons Captions to Send and Cancel respectively. Double click
on the Cancel button and when the source code editor comes up add the following line of code:
Do the same for the Send Button. Later, we will add successively much more code to this Sub Send_Click()
procedure. Now we will add the snippets of code to move names to and from the Group members-Lb1 list box and the
Message To-Lb2 listbox. So double click on the Add> command button and insert the following code::
Private Sub Badd_Click()
If Lb1.ListIndex >= 0 Then
Note we check first that the list is not empty then we add the item to Lb2 list box(Message To list) before
removing the item from the Group Members list Lb1. For the All>> command button the code is as follows:
Private Sub Baddall_Click()
Do While Lb1.ListCount > 0
Note that the Do While loop will not start if the Lb1 listbox is empty; which is exatly what we want.
The loop transfers all the Group Members starting at the top over to Lb2, the Message To listbox. As you might
guess the <Rem and <<All code is quite similar. Download the vbtut12.zip file from my website
at www.inforamp.net/~jbsurv to see the details plus some additional features. Finally we have to add some Group
Member names to Lb1 with the following code:
Private Sub UserForm_Initialize()
Note the With clause saves having to type Lb1 before each .AddItem. Now to check out the dialog
box, save the results (File | Save) and then place the cursor in any one of the UserForm Subs and press the F5
key or click Run | Run Sub/UserForm. Highlight a name in the Group Member list and then press the Add> button.
Try the other buttons. If the program does not work, use the debug features of VBA by setting a breakpoint in the
subroutine that is not working. make sure the debug toolbar is available - right mouse click anywhere on the standard
toolbar and choose Debug from the popup menu. To start debugging click on Debug | Step Into. With this we have
finished the first part of our UserForm. In the next tutorial we will complete the Contents and Meeting Options
pages. For more reading on VBA, there are some excellent sites on the Web including www.zdnet.com, www.techweb.com,
and www.idgnet.com (punch VBA into Search engines).
Jacques Surveyer is a consultant; let me know at firstname.lastname@example.org your favorite VB and VBA websites and I will
publish a list of the best in an upcoming tutorial .