Writing Text box value from userform to worksheet

[rpaulson]

here is all the code for my user form.

the form initializes just fine and I can type values into all my text boxes.

but when I click the OK Button (CommandButton1) to write the date to row 2 of my worksheet I get the following error.

"Object doesn't support this property or method"

for this line
Cells(2, c) = Controls(“txtBox” & c).Text

Private Sub CommandButton1_Click()
Dim c As Long
For c = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(2, c) = Controls(“txtBox” & c).Text
Next c
End Sub

Private Sub UserForm_initialize()
Dim ctlLbl As Control
Dim ctlTxt As Control
 
lc = Cells(1, Columns.Count).End(xlToLeft).Column
 
For c = 2 To lc
    Set ctlLbl = Me.Controls.Add("Forms.Label.1") 'Label
    With ctlLbl
    .Name = “lblBox” & c
    .Top = (c - 1) * 25
    .Left = 20
    .Width = 200
    .Caption = Cells(1, c)
    .Font.Bold = True
    .Font.Size = 10
    End With
Next c

 For c = 2 To lc
    Set ctlTxt = Me.Controls.Add("Forms.TextBox.1") 'text
    With ctlTxt
    .Name = “txtBox” & c
    .Top = (c - 1) * 25 - 10
    .Left = 150
    .Height = 25
    .Width = Cells(1, c).Width * 3
    .Font.Bold = True
    .Font.Size = 13
    End With
Next c
  
End Sub

Thanks for looking,

Ross

 

[AlphaFrog]

There are two types of quotation marks; The slanted type “ ” and the so-called normal stype " ".
In VBA, the normal type is required. You're using a mix of both.

Private Sub CommandButton1_Click()
Dim c As Long
For c = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(2, c) = Controls([COLOR="#FF0000"]“[/COLOR]txtBox[COLOR="#FF0000"]”[/COLOR] & c).Text
Next c
End Sub

Private Sub UserForm_initialize()
Dim ctlLbl As Control
Dim ctlTxt As Control
 
lc = Cells(1, Columns.Count).End(xlToLeft).Column
 
For c = 2 To lc
    Set ctlLbl = Me.Controls.Add("Forms.Label.1") 'Label
    With ctlLbl
    .Name = [COLOR="#FF0000"]“[/COLOR]lblBox[COLOR="#FF0000"]”[/COLOR] & c
    .Top = (c - 1) * 25
    .Left = 20
    .Width = 200
    .Caption = Cells(1, c)
    .Font.Bold = True
    .Font.Size = 10
    End With
Next c

 For c = 2 To lc
    Set ctlTxt = Me.Controls.Add("Forms.TextBox.1") 'text
    With ctlTxt
    .Name = [COLOR="#FF0000"]“[/COLOR]txtBox[COLOR="#FF0000"]”[/COLOR] & c
    .Top = (c - 1) * 25 - 10
    .Left = 150
    .Height = 25
    .Width = Cells(1, c).Width * 3
    .Font.Bold = True
    .Font.Size = 13
    End With
Next c

 

[rpaulson]

OMG. Who new, I don't even know how to type in the slanted quote, must have been that way from the source I copy and pasted some of the code from.

Anyway, it works now.

my head still hurts from banging it on the wall trying to figure it out.

Thanks

 

[AlphaFrog]

You're welcome. For those that are interested, Wikipedia has a more complete description of them.

Quotation Mark