Extending The Capabilities Of VBA UserForms With Windows API Functions
This page describes techniques you can use to extend the functionality or appearance
of VBA UserForms that are not possibe with the standard properties of UserForms.
Compared to their cousins in Visual Basic 6 (let alone the Forms package in the NET Framework),
UserForms in VBA have rather limited capabilities. One you have designed the UserForm, there isn't
much you can do using properties and methods of the UserForm to change its appearance or functionality
at run-time. This page describes how to use the Windows Application Programmatic Interface (API) functions
to extend and customize UserForms in VBA.
The techniques and code described on this page is available as a downloadable workbook.
On this page, we will use the terms UserForm and Form synonymously to mean a UserForm
within VBA version 6 (Office 2000 and later). If any other type of form is referred to, it will be made clear
what type of form that is.
API functions are functions and procedures that are part of the Windows operating system. They give you access to
capabilities that are not available from VBA or from the objects (UserForms, Workbooks, etc.,) that make up a VBA Project.
When you call an API, you are bypassing VBA and calling directly upon Windows. This means that you do not get the safety mechanisms
such as type checking that VBA normally provides. If you pass an invalid value to an API or (a very common mistake) use a ByRef
parameter instead of a ByVal parameter, you will most likely completely and immediately crash Excel and you will
lose all your unsaved work. I recommend that until you are confident that your API calls are solid you save your
work before calling an API function.
In the following discussion, Windows (with a captial W) should be taken to mean Microsoft Windows, the operating system. The
terms window or windows (with a lower case w) should be taken to mean a window object managed by
Microsoft Windows and described briefly below.
Windows (the operating system) identifies windows (on the screen) by use of what is called a Window Handle, abbreviated for
this point forward as HWnd. In the context of Windows (the operating system), a window is a region of the screen that
has properties and memory associated with it and that can receive and respond to messages from the operating system (messages that are
typically initiated by some action, such as clicking in a list box). A window can be either a top level window (such as the main Excel
application window) or it may be a child of another window. A window can be both a child and a parent window -- it may be a child of
an application window and contain within it child windows. For example, the main Excel application is a top level window, having no parent,
that contains child windows. Some of the child windows of the Excel application are the command bars, the status bar, the worksheet windows,
and many more. Windows (the operating system) manages the parent-child relationships by assigning each window its own HWnd. There are
API functions that can be used to find the parent of a given HWnd or to find the children of an HWnd. The HWnd is essentially a unique
identifier used by the operating system to identify a window. No meaningful information can be taken from the value of an HWnd. It simply
identifies a window.
Every window managed by Windows has a class name associated with it. (Note: This class name has nothing whatsoever to do with the name
of the UserForm. All UserForms have the same class name, ThunderDFrame. This is unrelated to the name of the form (e.g.,
frmMyForm) and cannot be changed. This class name identifies a set properties shared by all windows of that
class name. For example, the main Excel application window has a class name of XLMAIN. Class names never change but HWnds do.
When you start Excel, the application will
always have a class name of XLMAIN but will have a different HWnd each time it is started. (This is because different regions
of memory are used each time Excel is started. You can think of an HWnd as simply an address of a window in memory.)
When a window is created, attributes of that window (such as having a Minimize button) are stored in memory allocated to the window. If you
know the HWnd of a window, you can access the properties of that window by reading the window's property values and you can change the
properties of a window (such as displaying or hiding a Minimize button) by changing the values of a window's properties. As noted before, caution
should be exercised when working with window properties -- one wrong move can crash everything. The code on this page uses API
functions to find the HWnd of a UserForm based on the class name of the UserForm and then reads and modifies the memory associated with
the window. Pretty much anything you do with a window you do via the HWnd value.
The discussion of HWnds and window classes and their associated memory will now become clear. We cannot know at design time what the HWnd of
a UserForm will be; that value is assigned at run time by Windows. However, we do know two crucial pieces of information. The first is
the class name of the UserForm. For Office 2000 and later, that name is ThunderDFrame (for Office 97, it is
ThunderXFrame). The other crucial piece of information is the Caption of the form, assigned
either at design time in the properties window of the designer or at run time by assigning a String value to the
Caption property. Using these to pieces of information, we can find the HWnd of the form. The FindWindow
API function does this for us. Once we have a valid HWnd (if it is valid, an HWnd will be not equal to 0), we can call
GetWindowLong to access the window's configuration memory, and we can call SetWindowLong
to modify these values.
For illustration, we'll look at a single procedure, one that will make a UserForm resizable, to see how things work. Once you understand
this, you can easily follow all the other procedures in the downloadable example. Examine the code
below:
Sub MakeFormResizable()
Dim UFHWnd As Long
Dim WinInfo As Long
Dim R As Long
Const GWL_STYLE = -16
Const WS_SIZEBOX = &H40000
Const WS_USER as Long = &H4000
Load UserForm1
UFHWnd = FindWindow("ThunderDFrame", UserForm1.Caption)
If UFHWnd = 0 Then
Debug.Print "UserForm not found"
Exit Sub
End If
WinInfo = GetWindowLong(UFHWnd, GWL_STYLE)
WinInfo = WinInfo Or WS_SIZEBOX
R = SetWindowLong(UFHWnd, GWL_STYLE, WinInfo)
UserForm1.Show
End Sub
We first load UserForm1 into memory. This just loads the form -- it does not make it visible to the user.
Next, we call the FindWindow API function, passing it the window class name ThunderDFrame
and the Caption property of UserForm1. If this is successful,
UFHWnd will be non-zero. Then, we call GetWindowLong, passing it the
HWnd of the window we want to modify, a number (GWL_STYLE) indicating which
part of the window's memory we want to read. This value is returned to the WinInfo variable.
Once we have the form's Style setting in memory (in the WinInfo variable), we can modify it. Each bit in
the 32 bits of the WinInfo long controls one aspect of the window. The bit that controls whether a
form is resizable is bit 19 (starting at bit 1 on the right and counting right to left). That is the number stored in the
WS_SIZEBOX constant. In binary, the value is 0000 00000 0000 0100 0000 0000 0000 0000. When this value
is combined via a bit-wise Or with the existing value of WinInfo, all bits in
WinInfo that were originally set remain set, bit 19, or &H40000, is turned on, and
all bits (except &H40000) that were orignially clear remain clear. Only bit 19 is changed. After setting the bit to make the form
sizable, the code calls SetWindowLong to set the form's property memory to the modified value of
WinInfo. Finally, the form is made visible and is shown to the user.
When you use a method similar to the code shown above to change a form's properties, those changes remain in effect until the form
is unloaded from memory. Therefore, if you call Hide to hide the form, the property changes remain in effect. If,
however, you call Unload to dump the form, any changes you made to the properties are lost and must be
changed again, if desired, before showing the form a second time.
The downloadable workbook contains a number of functions, all fairly similar to the
MakeFormResizable procedure described above, to contol many aspects of a UserForm. The procedures
are listed below.
Function SetFormParent(UF As MSForms.UserForm, _
Parent As FORM_PARENT_WINDOW_TYPE) As Boolean
Sets a UserForm to be a child of another window, such as the application window or one of the workbook windows. See
this page for a discussion of parent and child windows as they relate to the Excel application windows.
Function ShowMaximizeButton(UF As MSForms.UserForm, _
HideButton As Boolean) As Boolean
Shows or hides a Maximize and Minimize button on the UserForm. Calling this function or ShowMinimizeButton will
cause both a Minimize and a Maximize button to be displayed, but only the Maximize button (if ShowMaximizeButton is called) or
only the Minimize button (if ShowMinimizeButton is called) will be functional. To make both buttons
functional, call both ShowMaximizeButton and ShowMinimizeButton
Function ShowMinimizeButton(UF As MSForms.UserForm, _
HideButton As Boolean) As Boolean
Shows or hides a Maximize and Minimize button on the UserForm. Calling this function or ShowMaximizeButton will
cause both a Minimize and a Maximize button to be displayed, but only the Maximize button (if ShowMaximizeButton is called) or
only the Minimize button (if ShowMinimizeButton is called) will be functional. To make both buttons
functional, call both ShowMaximizeButton and ShowMinimizeButton
Function HasMaximizeButton(UF As MSForms.UserForm) As Boolean
Return True if the Maximize button is visible and functional, False otherwise.
Function HasMinimizeButton(UF As MSForms.UserForm) As Boolean
Return True if the Minimize button is visible and functional, False otherwise.
Function IsCloseButtonVisible(UF As MSForms.UserForm) As Boolean
Return True if the Close button is visible, False otherwise. This will return True if even if the Close button is not functional.
Function IsCloseButtonEnabled(UF As MSForms.UserForm) As Boolean
Return True if the Close button is visible and functional, False otherwise.
Function ShowCloseButton(UF As MSForms.UserForm, HideButton As Boolean) As Boolean
Displays or hides the Close button on a UserForm.
Function EnableCloseButton(UF As MSForms.UserForm, Disable As Boolean) As Boolean
Enables or disables the Close button on a UserForm.
Function IsTitleBarVisible(UF As MSForms.UserForm) As Boolean
Returns True if the Title Bar on a UserForm is visible, False otherwise.
Function ShowTitleBar(UF As MSForms.UserForm, HideTitle As Boolean) As Boolean
Shows or hides the Title Bar of a UserForm.
Function IsFormResizable(UF As MSForms.UserForm) As Boolean
Returns True or False indicating whether the UserForm is resizable.
Function MakeFormResizable(UF As MSForms.UserForm, Sizable As Boolean) As Boolean
Enables or disables the resizability of a UserForm.
Function SetFormOpacity(UF As MSForms.UserForm, Opacity As Byte) As Boolean
Changes the opacity of a from between 255 (fully opaque -- normal mode) and 0 (totally transparent -- invisible).
Function HWndOfUserForm(UF As MSForms.UserForm) As Long
Returns the window handle (HWnd) of a user form. Supports UserForms that are top level windows, child windows of the application,
or child windows of a workbook window.
The techniques and code described on this page is available as a downloadable workbook.
This page last updated: 17-May-2008