Lesson 10 - Excel Userforms Finding the ActiveControl Creating Controls at Runtime

AUD $0.00

Category: [General,Excel] Demo Available

Workbook Download
This is a zipped Excel Workbook to go with this lesson.

Finding the ActiveControl,

Creating Controls at Runtime

Creating Controls

At times when creating a project in Excel VBA which incorporates the use of a UserForm, you may at times wish to create actual Controls via the choice of a user. The advantage of actually creating the Control is usually only the fact that it will require the need for less Controls on the UserForm at any one time, which in turn can lessen overheads.

To create Controls via VBA, we use the Add Method. The Add Method allows us to insert a new tab, or page in a TabStrip or MultiPage Control or (as we will be doing) add a Control to a UserForm at Runtime. The syntax for the Add Method for adding Controls to a UserForm is as shown below:

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

You should note from the above that the only mandatory argument that we must supply is the ProgID. Once we are aware of what these are, the actual creation of a Control is relatively simple. The table below shows the IDs for each Control that we can add:

ProgID values for individual controls are:

CheckBox	Forms.CheckBox.1
ComboBox	Forms.ComboBox.1
CommandButton	Forms.CommandButton.1
Frame	Forms.Frame.1
Image	Forms.Image.1
Label	Forms.Label.1
ListBox	Forms.ListBox.1
MultiPage	Forms.MultiPage.1
OptionButton	Forms.OptionButton.1
ScrollBar	Forms.ScrollBar.1
SpinButton	Forms.SpinButton.1
TabStrip	Forms.TabStrip.1
TextBox	Forms.TextBox.1
ToggleButton	Forms.ToggleButton.1

The above table shows the ProgID that must be supplied to the Add Method as a String. Lets now look at how we can use this on a UserForm to create a CommandButton in its most basic form.

Private Sub CommandButton1_Click()

Me.Controls.Add _
"Forms.CommandButton.1", "CopyOf"
End Sub

To use the above code, simply insert a UserForm an on the UserForm, place one CommandButton called CommandButton1. Position this button to the top right hand side of the UserForm. Then, within the Private Module of the UserForm, place the code as shown above. Run the UserForm and click CommandButton1 and you should immediately see a new CommandButton inserted onto your UserForm. By default, the button will be located in the top left hand corner of the UserForm. The only optional argument we have supplied is the name of the button which we have called CopyOf. There is no need for us to supply an argument to the Visible Property as the default is for it to be visible anyway. In other words it is only if we did not want the Control to be Visible in which case we would pass the Boolean False to this argument.

The above code is the very minimum code that we would use should we wish to create a Control onto a UserForm. For it to be of any use, we would obviously need to change some of its Properties immediately after the Control has been created.

Lets now modify the code we used above to not only add the Control, but also this time to add a Caption to our CommandButton. The first way we will use to do this is really only useful if you are to be changing one or two Properties of the newly added Control. When we reference a Control directly that has just been created, we must use either UserForm1!CopyOf<Property to change>. The important part to note here, is the user of the exclamation mark after the name of the UserForm, which in this case was UserForm1. We can also, to be more generic, use the key word Me, eg; Me!CopyOf<Property to change>. The example below shows how this can be done.

Private Sub CommandButton1_Click()
Dim cCont As Control

Me.Controls.Add _
        "Forms.CommandButton.1", "CopyOf"

   Me!CopyOf.Caption = "Hello"

End Sub

If we were to be changing more than two Properties of the Control, it would probably be best if we used a Variable declared as a Control. The reason for this would be that we could simply then use the With Statement on the Control Object created to modify its Properties very easily. We then set the Variable to the actual Control created and then work with the Variable to change some of its Properties. The example below demonstrates how you would change the Caption Property and the AutoSize Property to ensure the full Caption text is displayed.

Private Sub CommandButton1_Click()
Dim cCont As Control

