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?

 

[murry]

Darren's code was a great starting point for me, but I cannot figure out how to take the input from a created textbox and enter into a sheet.

The textbox is called Text1, Text2, . . .I confirmed that with a message box, but when I try to have it populate the a cell I get "Variable not defined" error. I'm not sure why it thinks it's a variable:

Workbooks(ActiveWorkbook.Name).Worksheets("sheet2").Cells(2, 3) = text1.Value

 

[cedricthecat]

It should be as easy as

Range("A1") = Text1

You can specify Workbooks and Sheets if you are putting the value elsewhere, but this should work if the user form is operating on an open Sheet!

 

[murry]

It should be as easy as

Range("A1") = Text1

You can specify Workbooks and Sheets if you are putting the value elsewhere, but this should work if the user form is operating on an open Sheet!

I agree that it should be that easy, but it's not working. The range is not the issue. The issue is with the text box name. My understanding is a text box name is a property and if I say

Range("A1") = Text1.value

it should display the value in the text box named "text1" in cell A1. The problem is I get an error messages that the text1 varible is not defined.

 

[Norie]

Try using the Controls collection of the userform.

ActiveWorkbook.Worksheets("sheet2").Cells(2, 3) = Me.Controls("text1").Value

 

[My Aswer Is This]

When you build UserForms like this can they be saved and used again some other day. Or do you have to rebuild it every time you want to use it?

 

[Norie]

If you add controls at runtime they will not be available the next time you run the form.

The code from Jon in post #2 will create add a userform to the VB project which you can use again.

 

[My Aswer Is This]

Ok. Thanks

If you add controls at runtime they will not be available the next time you run the form.

The code from Jon in post #2 will create add a userform to the VB project which you can use again.

 

[murry]

This didn't work either....

Try using the Controls collection of the userform.

ActiveWorkbook.Worksheets("sheet2").Cells(2, 3) = Me.Controls("text1").Value

I will try to post my code since I don't fully understand everything Darren did.

Class Module

Option Explicit


Public WithEvents SP As MSForms.Label
Public WithEvents TXT As MSForms.TextBox
Public WithEvents CB As MSForms.ComboBox
Public WithEvents CMD As MSForms.CommandButton


Private Sub CMD_click()






ActiveWorkbook.Worksheets("sheet2").Cells(2, 3) = Me.Controls("text1").Value




'Private Sub txt_change()


'MsgBox (TXT.Name)


'Unload Me


End Sub

and the user form:

Private Sub OKButton_Click()


    If Trim(ResultTextBox.Value) = "" Then
        MsgBox ("please enter desired number of results")
        Exit Sub
    End If
    


'Set Variables
methodname = NameTextBox.Value
resultNum = ResultTextBox.Value


    Dim cResEvnt As cControlEvent
    Dim ctlSB As Control
    Dim ctlTXT As Control
    Dim ctlCB As Control
    Dim ctlCMD As Control
    Dim lngCounter As Long
    Dim Lbl As MSForms.Label
      
    
    For lngCounter = 1 To resultNum
            
        Set ctlSB = Me.Frame1.Controls.Add("Forms.Label.1", "Lbl" & lngCounter)
        ctlSB.Caption = "Result " & lngCounter
        ctlSB.Left = 5
        ctlSB.Height = 15: ctlSB.Width = 50
        ctlSB.Top = (lngCounter - 1) * 17 + 2
        
        Set ctlTXT = Me.Frame1.Controls.Add("Forms.TextBox.1", "Text" & lngCounter)
        ctlTXT.Left = 60
        ctlTXT.Height = 15: ctlTXT.Width = 50
        ctlTXT.Top = (lngCounter - 1) * 17 + 2
        
        Set ctlCB = Me.Frame1.Controls.Add("Forms.ComboBox.1", "CB" & lngCounter)
        ctlCB.Left = 115
        ctlCB.Height = 15: ctlCB.Width = 108
        ctlCB.Top = (lngCounter - 1) * 17 + 2
        ctlCB.RowSource = "sheet2!$A$51:$A$54"
       
        Set cResEvnt = New cControlEvent
        Set cResEvnt.SP = ctlSB
        Set cResEvnt.TXT = ctlTXT
        Set cResEvnt.CB = ctlCB
        ResColct.Add cResEvnt
    
    Next lngCounter


Set ctlCMD = Me.Frame1.Controls.Add("Forms.Commandbutton.1", "cmdNextStep")
    ctlCMD.Caption = "Next Step"
    ctlCMD.Left = 5
    ctlCMD.Height = 24: ctlSB.Width = 72
    ctlCMD.Top = (resultNum + 1) * 17 + 2
Set cResEvnt = New cControlEvent
Set cResEvnt.CMD = ctlCMD
ResColct.Add cResEvnt


End Sub

I'm sure it's my lack of understanding of Class Modules and Events.

Thanks

 

[Norie]

How/what exactly doesn't it work?

What are you actually trying to do?

Could you upload a sample workbook to a file-sharing site like Box.net?

PS It might be worth starting a new thread for this with an explanation of what you are trying to achieve.s

 

[murry]

I have a form that based on user input displays a number of textboxes and combo boxes. After those are filled in I want eventually populate the spreadsheet. The problem is that I can't seem to take the text from the generated textbox and populate the spreadsheet.