Home > Publications & Training > Case Studies > Dynamic Userform
Web This Site

Creating userform controls on the fly

In the vast majority of cases, a userform contains a static list of items: these captions, those textboxes, such-and-such commandbuttons, etc.  Then, there are those instances when the contents of the userform cannot be determined except at runtime.  The design of the userform specifies where and how the controls will be shown with the actual controls added at runtime.  I recently had to create such a dynamic userform while working on TM Retro Slicers – an add-in that extends the Excel 2010 slicer capability to earlier versions of Excel. This note shows how to work with such a dynamic userform. 

For those not familiar with a slicer, it is a filter for a pivottable (or pivotchart) field shown as an independent object.  A slicer has are other capabilities but they are not germane to this note.

In TM Retro Slicers, I use a userform to show a slicer.  A slicer separately lists every value associated with a particular pivot field.  So, if a particular pivot field, Location, has 2 values (Campus and Satellite), the associated slicer would look like:

Figure 1 – the + is a static label control.
The blue controls below it are added at runtime

The + sign in the userform is a static control.  The blue controls below it are added at runtime.

On the other hand, if there are many more items in the pivotfield (as in the example below), the same userform would list each of those values separately.  Obviously, this requires a taller userform.

Figure 2

Comparing a statically created form and one that is created dynamically leads to a few key differences:

·       We have to add the controls and configure the relevant attributes of each of these controls at runtime rather than at design time.  Similarly, we have to configure the userform’s attributes (in particular, its size) at runtime.

·       When we add a control dynamically, there has to be a way to retain a reference to the control so that we can subsequently work with it.  Typically, this is done by adding it to a global VBA collection (or a Scripting.Dictionary) object.

·       We have to have some way to respond to user actions (such as a mouse click) associated with these dynamically added controls.

Adding userform controls at runtime

The Userform.Controls collection supports the Add method and it is this method that we will use to add controls to the form.  The syntax to add a control to a userform is

Set Control = object.Add( ProgID [, Name [, Visible]])

The help page for the Add Method lists the following valid ProfIDs:





























And, here’s a bonus.  How does one add a RefEdit control?  Stumped?  Well, I didn’t know either but a Google search of the ‘Net led to




To start with the example, create a userform and add a label towards the left top of the userform.  Change to caption to the plus sign (+) and name this control lbl_AddPT.  Next, and as I almost always do, add a public method (called Manager) that a consumer of the userform will use to create the slicer’s userform.  The code in Manager initializes the collection where we will store a reference to each newly created label.  It also creates one label control for each pivot item in the pivot field passed as the argument to the Manager method.  Then, it adjusts the height of the userform to accommodate all the labels.  It also changes the userform’s caption to include the pivotfield’s name and then shows the userform.

Option Explicit

Dim AllLbls As Collection


Public Function Manager(ByVal SlicerField As PivotField) _

        As Boolean


    Set AllLbls = New Collection


    Dim aPI As PivotItem, BtnNbr As Integer

    On Error GoTo ErrXIT

    For Each aPI In SlicerField.PivotItems

        addALabel aPI.Value, aPI.Visible, BtnNbr

        Next aPI

    On Error GoTo 0


    With Me.Controls("Val" & (BtnNbr - 1))

    Me.Height = .Top + .Height + 30

        End With

    Me.Caption = "TM Slicer: " & SlicerField.Name


    Me.Show vbModeless



    Manager = True

    Exit Function


    MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"

    Manager = False

    End Function

Code Sample 1

The work of actually adding each label control is delegated to the addALabel subroutine, which uses the one static control (named lblAddPT) in the userform (see Figure 1) as a base to locate the new controls.  It then configures various attributes of the new label.  In particular, the background color is set to pale blue if the value is already selected in the pivot field or white if it is not.  After configuring all the attributes, the code adds the control to an object of type clsUFLabel and then adds that object to the AddLabels collection.  The reason for adding the label to the clsUFLabel object is to enable event handling.  We will see how that works in the next section.

Private Sub addALabel(ByVal PIValue As String, PIVisible As Boolean, _

        ByRef BtnNbr As Integer)


    Dim aLbl As MSForms.Label

    Set aLbl = Me.Controls.Add("forms.label.1", "Val" & BtnNbr)


    Dim BaseCtl As MSForms.Label: Set BaseCtl = Me.lblAddPT


    With aLbl

    .Top = BaseCtl.Top + BaseCtl.Height + BtnNbr * (15 + 3) + 3

    .Left = 6

    .Caption = PIValue ' CellVal

    .BackStyle = fmBackStyleOpaque

    .BackColor = IIf(PIVisible, lblSelectedColor, lblNonSelectedColor)

    .Height = 15

    .BorderStyle = fmBorderStyleSingle


    Dim X As clsUFLabel: Set X = New clsUFLabel

    Set X.aLbl = aLbl

    AllLbls.Add X, aLbl.Name

        End With


    BtnNbr = BtnNbr + 1

    End Sub

Code Sample 2

The functions lblSelectedColr and lblNonSelectedColor return RGB values.  Rather than add the RGB values to each line of code where they are needed, the functions yield a central location for the colors – something that drastically simplifies maintenance.  Put the below code in a standard module.

Public Function lblSelectedColor() As Long

    lblSelectedColor = RGB(184, 204, 228)

    End Function

Public Function lblNonSelectedColor() As Long

    lblNonSelectedColor = RGB(255, 255, 255)

    End Function

Code Sample 3

If we were to use the code up to this point – possible by commenting out the references to clsUFLabel – the result would be a userform containing one label per pivot item.  But, clicking on the label would do nothing.  For that we have to add an event procedure that interacts with the user.

Responding to user interactions with the dynamic controls

When a userform control is known at design time, one can write an event procedure such as the one below that responds to a user click.

Private Sub lblAddPT_Click()

    Debug.Print "In lblAddPT_Click"

    End Sub

Code Sample 4

But, what happens when we know what controls to add only at runtime?  Clearly, we cannot write the code ahead of time.  And, even if we could, there is the question of repetitive code.  After all, each of the labels in the slicer will behave identically other than for the fact that they affect a different pivot item.  So, writing static code, even if it were possible, would be wasteful.  Luckily, there is a simpler way – I first saw it, I believe, on John Walkenbach’s site (www.j-walk.com).

It is possible to declare a variable that supports events with the keyword WithEvents.  However, such a variable can exist only in a class module.  So, add a new class module, name it clsUFLabel and add the code below.  Whenever, the aLbl variable’s Click event occurs, VBA will call the aLbl_Click procedure, which in turn toggles the background color of the label.

Option Explicit


Public WithEvents aLbl As MSForms.Label


Private Sub aLbl_Click()

    If aLbl.BackColor = lblSelectedColor Then

        aLbl.BackColor = lblNonSelectedColor


        aLbl.BackColor = lblSelectedColor

        End If

    aLbl.Parent.Repaint: DoEvents

    End Sub

Code Sample 5

And, of course, in the previous section, we had already added the code to use an object of this class.

Use the above example

To run the above, add a pivottable in a worksheet.  Then, select any cell in the pivottable and run the subroutine below to see a list of all the pivotitems for the associated pivotfield.  Click any label in the resulting userform and the color of that label will toggle between white and a shade of blue.

Option Explicit


Sub getGoing()

    frmSlicer.Manager ActiveCell.PivotField

    End Sub







Share Your Comments