|
Userform problem?
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 -- Many Thanks Sue |
Userform problem?
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 "Sue" wrote: 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 -- Many Thanks Sue |
Userform problem?
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. -- Many Thanks Sue "Joel" wrote: 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 "Sue" wrote: 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 -- Many Thanks Sue |
Userform problem?
Do you have a worksheet called New?
"Sue" wrote: 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. -- Many Thanks Sue "Joel" wrote: 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 "Sue" wrote: 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 -- Many Thanks Sue |
Userform problem?
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" -- Many Thanks Sue "Joel" wrote: Do you have a worksheet called New? "Sue" wrote: 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. -- Many Thanks Sue "Joel" wrote: 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 "Sue" wrote: 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 -- Many Thanks Sue |
| All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com