Working with User Forms

The code examples presented in this chapter so far have been extending the VBE to provide additional tools for the developer. This section shifts its attention to programmatically creating and manipulating UserForms, adding controls, and adding procedures to the UserForm's code module to handle the controls' events. Though the example provided in this section continues to extend the VBE, the same code and techniques can be applied in end-user applications, including:

□ Adding UserForms to workbooks created by the application

□ Sizing the UserForm and moving and sizing its controls to make the best use of the available screen space

□ Adding code to handle events in UserForms created by the application

□ Changing the controls shown on an existing UserForm in response to user input

□ Creating UserForms on the fly, as they are needed (for example, when the number and type of controls on the UserForm will vary significantly depending on the data to be shown)

These techniques will be demonstrated by writing code to add a UserForm to the active project, complete with standard-sized OK and Cancel buttons, as well as code to handle the buttons' Click events and the UserForm's QueryClose event. The UserForm's size will be set to two-thirds of the width and height of the Excel window, and the OK and Cancel buttons' position will be adjusted accordingly.

The example shown here is the difficult way to achieve the desired result, and is intended to be an educational, rather than a practical, example. The easy way to add a standardized UserForm is to create it manually and export it to disk as a .frm file, then import it using the following code (do not type this in):

Dim oVBC As VBComponent

Set oVBC = Application.VBE.ActiveVBProject.VBComponents.Import("MyForm.frm")

When you need to include it in another project, just import it again. The only advantage to doing it through code is that the UserForm can be given a size appropriate to the user's screen resolution and size, and its controls are positioned correctly.

Start by adding code in CMenuHandler Class_Initialize to create another menu:

AddMenu Application.VBE.CommandBars("Standard").FindControl(ID:=3280 6) _ .CommandBar, "&Standard Form", "FormNewUserform", 2, 581, _ msoButtonIconAndCaption, "Insert standardized UserForm"

The result of this addition will be the Standard Form menu, shown in Figure 26-5.

You'll be using objects from the MSForms object library to create the form and controls, so add a reference to the Microsoft Forms 2.0 Object Library by using the Tools O References dialog, or just adding and removing a UserForm. It takes quite a lot of code to create an entire form, so in this section the code listing is shown and explained piecemeal; all the highlighted lines of code should be typed in.




Standard form




Class Module



Addin Class

Figure 26-5

Figure 26-5

Add a new module for this routine, call it modMenuForm, and copy in the following code:

Option Explicit

'Window API call to freeze a window

'It does the same as Application.ScreenUpdating, but for the VBE Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long)

As Long

Application.ScreenUpdating does not affect the VBE, and the following FormNewUserformprocedure to create a form results in quite a lot of screen activity as the form is sized and the controls are drawn. A simple Windows API call can be used to freeze the VBE window at the start of the routine and unfreeze it at the end (see Chapter 27 for more information about using this and other API functions):

' Subroutine:


' Purpose:

Creates a new userform, Add-Ing standard OK and code to handle their events

and Cancel buttons

Sub FormNewUserform()

Dim oVBC As VBIDE.VBComponent, fmFrmDesign As UserForm,

lLine As Long

Microsoft's Windows design guidelines recommend a gap of 6 points (approximately 4 pixels) between buttons, and between a button and the edge of a form.

Const dGap As Double = 6

This is one of the more complex routines in the Add-in, so some error-handling code will be added to it. Every routine in this chapter should really be given similar error-handling code.

'Use our error handler to display a message if something goes wrong On Error GoTo ERR_HANDLER

Use the Windows API call to freeze the VBE's window. Note that HWnd is a hidden property of the MainWindow object. To display the hidden properties of an object, open the Object Browser, right-click in its window, and click the Show Hidden Members item.

'Freeze the VBE window - same as Application.ScreenUpdating = False LockWindowUpdate Application.VBE.MainWindow.HWnd

The VBComponent object (oVBC in the code) provides the "canvas" (background) of the UserForm, its Properties collection, and its CodeModule. When a new UserForm is added to a project, a VBComponent object is passed back that contains the form. The VBComponent's Properties collection can be used to change the size, color, font, caption, and so forth of the form's background.

'Add a new userform to the active VB Project

Set oVBC = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm)

'Set the form's height and width to 2/3 that of the Excel application. oVBC.Properties("Width") = Application.UsableWidth * 2 / 3 oVBC.Properties("Height") = Application.UsableHeight * 2 / 3

