Microsoft Forms

ISSN 1441-5585

Software Catalog

Microsoft Forms
Part III

Ken Getz and Mike Gilbert

This section attempts to introduce Microsoft Forms (the UserForm object), in a very quick manner. These forms, restricted at this point to modal use, allow you to request information from users and then take that information and use it in your application. We assume that you’ve used Visual Basic (or its equivalent) forms, and understand the concept of placing controls on forms, and writing code to react to the events generated by those controls (and the form itself) at run time. Below is a UserForm object in design mode.

A sample UserForm, and its associated toolbox.
A sample UserForm, and its associated toolbox.

UserForms can only be displayed modally. We said that already, right?

The Big Event
The key to working with Microsoft Forms (UserForm objects) is in understanding the events they "fire" as they’re working. For example, when a form loads, it runs the code attached to its Initialize event. When it closes, it runs the code attached to its Terminate event. In each case, the procedure "lives" in the VBA module attached to the form, and the procedure names are constructed from the class name of the object (UserForm), an underscore, and the name of the event (for example, UserForm_Initialize and UserForm_Terminate).

As an example, a form might initialize default values for controls in its Initialize event procedure, and save the new values to a database table in the Terminate event.

In order to study the available events, one simple solution is to open a form, add the control about which you’re interested (a MultiPage control, for example). Then, in the form’s module, choose the correct object in the left-hand combo box, then peruse the items found in the right-hand combo box in the module editor.

The illustration below shows where to look for the two combo boxes.

Use the combo boxes in the editor for perusing events.

The Options Are Limitless
Although the VBA control toolbox includes a number of useful controls, you are by no means limited to the controls you find there. There are literally thousands of ActiveX controls available in the market, many of which are free. You can use any of these on your forms

To add a new ActiveX control to your project, choose the Tools|Additional Controls menu item, and select the control from the list. You’ll see its icon added to the toolbox. From now on, you can use that control as if it had been included as part of the VBA environment. It has its own set of properties and methods, and most controls ship with a help file of some sort (to help you figure out those properties and methods).

Note: If you’re distributing solutions, you’ll need to also distribute any ActiveX controls you use with your solution. For what it’s worth, you may find the Setup Wizard that’s part of Microsoft’s Office Developer Edition (ODE) useful in creating the setup disks for your solution. It works great with Visio (and any VBA) solutions, and isn’t limited to Office 97 by any means.

Don’t forget that you can also use Visual Basic 5.0 Control Creation Edition (VB5CCE), which is available for free at http://www.microsoft.com/vbasic, to create your own ActiveX controls.

What Time is It? It’s Tool Time!
The VBA toolbox is a tiny little application all its own. Not only does it quietly contain all your built-in and ActiveX controls, it allows you a great deal of flexibility. What kinds of neat things can it do?

Add and Delete Pages

Rename Pages

Move Controls from Page to Page (to do this, drag the control to the tab for the new page, wait until VBA makes that page current, and then drop the control on the new page)

Change the image associated with any control

Import and Export control pages

The most interesting feature, however, is the ability to create control templates. That is, you can lay out groups of controls how you like them on a form, and then drag them back onto the toolbox, saving their properties and layout relationships. (This technique doesn’t save any code associated with the events of the controls, however).

Who Thinks of These Things, Anyway?
Take the time to peruse the properties, methods, and events of UserForms and you’ll find some items you won’t have seen in any other form-design environment from Microsoft.

For example, one of our favorites: the Zoom event and property. The Zoom property allows you to resize all the controls on the form (the value must be between 10 and 400). The Zoom event occurs when the Zoom property of the form changes. Therefore, imagine a form with a scrollbar, where changing the value of the scrollbar changes the zoom display of the form. Here’s some code to make that happen:

Option Explicit
Dim mintWidth As Integer
Dim mintHeight As Integer
Private Sub ScrollBar1_Change()
Me.Zoom = ScrollBar1.Value
End Sub
Private Sub UserForm_Initialize()
mintWidth = Me.Width
mintHeight = Me.Height
ScrollBar1.Value = 100
End Sub
Private Sub UserForm_Zoom(Percent As Integer)
Me.Width = mintWidth * (Percent / 100)
Me.Height = mintHeight * (Percent / 100)
End Sub

Note that changing the Zoom property only changes the controls, not the size of the form itself. Therefore, this example uses the Zoom event to resize the form at the same time. OK, not terribly useful, but fun for a demo!

Part IV

Ken Getz and Mike Gilbert

Also in this series:

VBA and Visio

Visio and the VBA IDE

Microsoft Forms

Advanced VBA

VBA Developer's Handbook by Ken Getz and Mike Gilbert

Rate this article...