Help with Controls.Add method

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,749
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,749
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,205
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
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,749

ADVERTISEMENT

Thanks Mike, will certainly do that.

Do all the properties need setting up, or are the deafaults the same as
at design time?
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,749
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,205

ADVERTISEMENT

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:

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,749
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Use CreateEventProc to create the event procedure:

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

But it may be simpler to put the controls on the UserForm at design time and hide the ones you don't want until necessary (like Excel's Find and Replace dialog which expands when Options is clicked).
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,749
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,419
Messages
5,831,510
Members
430,072
Latest member
klelmira

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
Top