Efficient User Form vba coding for saving to worksheet

C

Cheryl

Hello,

I was wonder if someone could take a quick glance to see if the way that I am coding saving data once the user data on the user form has been changed is efficient since it takes a long time to save the record (my user's will not have to patience to wait 10 seconds for it to save).. I was thinking maybe there is a better way...


Worksheets("Job Number List").Cells(lCurrentRow, 1).Value = IDChange
Worksheets("Job Number List").Cells(lCurrentRow, 3).Value = txtJobNumber.Text
Worksheets("Job Number List").Cells(lCurrentRow, 4).Value = txtAdjNo.Text
Worksheets("Job Number List").Cells(lCurrentRow, 5).Value = txtDateReceived.Text
Worksheets("Job Number List").Cells(lCurrentRow, 6).Value = txtDateRequired.Text
Worksheets("Job Number List").Cells(lCurrentRow, 7).Value = txtCompany.Text
Worksheets("Job Number List").Cells(lCurrentRow, 8).Value = txtPONumber.Text
Worksheets("Job Number List").Cells(lCurrentRow, 9).Value = txtJobDescription.Text
Worksheets("Job Number List").Cells(lCurrentRow, 10).Value = txtWeight.Text
Worksheets("Job Number List").Cells(lCurrentRow, 11).Value = txtDateCompleted.Text
Worksheets("Job Number List").Cells(lCurrentRow, 12).Value = txtRTNo.Text
Worksheets("Job Number List").Cells(lCurrentRow, 13).Value = txtCrossRef1.Text
Worksheets("Job Number List").Cells(lCurrentRow, 14).Value = txtCrossRef2.Text
Worksheets("Job Number List").Cells(lCurrentRow, 15).Value = txtShrink.Text
Worksheets("Job Number List").Cells(lCurrentRow, 16).Value = txtResinType.Text
Worksheets("Job Number List").Cells(lCurrentRow, 17).Value = txtDesignerName.Text
Worksheets("Job Number List").Cells(lCurrentRow, 18).Value = txtComments.Text
Worksheets("Job Number List").Cells(lCurrentRow, 19).Value = txtExpectedDesignStartDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 20).Value = txtExpectedDesignCompletionDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 21).Value = txtActualCompletionDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 22).Value = txtTeamLeader.Text
Worksheets("Job Number List").Cells(lCurrentRow, 23).Value = txtExpectedShopStartDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 24).Value = txtExpectedShopCompletionDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 25).Value = txtActualShopCompletionDate.Text

'Customer Details
Worksheets("Job Number List").Cells(lCurrentRow, 26).Value = txtPODate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 27).Value = txtLeadTime.Text
Worksheets("Job Number List").Cells(lCurrentRow, 28).Value = txtCustMouldRefNo.Text
Worksheets("Job Number List").Cells(lCurrentRow, 29).Value = txtContactPerson.Text
Worksheets("Job Number List").Cells(lCurrentRow, 30).Value = txtEmail.Text
Worksheets("Job Number List").Cells(lCurrentRow, 31).Value = txtDimensions.Text
Worksheets("Job Number List").Cells(lCurrentRow, 32).Value = txtContactPhoneNo.Text
'Worksheets("Job Number List").Cells(lCurrentRow, 33).Value= txtPackingSlipNo.Text

'Mould Type

If chkMouldTypeProduction = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 34).Value = "Production"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 34).Value = ""
End If
If chkMouldTypePrototype = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 35).Value = "Prototype"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 35).Value = ""
End If
If chkMouldTypeStripperPlate = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 36).Value = "Stripper Plate"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 36).Value = ""
End If
If chkMouldTypeConventional = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 37).Value = "Conventional"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 37).Value = ""
End If
If chkMouldTypeVertical = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 38).Value = "Vertical"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 38).Value = ""
End If
If chkMouldTypeOther = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 39).Value = "Other"
Worksheets("Job Number List").Cells(lCurrentRow, 40).Value = txtMouldTypeOther.Text
Else
Worksheets("Job Number List").Cells(lCurrentRow, 39).Value = ""
Worksheets("Job Number List").Cells(lCurrentRow, 40).Value = ""
End If

etc....

There is a total of 210 fields of information... and although it works.. i think i am doing something not as well as it could be...

Thanks for any input...
 
T

Tom Ogilvy

The only other alternative is to put the information in an array and write
the array once to the row. This would probably be faster than 210 cell
updates.

--
Regards,
Tom Ogilvy

