Emoncada
12-27-2007, 10:10 AM
I have this code I am trying to work on
Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next
For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
.Cells(RowNext + i, 11) = CmbBoxLocation.Value
.Cells(RowNext + i, 12) = TxtShippedBy.Text
.Cells(RowNext + i, 13) = TxtComments.Text
If Me.ChkBoxComments = True Then .Cells(RowNext + i, 14) = "YES"
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 15) = "YES"
End With
Next
If UserForm2.CmbBoxDesc1.Value <> "" Then
For i = 1 To j
With UserForm2
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
.Cells(RowNext + i, 11) = CmbBoxLocation.Value
.Cells(RowNext + i, 12) = TxtShippedBy.Text
.Cells(RowNext + i, 13) = TxtComments.Text
If Me.ChkBoxComments = True Then .Cells(RowNext + i, 14) = "YES"
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 15) = "YES"
End With
End With
Next
Basically want to call this when the save button is clicked via Userform1 or UserForm2 and then Possibly a UserForm3.
I am trying to test if there is a value in Userform2.CmbDesc1 Then for it to transfer all the data on that form to the spreadsheet the same way it does with Userform1.
I tried somethings but it's not working. Any Help would be great.
Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next
For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
.Cells(RowNext + i, 11) = CmbBoxLocation.Value
.Cells(RowNext + i, 12) = TxtShippedBy.Text
.Cells(RowNext + i, 13) = TxtComments.Text
If Me.ChkBoxComments = True Then .Cells(RowNext + i, 14) = "YES"
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 15) = "YES"
End With
Next
If UserForm2.CmbBoxDesc1.Value <> "" Then
For i = 1 To j
With UserForm2
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
.Cells(RowNext + i, 11) = CmbBoxLocation.Value
.Cells(RowNext + i, 12) = TxtShippedBy.Text
.Cells(RowNext + i, 13) = TxtComments.Text
If Me.ChkBoxComments = True Then .Cells(RowNext + i, 14) = "YES"
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 15) = "YES"
End With
End With
Next
Basically want to call this when the save button is clicked via Userform1 or UserForm2 and then Possibly a UserForm3.
I am trying to test if there is a value in Userform2.CmbDesc1 Then for it to transfer all the data on that form to the spreadsheet the same way it does with Userform1.
I tried somethings but it's not working. Any Help would be great.