• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating Labels & TextBoxes at Run Time

Bomino

Bomino

Member
Hi
I have a userform where all the Labels and TextBoxes are created at Run Time. I got pretty much everything set up correctly except for the most important part: Transferring textboxes data from Userform to the worksheet.
For some reasons, the code for the CommandButton1 keeps giving me an error message:
"Object doesn't support this property or method"

Could someone please help?

See the entire Userform code below and I am also attaching the file.

Code: 
Dim numbertxt As Long

Private Sub UserForm_Initialize()
Dim i As Long

numbertxt = Application.CountA(ShtMyGrades.[A:A])

Dim lblL1 As Control
For i = 2 To numbertxt
Set lblL1 = Controls.Add("Forms.Label.1")

With lblL1
.Caption = “Label” & i
.Name = “lbl” & i
.Height = 20
.Width = 50
.Left = 20
.Top = 20 * i * 1
End With
Next i

Dim q As Long
For q = 2 To numbertxt
Controls(“lbl” & q) = Cells(q, 1)
Next q


Dim txtB1 As Control
For i = 2 To numbertxt
Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = “TxtBox” & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With
Next i

End Sub

Private Sub CommandButton1_Click()
Dim p As Long

For p = 2 To numbertxt
  ShtMyGrades.Cells(p, 5) = Controls(“TxtBox” & p).Text
Next p

End Sub

Private Sub CommandButton3_Click()

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Then
ctl.Value = “”
End If
Next ctl

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub
 

Attachments

  • GradeCalculator.xlsm
    28.4 KB · Views: 2
I could not duplicate your problem once I changed the slanted double quotes to straight double quotes (Ctrl+F, Replace). There were a few double quotes after the & i" counter which had to be removed.

Option Explicit as first line of code and Debug menu's Compile should catch those errors.

Code: 
Dim numbertxt As Long

Private Sub UserForm_Initialize()
Dim i As Long

numbertxt = Application.CountA(ShtMyGrades.[A:A])

Dim lblL1 As Control
For i = 2 To numbertxt
Set lblL1 = Controls.Add("Forms.Label.1")

With lblL1
.Caption = "Label" & i
.Name = "lbl" & i
.Height = 20
.Width = 50
.Left = 20
.Top = 20 * i * 1
End With
Next i

Dim q As Long
For q = 2 To numbertxt
Controls("lbl" & q) = Cells(q, 1)
Next q


Dim txtB1 As Control
For i = 2 To numbertxt
Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "TxtBox" & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With
Next i

End Sub

Private Sub CommandButton1_Click()
Dim p As Long

For p = 2 To numbertxt
  ShtMyGrades.Cells(p, 5) = Controls("TxtBox" & p).Text
Next p

End Sub

Private Sub CommandButton3_Click()

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = """"
End If
Next ctl

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub
 
:):):)
I spent hours trying to figure out what I was doing wrong.
Thank you Kenneth Hobson. I truly appreciate it.
 
Kenneth,
I wantd to create a second UserForm. So I modified the above code to add a second column of Textboxes at Run Time. But I am still having serious problem saving the texboxes values to worksheet. I would like to have the results saved in sheet1.[A:B].

Could you please help again? Here is my code:


Code: 
Dim numbertxt As Long
Const StartWidth = 220
Dim StartHeight As Integer





Private Sub UserForm_Initialize()
Dim i As Long
Dim jrow As Long

On Error Resume Next

numbertxt = Application.InputBox("Enter number of Assignments for the Term", "Initial Set Up")

StartHeight = 20 * numbertxt + 120
With Me
        .Width = StartWidth
        .Height = StartHeight
End With
                    Dim lblL1 As Control
                    For i = 1 To numbertxt
                            Set lblL1 = Controls.Add("Forms.Label.1")
                          
                            With lblL1
                                .Caption = "Label" & i
                                .Name = "lbl" & i
                                .Height = 20
                                .Width = 20
                                .Left = 30
                                .Top = 20 * i * 1
                                .TextAlign = 2
                                .BorderStyle = 1
                                .BackColor = &HE0E0E0
                            End With
                    Next i
                  
                        Dim q As Long
                        For q = 1 To numbertxt
                        Controls("lbl" & q) = "#" & q
                        Next q

                Dim txtB1 As Control
                'For jrow = 1 To 2
                For i = 1 To numbertxt
                    For jrow = 1 To 2
                        Set txtB1 = Controls.Add("Forms.TextBox.1")
                            With txtB1
                                .Name = "TxtBox" & jrow
                                .Height = 20
                                .Width = 50
                                .Left = 50 * jrow + 2
                                .Top = 20 * i * 1
                                .ControlTipText = "Assignment ~~~>Associated Weight"
                            End With
                      Next jrow
                  Next i

End Sub

Private Sub CommandButton1_Click()
Dim p As Long
'~~~~> Here is where I can't get it to work as desired.
'~~~~> I would like to have all TextBoxes values in Sheet1 Columns A:B

For p = 1 To numbertxt
  For jrow = 1 To 2
      Sheet1.Cells(p, jrow) = Controls("TxtBox" & jrow).Text
      Next jrow
    Next p


End Sub

Private Sub CommandButton3_Click()

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = """"
End If
Next ctl

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub
 

