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.

Introduction

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.

Window Handles

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    ' HWnd of UserForm
    Dim WinInfo As Long   ' Values associated with the UserForm window
    Dim R As Long
    Const GWL_STYLE = -16
    Const WS_SIZEBOX = &H40000
    Const WS_USER  as Long = &H4000
    Load UserForm1  ' Load the form into memory but don't make it visible
    UFHWnd = FindWindow("ThunderDFrame", UserForm1.Caption)  ' find the HWnd of the UserForm
    If UFHWnd = 0 Then  
        ' cannot find form
        Debug.Print "UserForm not found"
        Exit Sub
    End If
    
    WinInfo = GetWindowLong(UFHWnd, GWL_STYLE)      ' get the style word (32-bit Long)
    WinInfo = WinInfo Or WS_SIZEBOX                 ' set the WS_SIZEBOX bit
    R = SetWindowLong(UFHWnd, GWL_STYLE, WinInfo)   ' set the style word to the modified value.
    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.

Persistence Of Changes

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.

Functions In The Downloadable Code

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.

SetFormParent

    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.

ShowMaximizeButton

    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

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

HasMaximizeButton

    Function HasMaximizeButton(UF As MSForms.UserForm) As Boolean

Return True if the Maximize button is visible and functional, False otherwise.

HasMinimizeButton

    Function HasMinimizeButton(UF As MSForms.UserForm) As Boolean

Return True if the Minimize button is visible and functional, False otherwise.

IsCloseButtonVisible

    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.

IsCloseButtonEnabled

    Function IsCloseButtonEnabled(UF As MSForms.UserForm) As Boolean

Return True if the Close button is visible and functional, False otherwise.

ShowCloseButton

    Function ShowCloseButton(UF As MSForms.UserForm, HideButton As Boolean) As Boolean

Displays or hides the Close button on a UserForm.

EnableCloseButton

    Function EnableCloseButton(UF As MSForms.UserForm, Disable As Boolean) As Boolean

Enables or disables the Close button on a UserForm.

IsTitleBarVisible

    Function IsTitleBarVisible(UF As MSForms.UserForm) As Boolean

Returns True if the Title Bar on a UserForm is visible, False otherwise.

ShowTitleBar

    Function ShowTitleBar(UF As MSForms.UserForm, HideTitle As Boolean) As Boolean

Shows or hides the Title Bar of a UserForm.

IsFormResizable

    Function IsFormResizable(UF As MSForms.UserForm) As Boolean

Returns True or False indicating whether the UserForm is resizable.

MakeFormResizable

    Function MakeFormResizable(UF As MSForms.UserForm, Sizable As Boolean) As Boolean

Enables or disables the resizability of a UserForm.

SetFormOpacity

    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).

HWndOfUserForm

    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