Help with Slow Running Code

[Rupert Bennett]

The code below is used to transfer information from a userform to two worksheets. This is a system to record invoices. The code does what it is supposed to do, but it takes extremely long (90 seconds to two minutes) to update the worksheets. Can someone look at my code please and tell me what, if anything ,I can do to get it to run faster?
Thanks for your help.
Rupert

It may be important to say that I am still using Excel 2003.

Private Sub cmdUpdate_Click()
‘Verify that all necessary information is entered

If TextBox53.Value = "" Then
MsgBox "You MUST enter an Accounting Date. Please enter in the format mm/dd/yyyy"

Exit Sub
End If

If cboVendor.Value = "" Then
MsgBox "Please select a Vendor Code"

Exit Sub
End If

If txtInvNumber.Value = "" Then
MsgBox "You did not enter an invoice number"

Exit Sub
End If

If txtInvDate.Value = "" Then

MsgBox "Please enter the invoice date"

Exit Sub
End If

If txtInvTotal.Value = "" Then
MsgBox "You did not enter the invoice total"

Exit Sub
End If

If txtDescription.Text = "" Then
MsgBox "Please enter a description"

Exit Sub
End If

If txtBalance.Value <> 0 Then
MsgBox "Invoice distribution does not match invoice total."

Exit Sub
End If
‘If all required information is entered and distribution matches the invoice total then
‘ update the "Entry" page and the Invoice database “InvoiceDb”

Dim LastRow As Range
Dim LastRow2 As Range
Set LastRow = Sheets("Entry").Range("D65535").End(xlUp)
Application.ScreenUpdating = False
Sheets("Entry").Range("D5").Value = TextBox53.Value

With LastRow
.Offset(1, 0) = ComboBox1.Value
.Offset(1, 2) = TextBox3.Value
.Offset(1, 3) = TextBox5.Value
.Offset(1, 4) = TextBox7.Value
.Offset(1, 5) = TextBox70.Value
.Offset(1, 6) = TextBox9.Value
.Offset(1, 8) = ComboBox2.Value
.Offset(1, 10) = TextBox13.Value
.Offset(1, 11) = ComboBox3.Value
.Offset(1, 13) = TextBox16.Value
.Offset(1, 14) = ComboBox4.Value
.Offset(1, 16) = TextBox19.Value
.Offset(1, 17) = ComboBox5.Value
.Offset(1, 19) = TextBox22.Value
.Offset(1, 20) = ComboBox6.Value
.Offset(1, 22) = TextBox25.Value
.Offset(1, 23) = ComboBox7.Value
.Offset(1, 25) = TextBox28.Value
.Offset(1, 26) = ComboBox8.Value
.Offset(1, 28) = TextBox31.Value
.Offset(1, 29) = ComboBox9.Value
.Offset(1, 31) = TextBox34.Value
.Offset(1, 32) = ComboBox10.Value
.Offset(1, 34) = TextBox37.Value
.Offset(1, 35) = ComboBox11.Value
.Offset(1, 37) = TextBox40.Value 

  End With
‘=========================================

Set LastRow2 = Sheets("InvoiceDb").Range("A65535").End(xlUp)

Sheets(“InvoiceDb”).Unprotect Password:="Some Password Here"
With LastRow2

.Offset(1, 0) = TextBox53.Value
.Offset(1, 1) = ComboBox1.Value
.Offset(1, 2) = TextBox3.Value
.Offset(1, 3) = TextBox5.Value
.Offset(1, 4) = TextBox7.Value
.Offset(1, 5) = TextBox9.Value
.Offset(1, 6) = TextBox55.Value
End With

Sheets(“InvoiceDb”).Protect Password:="Some Password Here"
  
Unload UserForm1


Application.ScreenUpdating = True

End If

Dim Prompt$
Dim Reply
Prompt$ = "Would you like to post another invoice?"
Reply = MsgBox(Prompt$, vbYesNo)
If Reply = vbYes Then
    UserForm1.Show

Else

Sheets("Entry").Select
Sheets("Entry").Range("E7").Select
Application.ScreenUpdating = True
Exit Sub

End If

End Sub

 

[Ruddles]

Unless I'm being particularly dim, I can't see what your code could possibly be doing for 90+ seconds. It's not as if it's looping or anything.

You could try turning calculation to manual just whilst it runs and set it back to automatic when it's finished.

Failing that, my recommendation would be to sprinkle a few of these through the code:-

debug.print "Started at "; format(now(),"hh:nn:ss")
debug.print "Got to point #1 at "; format(now(),"hh:nn:ss")
debug.print "Got to point #2 at "; format(now(),"hh:nn:ss")
debug.print "Got to point #3 at "; format(now(),"hh:nn:ss")
debug.print "Ended at "; format(now(),"hh:nn:ss")

Then check the Immediate window (Ctrl-G) and try to work out where the longs gaps are, and move the debug.print statements to that piece of code and repeat the process to see if there's anywhere obvious that's causing the delay.

 

[Rupert Bennett]

Thank you, Ruddles, for taking a look at the code. I agree it should not take that long and that is why I was asking for help. I will try your suggestions and see what happens.
Regards,
RB