 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
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()
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.
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.