Help with Controls.Add method

AlexanderBB

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

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AlexanderBB

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

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,703
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
23,770

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,703
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,703
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,691
Messages
5,512,896
Members
408,920
Latest member
KLH81

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top