Excel time cout count down in status bar

ajayn

New Member
Joined
Jun 16, 2016
Messages
2
Hello All,



I have code on how to make an excel file close automatically when idle for a specific time which is working fine.

I want to constantly show the countdown time until the timeout period of inactivity in the excel status bar. How do I do this? I tried below but obviously this will not work because it will update only when I touch the excel which I don't want to count inactivity




Thanks

Ajay N



CODE:

------





Module 1:

------------

Public NoActivity As Date
Public SuppressCalcEvent As Boolean


Public Sub ShutDown()
On Error Resume Next
Application.DisplayAlerts = False

SuppressCalcEvent = True
Call StopClock
With ThisWorkbook
.Save
.Close
End With

End Sub



Public Sub StartClock()
On Error Resume Next

NoActivity = Now + TimeValue("00:02:00")

Application.OnTime NoActivity, "ShutDown"

End Sub



Public Sub StopClock()

On Error Resume Next

Application.OnTime NoActivity, "ShutDown", , False

End Sub





Workbook Module:

--------------------



Private Sub Workbook_Open()


Call StartClock

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)



End Sub







Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If SuppressCalcEvent Then Exit Sub
Call StopClock

Call StartClock

End Sub





Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Call StopClock

Call StartClock

End Sub
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Hi Ajay, I misread your original post. I thought that your issue was applying the statusbar changes, not needing code for them.

Try the following in a standard code module as an example. Run the countdown code.

Code:
Public Last_Time As Double

Sub Countdown()
Last_Time = Now()
Application.DisplayStatusBar = True
Update_Status_Bar

End Sub

Sub Update_Status_Bar()
Time_Used = DateDiff("n", Last_Time, Now())
Time_Left = 20 - Time_Used
Application.StatusBar = "Minutes remaining until closure due to inactivity: " & Time_Left
If Time_Used >= 0 And Time_Used < 20 Then
    Start_Time = Now() + TimeSerial(0, 1, 0)
    Application.OnTime Start_Time, "Update_Status_Bar"
Else
    MsgBox "Kick out now" 'replace with your kill code
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

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