Transferring Data from a UserForm to a worksheet
 

Helo all. I have been using examples to create a Userform to enter data
into a worksheet. Although I have followed the instruction (with some
name changes) I keep getting a subscript out of range error in Line 4
Set ws = worksheets.
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("BrickData")

iRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

If Trim(Me.TxtType.Value) = "" Then
Me.TxtType.SetFocus
MsgBox "Please Enter a Brick Type"
Exit Sub
End If

ws.Cells(iRow, 1).Value = Me.TxtType.Value
ws.Cells(iRow, 2).Value = Me.TxtShape.Value
ws.Cells(iRow, 3).Value = Me.TxtOrder.Value
ws.Cells(iRow, 4).Value = Me.TxtDate.Value
ws.Cells(iRow, 5).Value = Me.TxtQuantity.Value

Me.TxtType.Value = ""
Me.TxtShape.Value = ""
Me.TxtOrder.Value = ""
Me.TxtDate.Value = ""
Me.TxtQuantity.Value = ""

End Sub
Any Ideas? Thanks in advance

Transferring Data from a UserForm to a worksheet
 

subscript out of range would mean you don't have a sheet with the name
BrickData

check the name of your sheet and make sure you don't have extra spaces on
the end or you haven't misspelled it in your code.

--
Regards,
Tom Ogilvy



"Kezza" wrote:

Helo all. I have been using examples to create a Userform to enter data
into a worksheet. Although I have followed the instruction (with some
name changes) I keep getting a subscript out of range error in Line 4
Set ws = worksheets.
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("BrickData")

iRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

If Trim(Me.TxtType.Value) = "" Then
Me.TxtType.SetFocus
MsgBox "Please Enter a Brick Type"
Exit Sub
End If

ws.Cells(iRow, 1).Value = Me.TxtType.Value
ws.Cells(iRow, 2).Value = Me.TxtShape.Value
ws.Cells(iRow, 3).Value = Me.TxtOrder.Value
ws.Cells(iRow, 4).Value = Me.TxtDate.Value
ws.Cells(iRow, 5).Value = Me.TxtQuantity.Value

Me.TxtType.Value = ""
Me.TxtShape.Value = ""
Me.TxtOrder.Value = ""
Me.TxtDate.Value = ""
Me.TxtQuantity.Value = ""

End Sub
Any Ideas? Thanks in advance