The VBComponent's Designer object provides access to the content of the UserForm, and is responsible for the area inside the form's borders and below its title bar. In this code, two controls are added to the normal blank UserForm to provide standard OK and Close buttons. The name to use for the control (Forms.CommandButton.1 in this case) can be found by adding the control to a worksheet, then examining the resulting =EMBED function. The appropriate controls can be found on the Developer tab of the Ribbon by clicking Controls O Insert O ActiveX Controls.

'Get the UserForm's Designer Set fmFrmDesign = oVBC.Designer

'Use the designer to add the standard controls With fmFrmDesign

'Add an OK button, according to standard Windows size With .Controls.Add("Forms.CommandButton.1", "bnOK") .Caption = "OK" .Default = True .Height = 18 .Width = 54 End With

'Add a Cancel button, according to standard Windows size With .Controls.Add("Forms.CommandButton.1", "bnCancel") .Caption = "Cancel" .Cancel = True .Height = 18 .Width = 54 End With

'Move the OK and Cancel buttons to the bottom-right of the UserForm, 'with a standard-width gap around and between them With .Controls("bnOK")

.Top = fmFrmDesign.InsideHeight - .Height - dGap .Left = fmFrmDesign.InsideWidth - .Width * 2 - dGap * 2 End With

With .Controls("bnCancel")

.Top = fmFrmDesign.InsideHeight - .Height - dGap .Left = fmFrmDesign.InsideWidth - .Width - dGap End With End With

This could be extended to add list boxes, labels, checkboxes, and so on. From this point on, you could just as easily be working with an existing UserForm, changing its size and the position and size of its controls to make the best use of the available screen resolution. The preceding code simply moves the OK and Cancel buttons to the bottom-right corner of the UserForm, without adjusting their size. The same technique can be used to move and size all of a UserForm's controls.

Now that buttons have been added to the UserForm at the correct place (the bottom-right corner), code can be added to the UserForm's module to handle the buttons' and UserForm's events. To add a procedure to a code module, you can use the CreateEventProc, InsertLines, or AddFromString methods. In this example, the code is being added from strings. Alternatively, the code could be kept in a separate text file and imported into the UserForm's module. If CreateEventProc is used, all of the procedure's parameters are filled in on your behalf, and you get the Private Sub... line, the End Sub line, and a blank line between them. CreateEventProc returns the number of the line in the module where the Private Sub... was added, which is then used to insert a comment line and to replace the default blank line with the code:

' Now add some code to the userform'

s code module

With oVBC.CodeModule

'Add the code for the OK button's

Click event

lLine = .CreateEventProc("Click",


.InsertLines lLine, "'Standard OK

button handler"

.ReplaceLine lLine + 2, " mbOK =

= True" & vbCrLf & "


If you use AddFromString or InsertLines, you have to supply the full text, such as this for the Cancel button:

'Add the code for the Cancel button's Click event .AddFromString vbCrLf & _

"'Standard Cancel button handler" & vbCrLf & _ "Private Sub bnCancel_Click()" & vbCrLf & _ " mbOK = False" & vbCrLf & _ " Me.Hide" & vbCrLf & _ "End Sub"

The code for the UserForm's QueryClose event is the same as that of the Cancel button, so some code will be added just to call the bnCancel_Click routine:

'Add the code for the UserForm's Close event - just call the Cancel code lLine = .CreateEventProc("QueryClose", "UserForm")

.InsertLines lLine, "'Standard Close handler, treat same as Cancel" .ReplaceLine lLine + 2, " bnCancel_Click"

'And close the code window that was automatically opened by Excel 'when we created the event procedures .CodePane.Window.Close End With

'Unfreeze the VBE window - same as Application.ScreenUpdating = True LockWindowUpdate 0&

Exit Sub

The standard error handler unfreezes the window, displays the error message, and closes. Such error handling should be added to all the routines in the Add-in:


'Unfreeze the VBE window - same as Application.ScreenUpdating = True LockWindowUpdate 0&

'Display the error message (in the VBE Window) and end the routine. Application.Visible = False

MsgBox "An Error occurred while creating the standard userform." & vbCrLf & _ Err.Number & ": " & Err.Description, vbOKOnly, psAddinTitle

Application.Visible = True End Sub

The Add-in is now complete. Switch back to Excel, use Office Menu O Prepare O Properties to give it a title and comment, then save the workbook as an Add-in (near the bottom of the list of available file types) with a .xlam extension. Then use the Add-Ins dialog box (Office Menu O Excel Options O Add-Ins O Manage: Excel Add-Ins O Go) to install it.

Was this article helpful?

0 0
The Accidental Blogging Millionaires

The Accidental Blogging Millionaires

Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.

Get My Free Ebook

Post a comment