Cheryl said:
Hello,

I was wonder if someone could take a quick glance to see if the way that I
Click to expand...
am coding saving data once the user data on the user form has been changed
is efficient since it takes a long time to save the record (my user's will
not have to patience to wait 10 seconds for it to save).. I was thinking
maybe there is a better way...
Worksheets("Job Number List").Cells(lCurrentRow, 1).Value = IDChange
Worksheets("Job Number List").Cells(lCurrentRow, 3).Value = txtJobNumber.Text
Worksheets("Job Number List").Cells(lCurrentRow, 4).Value = txtAdjNo.Text
Worksheets("Job Number List").Cells(lCurrentRow, 5).Value = txtDateReceived.Text
Worksheets("Job Number List").Cells(lCurrentRow, 6).Value = txtDateRequired.Text
Worksheets("Job Number List").Cells(lCurrentRow, 7).Value = txtCompany.Text
Worksheets("Job Number List").Cells(lCurrentRow, 8).Value = txtPONumber.Text
Worksheets("Job Number List").Cells(lCurrentRow, 9).Value = txtJobDescription.Text
Worksheets("Job Number List").Cells(lCurrentRow, 10).Value = txtWeight.Text
Worksheets("Job Number List").Cells(lCurrentRow, 11).Value = txtDateCompleted.Text
Worksheets("Job Number List").Cells(lCurrentRow, 12).Value = txtRTNo.Text
Worksheets("Job Number List").Cells(lCurrentRow, 13).Value = txtCrossRef1.Text
Worksheets("Job Number List").Cells(lCurrentRow, 14).Value = txtCrossRef2.Text
Worksheets("Job Number List").Cells(lCurrentRow, 15).Value = txtShrink.Text
Worksheets("Job Number List").Cells(lCurrentRow, 16).Value = txtResinType.Text
Worksheets("Job Number List").Cells(lCurrentRow, 17).Value = txtDesignerName.Text
Worksheets("Job Number List").Cells(lCurrentRow, 18).Value = txtComments.Text
Worksheets("Job Number List").Cells(lCurrentRow, 19).Value = txtExpectedDesignStartDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 20).Value = txtExpectedDesignCompletionDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 21).Value = txtActualCompletionDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 22).Value = txtTeamLeader.Text
Worksheets("Job Number List").Cells(lCurrentRow, 23).Value = txtExpectedShopStartDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 24).Value = txtExpectedShopCompletionDate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 25).Value = txtActualShopCompletionDate.Text

'Customer Details
Worksheets("Job Number List").Cells(lCurrentRow, 26).Value = txtPODate.Text
Worksheets("Job Number List").Cells(lCurrentRow, 27).Value = txtLeadTime.Text
Worksheets("Job Number List").Cells(lCurrentRow, 28).Value = txtCustMouldRefNo.Text
Worksheets("Job Number List").Cells(lCurrentRow, 29).Value = txtContactPerson.Text
Worksheets("Job Number List").Cells(lCurrentRow, 30).Value = txtEmail.Text
Worksheets("Job Number List").Cells(lCurrentRow, 31).Value = txtDimensions.Text
Worksheets("Job Number List").Cells(lCurrentRow, 32).Value = txtContactPhoneNo.Text
'Worksheets("Job Number List").Cells(lCurrentRow, 33).Value= txtPackingSlipNo.Text

'Mould Type

If chkMouldTypeProduction = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 34).Value = "Production"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 34).Value = ""
End If
If chkMouldTypePrototype = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 35).Value = "Prototype"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 35).Value = ""
End If
If chkMouldTypeStripperPlate = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 36).Value = "Stripper Plate"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 36).Value = ""
End If
If chkMouldTypeConventional = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 37).Value = "Conventional"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 37).Value = ""
End If
If chkMouldTypeVertical = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 38).Value = "Vertical"
Else
Worksheets("Job Number List").Cells(lCurrentRow, 38).Value = ""
End If
If chkMouldTypeOther = True Then
Worksheets("Job Number List").Cells(lCurrentRow, 39).Value = "Other"
Worksheets("Job Number List").Cells(lCurrentRow, 40).Value = txtMouldTypeOther.Text
Else
Worksheets("Job Number List").Cells(lCurrentRow, 39).Value = ""
Worksheets("Job Number List").Cells(lCurrentRow, 40).Value = ""
End If

etc....

There is a total of 210 fields of information... and although it works.. i
Click to expand...
think i am doing something not as well as it could be...
 
Top