Attachments

  • GradeCalculator_With_SetUp.xlsm
    41.6 KB · Views: 4
Your commandbutton1 is not the problem per se. It is the naming of your textbox controls. You duplicated textbox names 1 and 2.

Think about a naming scheme for the 2 columns of textbox controls. e.g.
For 3 sets: tbA1, tbA2, tbA3, tbB1, tbB2, tbB3.

Another for 3 sets by your inputbox might be: tb1, tb2, tb2, tb1001, tb1002, tb1003.

Your current loop to name them uses the columns 2, rather than the first loop.

If needed, I can show you how to name them using one of the two methods above and then loop accordingly in your commandbutton1. As the number of sets increase a lot, one should fill a 2d array and then set the range using it.
 
I amended the code per your suggestion, and it seems to work:

Code: 
      Dim txtB1 As Control
                'For jrow = 1 To 2
                For i = 1 To numbertxt
                    For jrow = 1 To 2
                        Set txtB1 = Controls.Add("Forms.TextBox.1")
                            With txtB1
                                .Name = "TxtBox" & i & jrow
                                .Height = 20
                                .Width = 50
                                .Left = 50 * jrow + 2
                                .Top = 20 * i * 1
                                .ControlTipText = "Assignment ~~~>Associated Weight"
                            End With
                      Next jrow
                  Next i

End Sub

Private Sub CommandButton1_Click()
Dim p As Long

For p = 1 To numbertxt
  For jrow = 1 To 2
    'For p = 1 To numbertxt
      Sheet1.Cells(p, jrow) = Controls("TxtBox" & p & jrow).Text
      Next
    Next
'Next p

End Sub

I like the idea of using the arrays; could you please show your ideas?
 
I don't see that method working. Did you test for more more than two row sets?

Add this so you can see what is going on. After a Run, press Ctrl+G.
Code: 
Private Sub UserForm_Click()
  Dim c As Control
  For Each c In Controls
    Debug.Print c.Name
  Next c
End Sub
Once a method works, I can show you how to do the array. Basically, one does the loop but rather than putting values into cells, it assigns values to a Redimmed array to be the size of your matrix. It is then a simple matter to set a cell and Resize it to be the same as the array/matrix and set that range's Value equal to the array.
 
Here is my AB method.
Code: 
Private Sub UserForm_Initialize()
  Dim i As Long, jrow As Integer, q As Long
  Dim lblL1 As Control
 
  On Error Resume Next
 
  numbertxt = Application.InputBox("Enter number of Assignments for the Term", "Initial Set Up")
 
  StartHeight = 20 * numbertxt + 120
  With Me
    .Width = StartWidth
    .Height = StartHeight
  End With
 
  For i = 1 To numbertxt
    Set lblL1 = Controls.Add("Forms.Label.1")
    With lblL1
      .Caption = "Label" & i
      .Name = "lbl" & i
      .Height = 20
      .Width = 20
      .Left = 30
      .Top = 20 * i * 1
      .TextAlign = 2
      .BorderStyle = 1
      .BackColor = &HE0E0E0
    End With
  Next i
 
  For q = 1 To numbertxt
    Controls("lbl" & q) = "#" & q
  Next q
 
  For i = 1 To numbertxt
    For jrow = 1 To 2
      With Controls.Add("Forms.TextBox.1")
      If jrow = 1 Then
        .Name = "TxtBoxA" & i
        Else: .Name = "TxtBoxB" & i
      End If
      .Height = 20
      .Width = 50
      .Left = 50 * jrow + 2
      .Top = 20 * i
      .ControlTipText = "Assignment ~~~>Associated Weight"
      End With
    Next jrow
  Next i