Set cCont = Me.Controls.Add _
        ("Forms.CommandButton.1", "CopyOf")

    With cCont
        .Caption = "Thanks for creating me"
        .AutoSize = True
    End With

End Sub

So using code similar to the above, we can access the Properties of the Control added and change them accordingly.

If we wanted to give the user a choice of which Control type to add to our UserForm, this again could be done with relative ease by inserting a ComboBox and giving the user a drop-down list of possible choices. As the Add Method requires a String for its ProgID argument, we can simply join the needed text chosen by the user from the ComboBox with the necessary "Forms" and the String Value ".1". The Procedure below shows how this could be done on a UserForm containing a Command button called CommandButton1 and a ComboBox called ComboBox1.

Private Sub CommandButton1_Click()
Dim cCont As Control
Dim strControl As String

If ComboBox1.ListIndex > -1 Then
strControl = "Forms." & ComboBox1 & ".1"
End If

Set cCont = Me.Controls.Add _
(strControl, "CopyOf")

End Sub

Private Sub UserForm_Initialize()
With ComboBox1
        .AddItem "CheckBox"
        .AddItem "CommandButton"
        .AddItem "TextBox"
        .AddItem "ToggleButton"
    End With
End Sub

As you will see when you run this code, it enables us to create any one of the four Controls that we have added to our ComboBox.

Another point of interest when adding Controls is that the UserForm itself has an Add Control Event. This Event is automatically fired when a Control is added at RunTime. Naturally, the Event is not fired when a Control is added at design time. It is the actual Add method which initiates the Add Control Event. A simple use of this would be to display a MessageBox letting the user know a Control has been added.

Private Sub UserForm_AddControl (ByVal Control As MSForms.Control)

MsgBox "Your Control has been Added"

End Sub

This Event would simply fire whenever a Control is added to our UserForm via the Add method.

Naturally, if you are going to be adding Controls at RunTime to a UserForm, you will most likely need to also add code to a specific Event for the Control that has been added. This requires programming the VBE (Visual Basic Environment) and referencing Object Properties and Methods which are specific to the VBE environment. You will find that most often rather than create a Control on a UserForm, you will have the Control sitting there all of the time, pre-coded and simply toggle the Visible or Enable Property of the Control which would then allow the User to use the Control in the desired way. This method is far simpler and less prone to errors than going down the programming of the Visual Basic Environment, but for those that are interested, follow the link below as a good starting point as to how this can be done.

http://www.cpearson.com/excel/vbe.htm

Should you wish to take this further, the two books listed below both have very good detailed chapters on programming the VBE , I have these books myself and can highly recommend them for not only programming the VBE but Visual Basics for Applications in general. They are aimed at the Intermediate + Level, which now you should consider yourself at.

Finding the ActiveControl

When coding a UserForm it can often be very handy to be able to identify the current ActiveControl. By doing this, we can very easily make our code more generic and save ourselves from typing the same Procedure over and over within the Private Module of the UserForm. To identify the ActiveControl, all we really need to do is use Me.ActiveControl

An example of when this could be used could be in the situation where we have numerous Controls of the same type on a UserForm and we need to run a similar Procedure when the user activates one of the Events of the Control.

Dim iRow As Integer

Private Sub DoComboAction()
'Toggle the Bold Property of the chosen name
iRow = ActiveControl.ListIndex + 1
    If iRow > 0 Then
      With Range(ActiveControl.RowSource).Cells(iRow, 1)
       .Font.Bold = Not (.Font.Bold)
      End With
    End If
iRow = 0
End Sub

Private Sub Level_1_Change()
DoComboAction
End Sub

Private Sub Level_2_Change()
DoComboAction
End Sub

Private Sub Level_3_Change()
DoComboAction
End Sub

Private Sub Level_4_Change()
DoComboAction
End Sub

The above code makes use of the ActiveControl and allow sus to place the code in one Procedure that runs the code for the ComboBox which has had its ChangeEvent fired.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples

Gallery

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.