Countdown timer (30 seconds) pop-up message before Macro Run

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
63
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi there,

How can I create a VBA CODE to run COUNTDOWN TIMER - a pop-up message, 30 seconds before Macro will run? I want to have a message displayed like "This schedule will AUTO-UPDATE & AUTO-SAVE in..." before macro will run.

I will appreciate any help.

Thanks guys!
 

keithmct

Board Regular
Joined
Mar 9, 2007
Messages
217
Office Version
  1. 2019
Platform
  1. Windows
at one stage I had something working in the background to autosave a copy of a workbook every 10 minutes, renamed to something else. I think it was:
VBA Code:
Public dTime As Date
Sub AutoSaveAs()
    dTime = Time + TimeValue("00:10:00") 'change this interval to your requirements
    With Application
        .OnTime dTime, "AutoSaveAs"
        .EnableEvents = False
        .DisplayAlerts = False
        ThisWorkbook.SaveCopyAs "new name of your file" & ".xlsm"
        .EnableEvents = True
    End With
End Sub
not sure if it automatically copies over the saved file each time without user input.

Also I had another instance of saving a copy but naming it with same name plus a timestamp every 30 minutes or something. This would repeat for 5 loops and the sixth time it would save but delete the oldest file only ever keeping 5 latest copies of the file over the last 150 minutes.
VBA Code:
Public dTime As Date

Sub AutoSaveAs()

    Dim fso As New FileSystemObject
    Dim fil As File
    Dim oldfile As File
    Dim BackUpPath As String 'This is the FOLDER where backups are stored

 dTime = Time + TimeValue("00:30:00")
    With Application
        .OnTime dTime, "AutoSaveAs"
        .EnableEvents = False
        .DisplayAlerts = False
        ThisWorkbook.SaveCopyAs "C:/yourfolder/30 minute backups/yourfilename backup" & Format(Date, "yyyy.mm.dd") & ".h" _
        & Hour(Now) & ".m" & Minute(Now) & ".xlsm" 'you have to create the folders where all this saves
        .EnableEvents = True
        
    End With

    Do Until fso.GetFolder("C:/yourfolder/30 minute backups").Files.Count < 6
        For Each fil In fso.GetFolder("C:/yourfolder/30 minute backups").Files
            'Checks to see if this file is older than the oldest file thus far
            If oldfile Is Nothing Then Set oldfile = fil
            If oldfile.DateLastModified > fil.DateLastModified Then Set oldfile = fil
        Next fil
        fso.DeleteFile oldfile, True
        Set oldfile = Nothing
    Loop

End Sub
If you try this and it doesn't work there might be some extra Functions I have left out which I would have to add one at a time to see which one matters. I have stuff everywhere in that workbook and I'm not an expert. (unless you count copy/paste)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
139
Office Version
  1. 365
Platform
  1. Windows
at one stage I had something working in the background to autosave a copy of a workbook every 10 minutes, renamed to something else. I think it was:
VBA Code:
Public dTime As Date
Sub AutoSaveAs()
    dTime = Time + TimeValue("00:10:00") 'change this interval to your requirements
    With Application
        .OnTime dTime, "AutoSaveAs"
        .EnableEvents = False
        .DisplayAlerts = False
        ThisWorkbook.SaveCopyAs "new name of your file" & ".xlsm"
        .EnableEvents = True
    End With
End Sub
not sure if it automatically copies over the saved file each time without user input.

Also I had another instance of saving a copy but naming it with same name plus a timestamp every 30 minutes or something. This would repeat for 5 loops and the sixth time it would save but delete the oldest file only ever keeping 5 latest copies of the file over the last 150 minutes.
VBA Code:
Public dTime As Date

Sub AutoSaveAs()

    Dim fso As New FileSystemObject
    Dim fil As File
    Dim oldfile As File
    Dim BackUpPath As String 'This is the FOLDER where backups are stored

dTime = Time + TimeValue("00:30:00")
    With Application
        .OnTime dTime, "AutoSaveAs"
        .EnableEvents = False
        .DisplayAlerts = False
        ThisWorkbook.SaveCopyAs "C:/yourfolder/30 minute backups/yourfilename backup" & Format(Date, "yyyy.mm.dd") & ".h" _
        & Hour(Now) & ".m" & Minute(Now) & ".xlsm" 'you have to create the folders where all this saves
        .EnableEvents = True
       
    End With

    Do Until fso.GetFolder("C:/yourfolder/30 minute backups").Files.Count < 6
        For Each fil In fso.GetFolder("C:/yourfolder/30 minute backups").Files
            'Checks to see if this file is older than the oldest file thus far
            If oldfile Is Nothing Then Set oldfile = fil
            If oldfile.DateLastModified > fil.DateLastModified Then Set oldfile = fil
        Next fil
        fso.DeleteFile oldfile, True
        Set oldfile = Nothing
    Loop

End Sub
If you try this and it doesn't work there might be some extra Functions I have left out which I would have to add one at a time to see which one matters. I have stuff everywhere in that workbook and I'm not an expert. (unless you count copy/paste)
Thanks for your prompt answer to this...
I will try to look at your autosave solution, maybe i can use this...

but my question was regarding how to avoid the user to click "OK" on a MsgBox :)
 

keithmct

Board Regular
Joined
Mar 9, 2007
Messages
217
Office Version
  1. 2019
Platform
  1. Windows
well if you use an automatic backup there will be no userform and no button to click (or not)
 

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
139
Office Version
  1. 365
Platform
  1. Windows
well if you use an automatic backup there will be no userform and no button to click (or not)
I already have an automatic backup solution. (maybe your is better)
I have just chosen to warn my users before i create the backup with a MsgBox saying the backup is in progress.

But i still have the basic question.
how to avoid user to click on "OK" on a MsgBox :)
 

keithmct

Board Regular
Joined
Mar 9, 2007
Messages
217
Office Version
  1. 2019
Platform
  1. Windows
i don't know enough about VBA. probably something to do with:
Sheet1.CommandButton1_Click
The google search term I used that got heaps of results was "auto-click command button VBA" but it got complicated quickly.
 

Forum statistics

Threads
1,144,394
Messages
5,724,084
Members
422,536
Latest member
Zeeshan53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top