Userform problem?

S

Sue

Hi All

I have a userform with first of all four text boxes that enter a customers
details on a sheet no problem see code below. We sell 24 items and have 24
textboxes in 3 rows of 8 e.g. Tb4 thru Tb11 / Tb12 thru Tb19 / Tb20 thru Tb27
.. I would like to put them all on the command button Add1 and for them all to
enter on 3 separate rows Tb4:Tb11 on one row starting at Col "D" / Tb12:Tb19
on the next row starting at Col "D" / Tb20:Tb27 on the next row starting at
Col "D"
We then hopefully have another customer his / her details are added to the
next rows and so on. If any of the 24 Tboxes have nothing entered the vba
should still run as normal.

Private Sub Add1_Click()
Dim ws As Worksheet
Set ws = Worksheets("New")
startrownum = 2
endrownum = 200
For rownum = startrownum To endrownum
If Trim(Sheets("New").Range("A" & Trim(Str(rownum)))) = "" Then
freerownum = rownum
rownum = endrownum
End If
Next rownum
ws.Cells(freerownum, 1).Value = " " & Trim(UserForm1.Tb1.Value) + " " &
Trim(UserForm1.Tb2.Value)
ws.Cells(freerownum, 2).Value = Tb1A.Value
ws.Cells(freerownum, 3).Value = Tb2A.Value
 
J

Joel

Private Sub Add1_Click()
Dim ws As Worksheet
With Worksheets("New")
startrownum = 2
endrownum = .Range("A2").End(xlDown).Row
freerownum = endrownum + 1
.Range("A" & freerownum & ":A" & (freerownum + 2)).Value = " " & _
Trim(UserForm1.Tb1.Value) + " " & Trim(UserForm1.Tb2.Value)
.Range("B" & freerownum & ":B" & (freerownum + 2)).Value = Tb1A.Value
.Range("C" & freerownum & ":C" & (freerownum + 2)).Value.Value = Tb2A.Value
.Range("D" & freerownum).Value = Tb4.Value
.Range("E" & freerownum).Value = Tb5.Value
.Range("F" & freerownum).Value = Tb6.Value
.Range("G" & freerownum).Value = Tb7.Value
.Range("H" & freerownum).Value = Tb8.Value
.Range("I" & freerownum).Value = Tb9.Value
.Range("J" & freerownum).Value = Tb10.Value
.Range("K" & freerownum).Value = Tb11.Value
.Range("D" & (freerownum + 1)).Value = Tb12.Value
.Range("E" & (freerownum + 1)).Value = Tb13.Value
.Range("F" & (freerownum + 1)).Value = Tb14.Value
.Range("G" & (freerownum + 1)).Value = Tb15.Value
.Range("H" & (freerownum + 1)).Value = Tb16.Value
.Range("I" & (freerownum + 1)).Value = Tb17.Value
.Range("J" & (freerownum + 1)).Value = Tb18.Value
.Range("K" & (freerownum + 1)).Value = Tb19.Value
.Range("D" & (freerownum + 2)).Value = Tb20.Value
.Range("E" & (freerownum + 2)).Value = Tb21.Value
.Range("F" & (freerownum + 2)).Value = Tb22.Value
.Range("G" & (freerownum + 2)).Value = Tb23.Value
.Range("H" & (freerownum + 2)).Value = Tb24.Value
.Range("I" & (freerownum + 2)).Value = Tb25.Value
.Range("J" & (freerownum + 2)).Value = Tb26.Value
.Range("K" & (freerownum + 2)).Value = Tb27.Value
End With
End Sub
 
S

Sue

Hi Joel

Sorry for the delay never received an email telling me there was answer to
my question. Getting a runtime error message 1004 on the line below if I
comment it out the code runs but starts in the heading Row.

endrownum = .Range("A2").End(xlDown).Row

Any suggestions much appreciated.
 
J

Joel

Do you have a worksheet called New?

Sue said:
Hi Joel

Sorry for the delay never received an email telling me there was answer to
my question. Getting a runtime error message 1004 on the line below if I
comment it out the code runs but starts in the heading Row.

endrownum = .Range("A2").End(xlDown).Row

Any suggestions much appreciated.
 
S

Sue

Hi Joel

I have a worksheet named "New" and when the line is commented out it puts
the info on the header row on sheet "New"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top