Refresh On Open - But allow time to cancel macro

[johnny51981]

I have a workbook that I need to run in the later hours to have ready in the morning, due to its lengthy refresh time.

The Scheduled Task is set up and tested with success.
The VBA to run the needed macros on open is set up and tested, also successfully.

Now, what I am trying to figure out is to allow for updating of the workbook's structure/criteria whenever needed, but giving myself or other users the ability to cancel the lengthy refresh.

Would someone be able to assist in providing the VBA on how to allow for a MsgBox to appear with any kind of Yes/No that will cancel the procedure if No is selected, but if nothing is selected within a minute and a half, then it defaults to Yes and then initiates the lengthy refresh?

Here is the MsgBox VBA that I have as of now:

Sub MsgBoxBeforeRunning()

Dim answer As Integer
 
    answer = MsgBox("Would you like to continue with the data refresh?", vbQuestion + vbYesNo)
    If answer = vbNo Then
    Exit Sub
    
    Else
    Application.Wait (Now + TimeValue("0:01:30"))
    Exit Sub

    End If
    
End Sub

 

[Jeffrey Mahoney]

Wait will pause everything. Not as useful as .OnTime like this:
Application.OnTime Now() + TimeValue("00:00:01"), "ContinueRunning", , Schedule:=True

Also you can use DOEvents to let the system finish needed tasks and process mouse clicks and keyboard presses. If you create a STOP button that changes a public value from False to True and use DOEvents in your loops, you can check if the macro needs to stop. In a standard module:
Public StopThis as boolean

For the button code

Private Sub StopThis_btn_Click()

  StopThis = True

End Sub

Does that help?

 

[johnny51981]

Thank you for your response, @Jeffrey Mahoney . But would you mind dumbing it down for me? I'm not sure how to do the Public StopThis as Boolean, even after doing some additional searching after reading what you had to say, its not sticking for me.

 

[Jeffrey Mahoney]

Ok. Let's say you have a macro with a FOR loop and it runs for a long time. Sometimes you need to stop it because of one reason or another. You would need to create a public variable and a button to change that public variable. See below for how I created the button. After creating the button, make sure design mode is on and double click the button. This will take you to VBA in the SHEET module for that sheet. Add the button code I gave you above. Now create a Standard Module if you don't already have one by Right Clicking on the Project or workbook and choosing Insert - Module. Now add the public variable like I did in the snipit below. A Boolean variable accepts either TRUE or FALSE. In your macro and inside the loop, add a line that checks the public variable to see if you want to stop. You need to put DoEvents ahead of the test so it can allow the button click to be processed. I added code so that the test wouldn't only test every 1,000 iterations so it would get slowed down too much.

Sub LongRunningMacro()

  Dim X As Long
  Dim Y As Double
 
  StopThis = False  'Make sure to set this before starting in case you stopped this previously
 
  For X = 1 To 2123456789
    Y = Sqr(2123456789 - X)
   
   
    If X Mod 1000 = 0 Then               'Test every 1000 lines
      DoEvents
      Debug.Print Y
      If StopThis = True Then Exit Sub    '
    End If
 
  Next X
   
End Sub

 

[johnny51981]

Thank you @Jeffrey Mahoney ! I got the Public StopThis as Boolean in the Module, I appreciate the example! I can handle the button creation no problem. But still struggling on where to place things in my VBA.

Here is what I have, and being that I have this set up to run at a specific time (using Task Scheduler), the VBA is in the ThisWorkbook object. Could you have a once over to see where I might be able to place the StopThis? Go easy on me....I'm still very much a novice in my VBA skills.

Private Sub Workbook_Open()
    DoEvents
    ThisWorkbook.Sheets("Settings").Select
    DoEvents
    Call OnOpen
End Sub
Sub OnOpen()

    StopThis = False  'Make sure to set this before starting in case you stopped this previously
    DoEvents
    Call OpenQueriesPane
    Call Calculator_StartTime
    Call Refresh_Queries
    Call ExportCSV
    Call CloseExcel
    If StopThis = True Then Exit Sub    '

End Sub
Sub CancelRefresh()
    If StopThis = True Then Exit Sub '
    
End Sub
Sub OpenQueriesPane()
    Application.CommandBars("Queries and Connections").Visible = True
    Application.CommandBars("Queries and Connections").Width = 700 'Change width as suits.
    DoEvents
End Sub
Sub Refresh_Queries()
    
    ActiveWorkbook.RefreshAll
    Call Calculator_StopTime
    
End Sub
Sub CloseExcel()
    ActiveWorkbook.Save
    Application.DisplayAlerts = False
    Application.Quit
End Sub
Sub Calculator_StartTime()
    Range("D3") = Format(Now, "hh:mm am/pm")
End Sub
Sub Calculator_StopTime()
    Range("D4") = Format(Now, "hh:mm am/pm")
End Sub
Sub ExportCSV()

    Application.ScreenUpdating = False
    
    ChDir ThisWorkbook.Path
    
    Application.DisplayAlerts = False
    
    ThisWorkbook.Sheets("NTP - All Info").Copy
    ActiveWorkbook.SaveAs Filename:="Charter NTP Info.CSV", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

 

[Jeffrey Mahoney]

Here's how I would do it. The ONTIME command waits 10 seconds in the background before calling the OnOpen SUB to give the user time to click the "Stop Macro" button. I put the StopThis tests in between each of the SUBS you are calling in case the user presses the button in between each.

Private Sub Workbook_Open()
    
    ThisWorkbook.Sheets("Settings").Select
    StopThis = False  'Make sure to set this before starting in case you stopped this previously
    DoEvents
    Application.OnTime Now() + TimeValue("00:00:10"), "OnOpen", , Schedule:=True  'Wait ten seconds then run macro
    
End Sub
Sub OnOpen()
    
    DoEvents
    If StopThis = True Then Exit Sub
    Call OpenQueriesPane
    DoEvents
    If StopThis = True Then Exit Sub
    Call Calculator_StartTime
    DoEvents
    If StopThis = True Then Exit Sub
    Call Refresh_Queries
    DoEvents
    If StopThis = True Then Exit Sub
    Call ExportCSV
    DoEvents
    If StopThis = True Then Exit Sub
    Call CloseExcel
    

End Sub

 

[johnny51981]

Fantastic! Thank you!!!

 

[Jeffrey Mahoney]

Glad I could help. If that is all, please mark my last post as the solution.

You already did! hehe