How to create user form using VBA?

B

BHawk

New Member
Joined
Apr 9, 2010
Messages
8
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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
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:
Code: 
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
 
Upvote 0
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:
Code: 
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:
Code: 
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.
 
Upvote 0
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.
 
Upvote 0
Darren Bartrup said:
Rich (BB code): 
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
Click to expand...
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.
 
Upvote 0
rorya said:
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.
Click to expand...

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.. :(
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If I comment out that line, it shows me the form:

a38be94893cb1d9946c7888d17c5ebd6.jpg
 
Upvote 0
I would strongly suggest you do not use:
Rich (BB code): 
Dim SpnColct As New Collection
Instead use:
Rich (BB code): 
Dim SpnColct As Collection
and then in the form's Initialize event use:
Rich (BB code): 
Set SpnColct = New Collection
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Userform to update sheet totals
2
Replies
12
Views
554
dmt32
D
M
  • Question Question
Using text input box to call cell values.
2
Replies
12
Views
163
Skyybot
Skyybot
D
  • Solved
Date Issues with User Forms
Replies
4
Views
205
dmt32
D
G
  • Solved
Textbox Value +1
Replies
4
Views
102
gilly01625
G
E
  • Question Question
Creating data entry form
Replies
3
Views
382
EssKayKay
E

Forum statistics

Threads
1,224,534
Messages
6,179,391
Members
452,909
Latest member
VickiS

Share this page

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