How to create user form using VBA?

[BHawk]

Hello,

I've allready made an user form, and now I want to do something different.

I want that when I press a command button, it creates a new label(#1) and a text box(#1) on the same form. And when I press the same button again, it creates the same label(#2) under label#1 and a text box(#2) under text box#1

Why I want to do this? Because there is no fixed how many labels and text boxes there will be.

I've searched a little bit around and found this: http://www.tek-tips.com/faqs.cfm?fid=5757 but it doesn't work.

Can anyone help me?

 

[Jon von der Heyden]

Welcome to the board!

AFAIK you can't add controls to a userform class instance (i.e. when it is open). You can, however, add controls to the userform when is isn't loaded.

Example:

Public Sub AddControl()
    Dim VBP As VBProject
    Dim VBC As VBComponent

    Set VBP = ThisWorkbook.VBProject
    Set VBC = VBP.VBComponents("UserForm1")
    
    With VBC.Designer
        With .Controls.Add("Forms.CommandButton.1")
            .Caption = "Cancel"
            .Width = 85
            .Height = 35
            .Left = 8
            .Top = 40
        End With
    End With
End Sub

 

[Darren Bartrup]

I haven't got the full set of code, I'll try and find it when I get home from work.

Create a userform (UserForm1).
Place a Command Button (CommandButton1) and a Frame (Frame1) onto the form. Make sure the Frame has its ScrollBars property set to 2 - fmScrollBarsVertical

Create a Class Module and name it cControlEvent.
Enter this code to the Class Module:

Option Explicit

Public WithEvents SP As MSForms.SpinButton
Public WithEvents TXT As MSForms.TextBox

Private Sub SP_SpinDown()
    SP.Value = SP.Value - 1
    MsgBox "Spin Down to " & SP.Value
End Sub

Private Sub SP_SpinUp()
    SP.Value = SP.Value + 1
    MsgBox "Spin Up to " & SP.Value
End Sub

Private Sub TXT_Change()
    MsgBox "You changed the value."
End Sub

Add this code to the User Form:

Option Explicit

Dim SpnColct As New Collection

Private Sub CommandButton1_Click()

    Dim cSpnEvnt As cControlEvent
    Dim ctlSB As Control
    Dim ctlTXT As Control
    Dim lngCounter As Long
    
    For lngCounter = 1 To 30
        Set ctlTXT = Me.Frame1.Controls.Add("Forms.TextBox.1", "Text" & lngCounter)
        ctlTXT.Name = "Text" & lngCounter
        ctlTXT.Left = 5
        ctlTXT.Height = 15: ctlTXT.Width = 50
        ctlTXT.Top = (lngCounter - 1) * 17 + 2
    
        Set ctlSB = Me.Frame1.Controls.Add("Forms.SpinButton.1", "Spin" & lngCounter)
        ctlSB.Name = "Spin" & lngCounter
        ctlSB.Left = 60
        ctlSB.Height = 15: ctlSB.Width = 10
        ctlSB.Top = (lngCounter - 1) * 17 + 2
        
        Set cSpnEvnt = New cControlEvent
        Set cSpnEvnt.SP = ctlSB
        Set cSpnEvnt.TXT = ctlTXT
        SpnColct.Add cSpnEvnt
    
    Next lngCounter
    
    Me.Frame1.ScrollHeight = (lngCounter - 1) * 17 + 2
    
End Sub

This will create 30 spin buttons and text boxes within the frame on the form and provide a scrollbar to view them all. Each control has its own event.

Obviously not exactly what you're after, but hopefully gives you a start.

 

[RoryA]

You can add them at runtime, they just won't be saved with the form when it's unloaded - they need to be added each time.

 

[BHawk]

Option Explicit

Dim SpnColct As New Collection

Private Sub CommandButton1_Click()

    Dim cSpnEvnt As cControlEvent
    Dim ctlSB As Control
    Dim ctlTXT As Control
    Dim lngCounter As Long
    
    For lngCounter = 1 To 30
        Set ctlTXT = Me.Frame1.Controls.Add("Forms.TextBox.1", "Text" & lngCounter)
        ctlTXT.Name = "Text" & lngCounter
        ctlTXT.Left = 5
        ctlTXT.Height = 15: ctlTXT.Width = 50
        ctlTXT.Top = (lngCounter - 1) * 17 + 2
    
        Set ctlSB = Me.Frame1.Controls.Add("Forms.SpinButton.1", "Spin" & lngCounter)
        ctlSB.Name = "Spin" & lngCounter
        ctlSB.Left = 60
        ctlSB.Height = 15: ctlSB.Width = 10
        ctlSB.Top = (lngCounter - 1) * 17 + 2
        
        Set cSpnEvnt = New cControlEvent
        Set cSpnEvnt.SP = ctlSB
        Set cSpnEvnt.TXT = ctlTXT
        SpnColct.Add cSpnEvnt
    
    Next lngCounter
    
    Me.Frame1.ScrollHeight = (lngCounter - 1) * 17 + 2
    
End Sub

This is actually great, it helps a lot!

I'm just wondering, what does the "red marked" part in code?

Because it gives me Run-time error '424': Object required and if I disable that line, it works great.

 

[BHawk]

You can add them at runtime, they just won't be saved with the form when it's unloaded - they need to be added each time.

I allready did this version, but it looks complex. Imagine when you have like 60 entries and next time only 3. And I never know if 60 is enough..

 

[RoryA]

It's the same thing that Darren is suggesting. As long as you know how many controls you want, you just add them when the form loads.

 

[Darren Bartrup]

I'm not sure why it's causing an error on that line.

Are the events firing when you comment out that line? Do you get message boxes popping up when you change the value of the spin buttons or text boxes? (as they don't when I comment the line out).

The three lines above the problem line connect the controls to the spin and change events defined within the class module.
These are then stored within a collection (available to all procedures within the form module) so they can be used.

 

[BHawk]

If I comment out that line, it shows me the form:

 

[RoryA]

I would strongly suggest you do not use:

Dim SpnColct As New Collection

Instead use:

Dim SpnColct As Collection

and then in the form's Initialize event use:

Set SpnColct = New Collection