Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

If you've spent any time developing UserForms, you probably know that it can be quite tedious to add and adjust the controls so that they're aligned and sized consistently. Even if you take full advantage of the VBE formatting commands, it can still take a considerable amount of time to get the controls to look just right.

The UserForm shown in Figure 28-8 contains 100 CommandButtons, all of which are identical in size and positioned precisely on the form. Furthermore, each CommandButton has its own event handler procedure. Adding these buttons manually and creating their event handlers would take some time - lots of time. Adding them automatically at design time by using a VBA procedure takes less than a second.

Figure 28-8: A VBA procedure added the CommandButtons on this UserForm.

Design-time versus runtime UserForm manipulations

It's important to understand the distinction between manipulating UserForms or controls at design time and manipulating these objects at runtime. Runtime manipulations are apparent when the UserForm is shown, but the changes made are not permanent. For example, you might write code that changes the Caption property of the UserForm before the form is displayed. The new caption appears when the UserForm is shown, but when you return to the VBE, the UserForm displays its original caption. Runtime manipulation is very common, and Part IV of this book contains many examples of code that perform runtime manipulation of UserForms and controls.

Design-time manipulations, on the other hand, are permanent - just as if you made the changes manually by using the tools in the VBE. Normally, you perform design-time manipulations as a way to automate some of the tedious chores in designing a UserForm. To make design-time manipulations, you access the Designer object for the UserForm.

To demonstrate the difference between design-time and runtime manipulations, I developed two simple procedures that add a CommandButton to a UserForm. One procedure adds the button at runtime; the other adds it at design time.

The following RunTimeButton procedure is very straightforward. When used in a general (non-UserForm) module, it simply adds a CommandButton, changes a few of its properties, and then displays the UserForm. The CommandButton appears on the form when the form is shown, but when you view the form in the VBE, the CommandButton is not there.

Sub RunTimeButton() ' Adds a button at runtime Dim Butn As CommandButton Set Butn = UserForm1.Controls.Add("Forms.CommandButton.1") With Butn .Caption = "Added at runtime" .Width = 100 .Top = 10 End With UserForm1.Show End Sub

Following is the DesignTimeButton procedure. What's different here is that this procedure uses the Designer object, which is contained in the VBComponent object. Specifically, it uses the Add method to add the CommandButton control. Because the Designer object was used, the CommandButton is added to the UserForm just as if you did it manually in the VBE.

Sub DesignTimeButton() ' Adds a button at design time Dim Butn As CommandButton Set Butn = ThisWorkbook.VBProject. _ VBComponents("UserForm1") _ .Designer.Controls.Add("Forms.CommandButton.1") With Butn .Caption = "Added at design time" .Width = 120 .Top = 40 End With End Sub

Adding 100 CommandButtons at design time

The example in this section demonstrates how to take advantage of the Designer object to help you design a UserForm. In this case, the code adds 100 CommandButtons ( perfectly spaced and aligned), sets the Caption property for each CommandButton, and also creates 100 event handler procedures (one for each CommandButton).

Sub Add100Buttons() Dim UFvbc As VBComponent Dim CMod As CodeModule Dim ctl As Control Dim cb As CommandButton Dim n As Long, c As Long, r As Long Dim code As String Set UFvbc = ThisWorkbook.VBProject.VBComponents("UserForm1") ' Delete all controls, if any For Each ctl In UFvbc.Designer.Controls UFvbc.Designer.Controls.Remove ctl.Name Next ctl ' Delete all VBA code UFvbc.CodeModule.DeleteLines 1, UFvbc.CodeModule.CountOfLines ' Add 100 CommandButtons n = 1 For r = 1 To 10 For c = 1 To 10 Set cb = UFvbc.Designer. _ Controls.Add("Forms.CommandButton.1") With cb .Width = 22 .Height = 22 .Left = (c * 26) - 16 .Top = (r * 26) - 16 .Caption = n End With ' Add the event handler code With UFvbc.CodeModule code = "" code = code & "Private Sub CommandButton" & n & _ "_Click" & vbCr code = code & "Msgbox ""This is CommandButton" & n & _ """" & vbCr code = code & "End Sub" .InsertLines .CountOfLines + 1, code End With n = n + 1 Next c Next r End Sub

The Add100Buttons procedure requires a UserForm named UserForm1 . You'll need to make the UserForm a bit larger than its default size so that the buttons will fit. The procedure starts by deleting all controls on the form by using the Remove method of the Controls collection and then deleting all the code in the code module by using the DeleteLines method of the CodeModule object. Next, the CommandButtons are added, and the event handler procedures are created within two For-Next loops . These event handlers are very simple. Here's an example of such a procedure for CommandButton1 :

Private Sub CommandButton1_Click() MsgBox "This is CommandButton1" End Sub

If you'd like to show the form after adding the controls at design time, you need to add the following instruction right before the End Sub statement:

VBA.UserForms.Add("UserForm1").Show

It took me quite a while to figure out how to actually display the UserForm. When VBA generates the 100-button UserForm, it indeed exists in VBA's memory, but it isn't officially part of the project yet. So you need the Add method to formally enroll UserForm1 into the collection of UserForms . The return value of this method is a reference to the form itself, which is why the Show method can be appended to the end of the Add method. So, as a rule, the UserForm must be added to the UserForms collection before it can be used.