Run code after set period of time

[EssKayKay]

Hello,
I’m not sure this is even possible but I’ll start here.

I have a routine (cmdPmtMade_Click) that for the most part works fine. However, as the routine is run numerous times the whole process begins to slow down. Over time, what initially takes a couple seconds to run can progress to nearly a minute. I have tried to see what is causing this but was unsuccessful in my review.

I have a second routine that basically copies all the data from one worksheet to another then re-saves the file under the original name. After running this “Repair” sub, the “PmtMade” sub (and others) works great for awhile until it again begins to slow down. If I rerun the REPAIR, all is fine again for some time.

What I was thinking of is some way that if the original routine (PmtMade) takes more that X number of seconds, a message box would appear prompting the user to run the “Repair” routine to speed up the process.

So, my question – Is there some command that when “PmtMade” button is clicked after a specified time (say 10 seconds), will trigger some other code or message box ? I tried Application.OnTime but could not get that to work (I probably had something set wrong).

Thanks for viewing,
Steve K.

 

[Skyybot]

I've changed the placement of the Refresh call. I think the above will not work.

Private Sub cmdPmtMade_Click()

ThisWorkbook.x = ThisWorkbook.x + 1 



If Sheets("Amortize").Columns(17).Hidden = False Then 'MsgBox "Visible"
Range("A4,H5").Select
Range("H5").Activate
End If

Application.ScreenUpdating = False
Application.EnableEvents = True

Range("OneTime") = "---"
Range("TempCell") = "Pmt_Made"

Call OneTimePmt1
Call OneTimePmt2

If Range("F6") <= 0 Or Range("F7") <= 0 Or Range("F8") <= 0 Or Range("F10") <= 0 Then
  MsgBox " Missing Loan Information." & vbNewLine & _
  " Enter Loan Info criteria. . .", , " - Loan Info -"
  If Range("F10") = "" Then Range("F10").Select
  If Range("F8") = "" Then Range("F8").Select
  If Range("F7") = "" Then Range("F7").Select
  If Range("F6") = "" Then Range("F6").Select
Protect_It

If ThisWorkbook.x = 5 Then Refresh_ALL   'This reference may need to change depending on code placement

Exit Sub
End If

'======== Added 0/06/24 ==========
If Range("M34") > 0 Then Formulas2Values '<=== check this for editing
  PaymentSetup
  UpdateOneTimePmt
  ResetPITI
  OneTimeDATE
Range("M31").End(xlDown).Offset(1, 0).Select

'--- Set selected Pmt_Date at screen center ---
If Range("M45") > 0 Then
  If ActiveCell.Row > 13 Then ActiveWindow.ScrollRow = ActiveCell.Row - 13
     Range("M2035").End(xlUp).Offset(1, 0).Select
  End If

UnProtect_It
Range("F29").ClearContents
Range("F29") = "Ready"

If ThisWorkbook.x = 5 Then Refresh_ALL   'This reference may need to change depending on code placement

Application.EnableEvents = True
Application.ScreenUpdating = true

End Sub

 

[EssKayKay]

Thank you Skybot for your quick response and support. This is working quite well. I have a little to add but I am very close. If I run into problems I'm sure I will be back but this looks promising.

Side Note - actually, I believe both routines worked fine but I settled on your second revision.

Again, much appreciated. . .
Steve K.

 

[EssKayKay]

I was having a problem marking this as solved but I think I got it.
Once again, thanks. . .

 

[EssKayKay]

Hello all,
I am back on this issue once again.

SkyBot’s routine works pretty good (thank you SkyBot). However, I think what would work better for me is if there is some other way to prompt me (or better, run another macro) after a set time length, not the number of times the original routine is run.

What I have in mind is some way to determine how long a sub routine takes to run including any calls. If so, then post that time to some cell.
As example, let’s say the routine took 10.5 seconds to complete, then update cell "P1" with 10.5. If that’s possible, I believe I could make this work.

Again, thanks for viewing,
Steve K.

 

[EssKayKay]

I think I found what I was looking for. Here's the link:

VBA Code To Calculate How Long Your Macro Takes To Run​

There are various scenarios where you may want to calculate the time it takes for your VBA code to run. I often do this when trying to test various ways to code a procedure. I like to see which method gives me a faster run time.

www.thespreadsheetguru.com

Sub CalculateRunTime()      '--- in seconds ---
'PURPOSE: Determine how many seconds it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
   StartTime = Timer

'*****************************
    'Insert Your Code Here...

'*****************************

'Determine how many seconds code took to run
    SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
   MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
   Range("P1") = SecondsElapsed
End Sub

Thank you all. Hopefully this does what I want. Any other problems, I'm sure I'll be back.
Steve K.