| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer userform contents to Sheet
I have an userform with 60 textboxes (12 lines of 5) that which contents, if
non blank , I would like to place into a sheet in the same order and then be able to keep doing it starting at the next available row. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfer userform contents to Sheet
Not sure if you are using worksheet forms or VBA userfroms. Here is some
sample code that may help. Sub saveuserform() Const SaveSheet = "Summary" With Sheets(SaveSheet) LastRow = .Cells(Rows.Count, "A").End(xlUp).Row If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then RowOff = 0 Else RowOff = LastRow End If .Range("A1").Offset(RowOff, 0) = userform1.textbox1.Text .Range("B1").Offset(RowOff, 0) = userform1.textbox2.Text .Range("C1").Offset(RowOff, 0) = userform1.textbox3.Text .Range("D1").Offset(RowOff, 0) = userform1.textbox4.Text .Range("E1").Offset(RowOff, 0) = userform1.textbox5.Text .Range("A2").Offset(RowOff, 0) = userform1.textbox6.Text .Range("B2").Offset(RowOff, 0) = userform1.textbox7.Text .Range("C2").Offset(RowOff, 0) = userform1.textbox8.Text .Range("D2").Offset(RowOff, 0) = userform1.textbox9.Text .Range("E2").Offset(RowOff, 0) = userform1.textbox10.Text .Range("A3").Offset(RowOff, 0) = userform1.textbox11.Text .Range("B3").Offset(RowOff, 0) = userform1.textbox12.Text .Range("C3").Offset(RowOff, 0) = userform1.textbox13.Text .Range("D3").Offset(RowOff, 0) = userform1.textbox14.Text .Range("E3").Offset(RowOff, 0) = userform1.textbox15.Text .Range("A4").Offset(RowOff, 0) = userform1.textbox16.Text .Range("B4").Offset(RowOff, 0) = userform1.textbox17.Text .Range("C4").Offset(RowOff, 0) = userform1.textbox18.Text .Range("D4").Offset(RowOff, 0) = userform1.textbox19.Text .Range("E4").Offset(RowOff, 0) = userform1.textbox20.Text .Range("A5").Offset(RowOff, 0) = userform1.textbox21.Text .Range("B5").Offset(RowOff, 0) = userform1.textbox22.Text .Range("C5").Offset(RowOff, 0) = userform1.textbox23.Text .Range("D5").Offset(RowOff, 0) = userform1.textbox24.Text .Range("E5").Offset(RowOff, 0) = userform1.textbox25.Text .Range("A6").Offset(RowOff, 0) = userform1.textbox26.Text .Range("B6").Offset(RowOff, 0) = userform1.textbox27.Text .Range("C6").Offset(RowOff, 0) = userform1.textbox28.Text .Range("D6").Offset(RowOff, 0) = userform1.textbox29.Text .Range("E6").Offset(RowOff, 0) = userform1.textbox30.Text .Range("A7").Offset(RowOff, 0) = userform1.textbox31.Text .Range("B7").Offset(RowOff, 0) = userform1.textbox32.Text .Range("C7").Offset(RowOff, 0) = userform1.textbox33.Text .Range("D7").Offset(RowOff, 0) = userform1.textbox34.Text .Range("E7").Offset(RowOff, 0) = userform1.textbox35.Text .Range("A8").Offset(RowOff, 0) = userform1.textbox36.Text .Range("B8").Offset(RowOff, 0) = userform1.textbox37.Text .Range("C8").Offset(RowOff, 0) = userform1.textbox38.Text .Range("D8").Offset(RowOff, 0) = userform1.textbox39.Text .Range("E8").Offset(RowOff, 0) = userform1.textbox40.Text .Range("A9").Offset(RowOff, 0) = userform1.textbox41.Text .Range("B9").Offset(RowOff, 0) = userform1.textbox42.Text .Range("C9").Offset(RowOff, 0) = userform1.textbox43.Text .Range("D9").Offset(RowOff, 0) = userform1.textbox44.Text .Range("E9").Offset(RowOff, 0) = userform1.textbox45.Text .Range("A10").Offset(RowOff, 0) = userform1.textbox46.Text .Range("B10").Offset(RowOff, 0) = userform1.textbox47.Text .Range("C10").Offset(RowOff, 0) = userform1.textbox48.Text .Range("D10").Offset(RowOff, 0) = userform1.textbox49.Text .Range("E10").Offset(RowOff, 0) = userform1.textbox50.Text .Range("A11").Offset(RowOff, 0) = userform1.textbox51.Text .Range("B11").Offset(RowOff, 0) = userform1.textbox52.Text .Range("C11").Offset(RowOff, 0) = userform1.textbox53.Text .Range("D11").Offset(RowOff, 0) = userform1.textbox54.Text .Range("E11").Offset(RowOff, 0) = userform1.textbox55.Text .Range("A12").Offset(RowOff, 0) = userform1.textbox56.Text .Range("B12").Offset(RowOff, 0) = userform1.textbox57.Text .Range("C12").Offset(RowOff, 0) = userform1.textbox58.Text .Range("D12").Offset(RowOff, 0) = userform1.textbox59.Text .Range("E12").Offset(RowOff, 0) = userform1.textbox60.Text End With End Sub 'another method if using worksheets Sub saveuserform1() Const SaveSheet = "Summary" With Sheets(SaveSheet) LastRow = .Cells(Rows.Count, "A").End(xlUp).Row If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then RowOff = 0 Else RowOff = LastRow End If Sheets("Old Sheets").Range("A1:E12").Copy _ Destination:=.Range("A1").Offset(RowOff, 0) End With End Sub "LuisE" wrote: I have an userform with 60 textboxes (12 lines of 5) that which contents, if non blank , I would like to place into a sheet in the same order and then be able to keep doing it starting at the next available row. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfer userform contents to Sheet
Works great. Thank you.
"Joel" wrote: Not sure if you are using worksheet forms or VBA userfroms. Here is some sample code that may help. Sub saveuserform() Const SaveSheet = "Summary" With Sheets(SaveSheet) LastRow = .Cells(Rows.Count, "A").End(xlUp).Row If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then RowOff = 0 Else RowOff = LastRow End If .Range("A1").Offset(RowOff, 0) = userform1.textbox1.Text .Range("B1").Offset(RowOff, 0) = userform1.textbox2.Text .Range("C1").Offset(RowOff, 0) = userform1.textbox3.Text .Range("D1").Offset(RowOff, 0) = userform1.textbox4.Text .Range("E1").Offset(RowOff, 0) = userform1.textbox5.Text .Range("A2").Offset(RowOff, 0) = userform1.textbox6.Text .Range("B2").Offset(RowOff, 0) = userform1.textbox7.Text .Range("C2").Offset(RowOff, 0) = userform1.textbox8.Text .Range("D2").Offset(RowOff, 0) = userform1.textbox9.Text .Range("E2").Offset(RowOff, 0) = userform1.textbox10.Text .Range("A3").Offset(RowOff, 0) = userform1.textbox11.Text .Range("B3").Offset(RowOff, 0) = userform1.textbox12.Text .Range("C3").Offset(RowOff, 0) = userform1.textbox13.Text .Range("D3").Offset(RowOff, 0) = userform1.textbox14.Text .Range("E3").Offset(RowOff, 0) = userform1.textbox15.Text .Range("A4").Offset(RowOff, 0) = userform1.textbox16.Text .Range("B4").Offset(RowOff, 0) = userform1.textbox17.Text .Range("C4").Offset(RowOff, 0) = userform1.textbox18.Text .Range("D4").Offset(RowOff, 0) = userform1.textbox19.Text .Range("E4").Offset(RowOff, 0) = userform1.textbox20.Text .Range("A5").Offset(RowOff, 0) = userform1.textbox21.Text .Range("B5").Offset(RowOff, 0) = userform1.textbox22.Text .Range("C5").Offset(RowOff, 0) = userform1.textbox23.Text .Range("D5").Offset(RowOff, 0) = userform1.textbox24.Text .Range("E5").Offset(RowOff, 0) = userform1.textbox25.Text .Range("A6").Offset(RowOff, 0) = userform1.textbox26.Text .Range("B6").Offset(RowOff, 0) = userform1.textbox27.Text .Range("C6").Offset(RowOff, 0) = userform1.textbox28.Text .Range("D6").Offset(RowOff, 0) = userform1.textbox29.Text .Range("E6").Offset(RowOff, 0) = userform1.textbox30.Text .Range("A7").Offset(RowOff, 0) = userform1.textbox31.Text .Range("B7").Offset(RowOff, 0) = userform1.textbox32.Text .Range("C7").Offset(RowOff, 0) = userform1.textbox33.Text .Range("D7").Offset(RowOff, 0) = userform1.textbox34.Text .Range("E7").Offset(RowOff, 0) = userform1.textbox35.Text .Range("A8").Offset(RowOff, 0) = userform1.textbox36.Text .Range("B8").Offset(RowOff, 0) = userform1.textbox37.Text .Range("C8").Offset(RowOff, 0) = userform1.textbox38.Text .Range("D8").Offset(RowOff, 0) = userform1.textbox39.Text .Range("E8").Offset(RowOff, 0) = userform1.textbox40.Text .Range("A9").Offset(RowOff, 0) = userform1.textbox41.Text .Range("B9").Offset(RowOff, 0) = userform1.textbox42.Text .Range("C9").Offset(RowOff, 0) = userform1.textbox43.Text .Range("D9").Offset(RowOff, 0) = userform1.textbox44.Text .Range("E9").Offset(RowOff, 0) = userform1.textbox45.Text .Range("A10").Offset(RowOff, 0) = userform1.textbox46.Text .Range("B10").Offset(RowOff, 0) = userform1.textbox47.Text .Range("C10").Offset(RowOff, 0) = userform1.textbox48.Text .Range("D10").Offset(RowOff, 0) = userform1.textbox49.Text .Range("E10").Offset(RowOff, 0) = userform1.textbox50.Text .Range("A11").Offset(RowOff, 0) = userform1.textbox51.Text .Range("B11").Offset(RowOff, 0) = userform1.textbox52.Text .Range("C11").Offset(RowOff, 0) = userform1.textbox53.Text .Range("D11").Offset(RowOff, 0) = userform1.textbox54.Text .Range("E11").Offset(RowOff, 0) = userform1.textbox55.Text .Range("A12").Offset(RowOff, 0) = userform1.textbox56.Text .Range("B12").Offset(RowOff, 0) = userform1.textbox57.Text .Range("C12").Offset(RowOff, 0) = userform1.textbox58.Text .Range("D12").Offset(RowOff, 0) = userform1.textbox59.Text .Range("E12").Offset(RowOff, 0) = userform1.textbox60.Text End With End Sub 'another method if using worksheets Sub saveuserform1() Const SaveSheet = "Summary" With Sheets(SaveSheet) LastRow = .Cells(Rows.Count, "A").End(xlUp).Row If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then RowOff = 0 Else RowOff = LastRow End If Sheets("Old Sheets").Range("A1:E12").Copy _ Destination:=.Range("A1").Offset(RowOff, 0) End With End Sub "LuisE" wrote: I have an userform with 60 textboxes (12 lines of 5) that which contents, if non blank , I would like to place into a sheet in the same order and then be able to keep doing it starting at the next available row. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| transfer contents from a cell in previous sheet if the sheet is a | New Users to Excel | |||
| Transfer Cell Contents to Tab | Excel Discussion (Misc queries) | |||
| Automatically transfer cell contents | Excel Programming | |||
| Automatically transfer cell contents | Excel Worksheet Functions | |||
Linear Mode
