|
Efficient User Form vba coding for saving to worksheet
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... |
Efficient User Form vba coding for saving to worksheet
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" wrote in message ... 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... |
| All times are GMT +1. The time now is 11:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com