PDA

View Full Version : Solved: Form to Spreadsheet problem



Emoncada
05-15-2007, 02:29 PM
Ok guru's I have this form I've been working on thanks to the help of a lot of you great people. I am stuck now on the most important part. I need to transfer the data from the form to the spreadsheet. My request is for assistance in the part that I think might need some type of "loop".
I am attaching the spreadsheet w/form and .txt file.
I need it to repeat the following .value's for each CmbBoxDesc with a value.

So that means column A:C & H:J would need to be repeated everytime there is a item in one of the CmbBoxDesc.

Example:
So If there is 3 items
CmbBoxDesc1 = "2500"
CmbBoxDesc2 = "HP NC8430"
CmbBoxDesc3 = "10FT. PATCH CABLE"
Then A2:A4 would be the same value of TxtOrdNum.Value
Then B2:B4 would be the same value of TxtShipDate.Value
Then C2:C4 would be the same Value of LblShipVia.Caption
Then H2:H4 would be the same value of CmbBoxProject.Value
Then I2:I4 would be the same value of LblRacf.Caption
Then J2:J4 would be the same value of CmbBoxClientName.Value

Hope this kind of explains what I need. I have this script to start
Private Sub CmdPrintSave_Click()
Dim RowNext As Integer

'last row of data puls one row
RowNext = Worksheets("Packing Slip Pim").Cells(65536, 1).End(xlUp).Row + 1

'Cells(Row Number,Column Number)
With Worksheets("Packing Slip Pim")
.Cells(RowNext, 1) = UCase(TxtOrderNum.Value)
.Cells(RowNext, 2) = TxtShipDate.Caption
.Cells(RowNext, 3) = LblShipVia.Caption
.Cells(RowNext, 4) = UCase(TxtTrack1.Value)

Then im stuck. Any Help would be Great.

Emoncada
05-15-2007, 02:32 PM
Sorry forgot the zip file.

mdmackillop
05-15-2007, 02:49 PM
Private Sub CommandButton1_Click()
Dim i As Long, j As Long
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next
Range("A2").Resize(j) = TxtOrdNum.Value
'etc.
End Sub

Emoncada
05-15-2007, 03:30 PM
Sorry mdmack I guess I would put that into the
CmdPrintSave_Click() right? Can you put it where it should in the vb so I can understand a little better sorry.

Emoncada
05-16-2007, 08:44 AM
Can I do something like this

Private Sub CmdPrintSave_Click()
Dim RowNext As Integer

'last row of data puls one row
RowNext = Worksheets("Packing Slip Pim").Cells(65536, 1).End(xlUp).Row + 1

'Cells(Row Number,Column Number)
With Worksheets("Packing Slip Pim")
.Cells(RowNext, 1) = UCase(TxtOrderNum.Value)
.Cells(RowNext, 2) = TxtShipDate.Caption
.Cells(RowNext, 3) = LblShipVia.Caption
.Cells(RowNext, 4) = UCase(TxtTrack1.Value)
.Cells(RowNext, 5) = TxtSN1.Value
.Cells(RowNext, 6) = CmbBoxDesc1.Value
.Cells(RowNext, 7) = TxtQua1.Value
.Cells(RowNext, 8) = CmbBoxProject.Value
.Cells(RowNext, 9) = LblRacf.Caption
.Cells(RowNext, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext, 11) = "YES"
'------------------------------------------------------------------------
If CmbBoxDesc2.Value <> "" Then
.Cells(RowNext, 1) = UCase(TxtOrderNum.Value)
.Cells(RowNext, 2) = TxtShipDate.Caption
.Cells(RowNext, 3) = LblShipVia.Caption
'------------------------Only Changed This Part---------------------------------
.Cells(RowNext, 4) = UCase(TxtTrack2.Value)
.Cells(RowNext, 5) = TxtSN2.Value
.Cells(RowNext, 6) = CmbBoxDesc2.Value
.Cells(RowNext, 7) = TxtQua2.Value
'-------------------------------------------------------------------------
.Cells(RowNext, 8) = CmbBoxProject.Value
.Cells(RowNext, 9) = LblRacf.Caption
.Cells(RowNext, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext, 11) = "YES"
Else
End With
'-------------------------------------------------------------------------
If CmbBoxDesc3.Value <> "" Then
.Cells(RowNext, 1) = UCase(TxtOrderNum.Value)
.Cells(RowNext, 2) = TxtShipDate.Caption
.Cells(RowNext, 3) = LblShipVia.Caption
'------------------------Only Changed This Part-----------------------------
.Cells(RowNext, 4) = UCase(TxtTrack3.Value)
.Cells(RowNext, 5) = TxtSN3.Value
.Cells(RowNext, 6) = CmbBoxDesc3.Value
.Cells(RowNext, 7) = TxtQua3.Value
'---------------------------------------------------------------------
.Cells(RowNext, 8) = CmbBoxProject.Value
.Cells(RowNext, 9) = LblRacf.Caption
.Cells(RowNext, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext, 11) = "YES"
Else
End With
'etc
Can something like this work or do something like this in a shorter script

mdmackillop
05-16-2007, 10:59 AM
Private Sub CmdPrintSave_Click()
Dim RowNext As Long, 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
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 11) = "YES"
End With
Next
End Sub

Emoncada
05-16-2007, 02:01 PM
It looks good MdMack. I will further test. But Looks Great So Far. Thanks.

Emoncada
05-16-2007, 02:32 PM
MdMack Im getting a run-time error '9'
Subscript out of range

It highlights this line
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row

Any Ideas?

mdmackillop
05-16-2007, 02:48 PM
Try Dim RowNext as Long.
Integer can be exceeded by the row number. Check Interger Data Type in help.