auto shutdown

fry

Active Member
Joined
Apr 25, 2007
Messages
411
Hi All

I'm using the following code to automatically shut down excel when the sheet is finished with...

Code:
   Application.OnTime Now + TimeValue("00:00:10"), "close_workbook"
    
Sub close_workbook()
Application.Quit
End Sub

My question is...

Is there a way of showing the 10 second countdown timer in a dialog box???

Thanks all :)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

fry

Active Member
Joined
Apr 25, 2007
Messages
411
When I say dialog box I mean message box.....the type you see when you get shareware and a button counts down to zero then you can use it.....

Maybe this time I am getting too adventurous :)
 
L

Legacy 68403

Guest
i'm not sure if this will help you or not but you could use the status bar.

Code:
Sub Macro1()
Application.StatusBar = "10 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "9 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "8 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "7 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "6 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "5 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "4 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "3 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "2 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.StatusBar = "1 seconds remaining"
Application.Wait (Now + TimeValue("0:00:1"))

Application.OnTime Now + TimeValue("00:00:01"), "close_workbook"

End Sub
    
Sub close_workbook()
Application.Quit
Application.StatusBar = False

End Sub
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
you might make that a little shorter with a loop
Code:
Sub Macro1() 
for i = 10 to 1 step -1

    Application.StatusBar = i & " seconds remaining" 
    Application.Wait (Now + TimeValue("0:00:1")) 

next i

Application.OnTime Now + TimeValue("00:00:01"), "close_workbook" 

End Sub 
    
Sub close_workbook() 
Application.Quit 
Application.StatusBar = False

End sub
 

fry

Active Member
Joined
Apr 25, 2007
Messages
411
Thanks guys, I was rather looking for a message box timer if possible but your help is appreciated.

Jonmo I spent time last night doing exactly what you did sorting out another problem I had. I used macros to produce some code which I then experimented with so thanks for the tip!!

I didn't get to bed until after midnight cos I was coding........am I sad or what??? :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,563
Messages
5,765,119
Members
425,262
Latest member
sabry

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