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

[dinkss]

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!

 

[Logit]

Option Explicit

Sub t()
Dim i As Integer
Dim s As Long
    Sheet1.Range("A1").Value = i
        For i = 30 To 0 Step -1
             Range("A1") = i
             s = Timer + 1
             Do While Timer < s
                DoEvents
            Loop
        Next
End Sub

 

[dinkss]

Where do I put a message to be displayed?

 

[dinkss]

Option Explicit

Sub t()
Dim i As Integer
Dim s As Long
    Sheet1.Range("A1").Value = i
        For i = 30 To 0 Step -1
             Range("A1") = i
             s = Timer + 1
             Do While Timer < s
                DoEvents
            Loop
        Next
End Sub

I tried this macro and it doesn't work for me.

 

[keithmct]

It works for me, counts down 30 to 0 in cell A1. It just looks like a "proof of concept" bit of code that you need to adapt to your purpose.
more information required might be:
when will the save macro first run?
after what time interval will it run again and again?

 

[dinkss]

It works for me, counts down 30 to 0 in cell A1. It just looks like a "proof of concept" bit of code that you need to adapt to your purpose.
more information required might be:
when will the save macro first run?
after what time interval will it run again and again?

I need a macro to run before automatic save macro and then to show a pop-up message with countdown timer as I have setup automatic save and update macros to run every 45 minutes. So I need a countdown time pop-up message macro that will show up a message on the screen that auto save and auto update macros will run in 30 seconds to inform user not to enter any information within 30 seconds.

 

[Logit]

Option Explicit

Sub t()
Dim i As Integer
Dim s As Long

MsgBox "Auto save and auto update macros will run in 30 seconds.", vbInformation & vbOKOnly, "Please Wait" 

    Sheet1.Range("A1").Value = i
        For i = 30 To 0 Step -1
             Range("A1") = i
             s = Timer + 1
             Do While Timer < s
                DoEvents
            Loop
        Next

'Place a call to your autosave macro here

End Sub

 

[keithmct]

To get the countdown to actually appear in the message box/userform, I found this from JoeMo in 2015 which I could only adapt down to 1 minute. I don't know how to get to 30 seconds and I don't know how to get the timer to start automatically when the userform appears. Press Alt F8 to run the macro:

Adding a countdown timer to userform in VBA

Hello, I am wondering if it is possible to add a countdown timer to a userform. Here is the design that I had in mind: a) Insert a textbox on the userform displaying the countdown b) Allow the user to interact with controls on userform while the countdown hasn't reached 0 c) Take action if...

www.mrexcel.com

in a module put this

Public AllowedTime As Variant
Sub TestUserForm()
AllowedTime = "1" 'InputBox("Enter the time interval to count down in whole minutes - max is 99")
'If AllowedTime = "" Then Exit Sub
'MsgBox "click Start when you are ready to begin"
UserForm1.Show
End Sub

make a userform thus:

in sheet 1 put this

Private Sub UserForm_Initialize()
Dim M As Double, S As Double
M = Int(CDbl(AllowedTime))
S = (CDbl(AllowedTime) - Int(CDbl(AllowedTime))) * 60
With tBx1
    .Value = Format(CStr(M), "00") & ":" & Format(CStr(S), "00")
End With
End Sub

in the code for the button "Start" put this

Private Sub CommandButton1_Click()
Dim t, E, M As Double, S As Double
'AllowedTime is set in sub 'modTestUserForm' for this user form
t = Timer
Do
'next If-End If protects in case clock was started just before midnight and Timer resets during countdown
    If Timer - t < 0 Then
        Unload UserForm1
        MsgBox "Error encountered - start again"
        Exit Sub
    End If
    E = CDbl(Time) * 24 * 60 * 60 - t 'elapsed time in secs
    M = CDbl(AllowedTime) - 1 - Int(E / 60)
    S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)
    With tBx1
        .Value = Format(CStr(M), "00") & ":" & Format(CStr(S), "00")
    End With
    DoEvents
Loop Until (Timer - t) / 60 >= CDbl(AllowedTime) Or UserForm1.Visible = False 'i.e. Cancel clicked during countdown
    Unload UserForm1
    Beep
    MsgBox "workbook saved"
End Sub

in the code for button "don't wait - save now" put this

Private Sub CommandButton2_Click()
Unload UserForm1
ThisWorkbook.Save
End Sub

 

[dinkss]

Option Explicit

Sub t()
Dim i As Integer
Dim s As Long

MsgBox "Auto save and auto update macros will run in 30 seconds.", vbInformation & vbOKOnly, "Please Wait"

    Sheet1.Range("A1").Value = i
        For i = 30 To 0 Step -1
             Range("A1") = i
             s = Timer + 1
             Do While Timer < s
                DoEvents
            Loop
        Next

'Place a call to your autosave macro here

End Sub

I will try this out! Thanks

 

[Lars1]

I will try this out! Thanks

Hi

I have just seen this post and i need exactly the same function.
The only difference is i would like to create an automatic backup of my file.

Everything works, but...

Is it possible to get the above code/function work without the user should click on "OK" ?
Simply show the information box and then countdown without any interaction from the user ?