-
Solved: Userform to Spreadsheet help
I have the following code
[vba]
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.....
[/vba]
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
[vba]
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
[/vba]
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?
-
Try this maybe?
[VBA]
.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") [/VBA]
-
That worked Tommy, Thanks for the help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules