PDA

View Full Version : Solved: Userform to Spreadsheet help



Emoncada
01-17-2013, 10:50 AM
I have the following code


Private Sub CmdUpdate_Click()

'Add New Button to your UserForm so you press to Apply Data to Sheet
Dim RowNext1 As Integer, RowNext2 As Integer, RowNext3 As Integer
Dim RowNext4 As Integer, RowNext5 As Integer, RowNext6 As Integer
' etc to Dim RowNext15 As Integer....

'last row of data puls one row
RowNext1 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 1
RowNext2 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 2
RowNext3 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 3
RowNext4 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 4
RowNext5 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 5
RowNext6 = Worksheets("Unreported").Cells(65536, 1).End(xlUp).Row + 6
'etc to RowNext15.....

With Worksheets("Unreported")

If UserForm2.chkPart78.Value = True Then

.Cells(RowNext1, 1) = Date
.Cells(RowNext1, 2) = UserForm2.chkPart78.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext1, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext1, 4) = UserForm2.LblPartition.Caption
.Cells(RowNext1, 5) = "Type 5"
.Cells(RowNext1, 6) = Environ("Username")
End If

If UserForm2.chkFirm14.Value = True Then

.Cells(RowNext2, 1) = Date
.Cells(RowNext2, 2) = UserForm2.chkFirm14.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext2, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext2, 4) = UserForm2.LblFirmware.Caption
.Cells(RowNext2, 5) = "Type 5"
.Cells(RowNext2, 6) = Environ("Username")

'etc.....



This works perfect transferring the data to the spreadsheet, but I need to modify it for one item. I added a txtbox for the user to enter a quantity when they select chkfirm14.

So I need something like this


if Userform2.chkfirm14.value = True Then

j = UserForm2.TxtDriveQty.Value

For Counter = 1 To j

.Cells(RowNext2, 1) = Date
.Cells(RowNext2, 2) = UserForm2.chkFirm14.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext2, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext2, 4) = UserForm2.LblFirmware.Caption
.Cells(RowNext2, 5) = "Type 5"
.Cells(RowNext2, 6) = Environ("Username")

Next Counter


Problem with this is (RowNext2) is just 1 line so no matter the qty in txtDriveQty it just places 1 line.

I need something like RowNext2 + 1 for Counter.

Any Ideas?

Tommy
01-17-2013, 11:27 AM
Try this maybe?

.Cells(RowNext2 + counter, 1) = Date
.Cells(RowNext2 + counter, 2) = UserForm2.chkFirm14.Caption & " - " & UserForm2.CmbModel.Value
.Cells(RowNext2 + counter, 3) = UserForm2.TxtSerial.Value
.Cells(RowNext2 + counter, 4) = UserForm2.LblFirmware.Caption
.Cells(RowNext2 + counter, 5) = "Type 5"
.Cells(RowNext2 + counter, 6) = Environ("Username")

Emoncada
01-17-2013, 11:55 AM
That worked Tommy, Thanks for the help.