Help with Controls.Add method

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,835
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,

I came across the following via Google
Code:
Private Sub CommandButton1_Click()
Dim cControl As Control


    Set cControl = Me.Controls.Add("Forms.Frame.1", "MyFrame", True)
    
    With cControl
        .Width = 100
        .Height = 135
        .Top = 0
        .Left = 0
        .ZOrder (1)
    End With
    
    Set cControl = Me.Controls.Add("Forms.TextBox.1", "MyTextBox", True)
    
    With cControl
        .Width = 150
        .Height = 50
        .Top = 20
        .Left = 20
        .ZOrder (0)
    End With
    
End Sub

After trying the above it seems the controls don't 'stay'. Looking at the Form again in design mode they've gone.

Does this means the code must be run from the Form and the controls are intended to be temporary? They vanish as the Form is closed - but otherwise are available to use in the same way as a control created at design time?

Are there any known issues that might affect their usage? Are any of the common toolbox contols excluded?

And finally, could anyone tell me (or has a link) where I can learn the parameters for the 'Set cControl =' lines ?

Many thanks, ABB
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Thanks Andrew, I read the link and ran some tests. Results are looking good so I'm doing a major redesign of my whole form. There's a number of design time controls and suddenly more were needed. There's x groups of 7 labels in a Frame... x is usually 3 or less but it can be as high as 7. It just seemed crazy to have so many controls and I'm sure creating them dynamically as needed is the answer. Just hope I can manage it!

Cheers, ABB
 
Upvote 0
On the fly control creation almost demands the use of Class modules.

If you want to program the Design time creation of controls, you can use the .Designer object.

Code:
With ThisWorkbook.VBProject.vbcomponents("Userform1").designer
    With .Controls.Add("Forms.TextBox.1", "MyTextBox", True)
        .Width = 150
        .Height = 50
        .Top = 20
        .Left = 20
    End With
End With
 
Upvote 0
Thanks Mike, will certainly do that.

Do all the properties need setting up, or are the deafaults the same as
at design time?
 
Upvote 0
Mike, I've struck a problem on line -

With .Controls.Add("Forms.TextBox.1", "MyTextBox", True)

I get

Run-time error '91': Object variable or With block variable not set

I have your code in a module and have tried calling it (from aother module) with and without 'Userform1.Show'.

The same error each time. Helppp!!!

Thanks, ABB
 
Upvote 0
This code is to be run at design time. And only once at that (you cant have two textboxes named "MyTextBox").

Start with a new workbook.
Insert a Userform (no controls)
Insert a new module and put this sub in it.

Code:
Sub MakeABox()
    With ThisWorkbook.VBProject.vbcomponents("Userform1").designer
        With .Controls.Add("Forms.TextBox.1", "MyTextBox", True)
            .Width = 150
            .Height = 50
            .Top = 20
            .Left = 20
        End With
    End With
End Sub

After running MakeABox, Userform1 will have a textBox control (named MyTextBox) that it didn't have before.
Running MakeABox again will cause an error because of the name conflict.

You mentioned Frames with 7 Labels each.
The sub MakeAFrame will make a frame and fill it with 7 labels.

The sub Make5Frames will make 5 of these frames and position them. (It works best on a UF that is 700 wide or greater)

Code:
Sub make5FilledFrames()
    Dim i As Long
    For i = 1 To 5
        Call MakeAFrame(20, 10 + (i - 1) * 140)
    Next i
End Sub

Sub MakeAFrame(myTop As Single, myLeft As Single)
    Dim i As Long
    With ThisWorkbook.VBProject.vbcomponents("Userform1").designer
        With .Controls.Add("Forms.Frame.1", , True)
            .Width = 120
            .Height = 200
            .Top = myTop
            .Left = myLeft
            .Caption = "hello"
            For i = 1 To 7
                With .Controls.Add(("Forms.Label.1"), , True)
                    .Width = 100
                    .Height = 18
                    .Top = 10 + (i - 1) * (.Height + 5)
                    .Left = 10
                End With
            Next i
        End With
    End With
End Sub

The whole purpose of this is to automate the makeing of a userform at design time. Very rarely would there be a reason for the user to call a routine that uses the Designer object.

When making multiple copies of controls like this, Class modules to drive the event code is a good solution, but assigning the controls to that class must be done at run time.

And, of course, if the .Designer is used to add controls, the controls will go away if the Workbook is not saved.

About the default properties, if I don't specify it, I can't complain if it doesn't happen, so you can try it, and if it doesn't turn out the way you want, you can either delete the newly made control, change the creation code and run it again.

Or you can alter the default properties of an existing control using the designer object.
This code will change the default backcolor of an existing TextBox
Code:
With ThisWorkbook.VBProject.vbcomponents("Userform1").designer
    .Controls("TextBox1").BackColor = RGB(255, 250, 50)
End With
 
Last edited:
Upvote 0
Hi Mike,

Thanks very much for the info and examples. I'm getting there in leaps and bounds (but it hurts the brain something awful :( !!)

I've almost got the dynamic controls doing exactly what the design times ones did, with one exception. One of my group of seven is a Listbox and
I'm wondering how to code the click and double click events as they don't appear in the VBA editor.

The other thing, and this may be wrong but is what I want AFAIK, the Form needs to be called repeatedly, and I understand the .designer method is call once. So I've used Dim cControl As Control and Set cControl = method which is giving the desired result and may be OK.

Regards, ABB
 
Upvote 0
Hi Andrew,

Yes, I see what you mean. But I've spent all day removing the design time stuff and adding this new dynamic method! And got it all working bar the event procedure.
Looks like that example's a Workbook Open event, and I'm after a ListBox Click and Double Click event. With any luck Google may have some further examples. And I'm guessing each list box will need it.

Roll up sleeves and into it....:mad:

Regards, ABB
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top