End Sub
 
Private Sub CommandButton1_Click()
  Dim p As Long, a, jrow As Integer
 
  ReDim a(1 To numbertxt, 1 To 2)
 
  For p = 1 To numbertxt
    For jrow = 1 To 2
      If jrow = 1 Then
        a(p, jrow) = Controls("TxtBoxA" & p).Text
        Else
          a(p, jrow) = Controls("TxtBoxB" & p).Text
      End If
    Next jrow
  Next p
 
  Sheet1.[A1].Resize(numbertxt, 2).Value = a
End Sub
 
Kenneth Hobson said:
I don't see that method working. Did you test for more more than two row sets?

Add this so you can see what is going on. After a Run, press Ctrl+G.
Code: 
Private Sub UserForm_Click()
  Dim c As Control
  For Each c In Controls
    Debug.Print c.Name
  Next c
End Sub
Once a method works, I can show you how to do the array. Basically, one does the loop but rather than putting values into cells, it assigns values to a Redimmed array to be the size of your matrix. It is then a simple matter to set a cell and Resize it to be the same as the array/matrix and set that range's Value equal to the array.
Click to expand...
I tested it for 10 and 15 rows and it worked; but I don't think it would work for more than 2 columns tho'.
 
I must have copied wrong I guess. If you need help with the array method using your method, post back with the file and will check it. Obviously, I can't work with duplicate control names.

TIP: The Tag property can often help for some tasks. e.g. Textbox1 value to Sheet1!A1, Textbox2 value goes to Sheet2!B13, etc.
 
Kenneth Hobson said:
Here is my AB method.
Code: 
Private Sub UserForm_Initialize()
  Dim i As Long, jrow As Integer, q As Long
  Dim lblL1 As Control

  On Error Resume Next

  numbertxt = Application.InputBox("Enter number of Assignments for the Term", "Initial Set Up")

  StartHeight = 20 * numbertxt + 120
  With Me
    .Width = StartWidth
    .Height = StartHeight
  End With

  For i = 1 To numbertxt
    Set lblL1 = Controls.Add("Forms.Label.1")
    With lblL1
      .Caption = "Label" & i
      .Name = "lbl" & i
      .Height = 20
      .Width = 20
      .Left = 30
      .Top = 20 * i * 1
      .TextAlign = 2
      .BorderStyle = 1
      .BackColor = &HE0E0E0
    End With
  Next i

  For q = 1 To numbertxt
    Controls("lbl" & q) = "#" & q
  Next q

  For i = 1 To numbertxt
    For jrow = 1 To 2
      With Controls.Add("Forms.TextBox.1")
      If jrow = 1 Then
        .Name = "TxtBoxA" & i
        Else: .Name = "TxtBoxB" & i
      End If
      .Height = 20
      .Width = 50
      .Left = 50 * jrow + 2
      .Top = 20 * i
      .ControlTipText = "Assignment ~~~>Associated Weight"
      End With
    Next jrow
  Next i
End Sub

Private Sub CommandButton1_Click()
  Dim p As Long, a, jrow As Integer

  ReDim a(1 To numbertxt, 1 To 2)

  For p = 1 To numbertxt
    For jrow = 1 To 2
      If jrow = 1 Then
        a(p, jrow) = Controls("TxtBoxA" & p).Text
        Else
          a(p, jrow) = Controls("TxtBoxB" & p).Text
      End If
    Next jrow
  Next p

  Sheet1.[A1].Resize(numbertxt, 2).Value = a
End Sub
Click to expand...
Thank you so very much Kenneth Hobson!
 
Kenneth Hobson said:
I must have copied wrong I guess. If you need help with the array method using your method, post back with the file and will check it. Obviously, I can't work with duplicate control names.

TIP: The Tag property can often help for some tasks. e.g. Textbox1 value to Sheet1!A1, Textbox2 value goes to Sheet2!B13, etc.
Click to expand...
Awesome!!
I will definitely get back in touch. Once again, thanks a lot!
 
You must log in or register to reply here.
Back
Top