Hi Kevin,
Thanks a mil for your patience. I think I have it working with your first part of code.
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Splash" Then ws.Visible = xlVeryHidden
Next ws
The second part seems to be causing errors. Do I require this second part for any reason? Sheets are already programmed to unhide depending on who logs in. Your bit ensures all sheets return to hidden when timer runs out. I just want to make sure I'm not leaving myself open by leaving the second part out.
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Option Explicit
'Module1
Public bGblInhibitTimers As Boolean
Public RunWhen As Double
Public RunStatusBarWhen As Double
Public Const NUM_MINUTES = 10
Public Const NUM_SECONDS = 0
Public Const STATUS_BAR_REFRESH_TIME_IN_SECONDS = 1
Public Sub StopTimers()
'This is used for debugging purposes to shut down the timers
bGblInhibitTimers = True
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
Application.OnTime RunStatusBarWhen, "TimeTilExitTimer", , False
On Error GoTo 0
Application.StatusBar = "Timers stopped by StopTimers()."
End Sub
Public Sub SaveAndClose()
'Tell the time remaining timer to stop
bGblInhibitTimers = True
On Error Resume Next
Application.OnTime RunStatusBarWhen, "TimeTilExitTimer", , False
On Error GoTo 0
'Debug.Print "bGblInhibitTimers = true in SaveAndClose() at " & Now
'Return control of the Status Bar to Excel
Application.StatusBar = ""
Application.StatusBar = False
Sheets("Splash").Visible = xlSheetVisible
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Splash" Then ws.Visible = xlVeryHidden
Next ws
ThisWorkbook.Close savechanges:=True
End Sub
Sub TestTime()
RunWhen = Now + TimeSerial(0, NUM_MINUTES, NUM_SECONDS)
Debug.Print Format(RunWhen, "mm/dd/yy hh:mm:ss AM/PM")
End Sub
Sub TimeTilExitTimer()
'This displays a time until exit message
Const SECONDS_PER_DAY = 86400#
Const SECONDS_PER_MINUTE = 60#
Dim sMessage As String
Dim ySecondsTimeToExit As Double
Dim yMinutes As Double
Dim ySeconds As Double
Dim myTime As Date
'Get the current date and time
myTime = Now()
'Calculate the number of seconds remaining to shut down
ySecondsTimeToExit = SECONDS_PER_DAY * (RunWhen - myTime)
'Check for Integrity Error - RunWhen = 0
'Should Never Happen - Shut Down the refresh timer
If ySecondsTimeToExit < 0 Then
bGblInhibitTimers = True
Application.StatusBar = "Software Integrity Error - Time Remaining Display discontinued."
End If
If ySecondsTimeToExit <= 99 Then
sMessage = "Program will time out and exit in " & Format(ySecondsTimeToExit, "0") & " Seconds."
Else
yMinutes = Int(ySecondsTimeToExit / SECONDS_PER_MINUTE)
ySeconds = Int(ySecondsTimeToExit - 60 * yMinutes)
If yMinutes > 0 And ySeconds >= 60 Then
yMinutes = yMinutes + 1
ySeconds = ySeconds - 60
End If
sMessage = "Program will time out and exit at " & Format(RunWhen, "hh:mm:ss AM/PM") & " in " & _
Format(yMinutes, "0") & " Minutes " & Format(ySeconds, "0") & " Seconds."
End If
If bGblInhibitTimers = True Then
'Return control of the Status Bar to Excel
Application.StatusBar = ""
Application.StatusBar = False
'Debug.Print "bGblInhibitTimers = true in TimeTilExitTimer() at " & Now
Else
Application.DisplayStatusBar = True
Application.StatusBar = sMessage
RunStatusBarWhen = Now + TimeSerial(0, 0, STATUS_BAR_REFRESH_TIME_IN_SECONDS)
Application.OnTime RunStatusBarWhen, "TimeTilExitTimer", , True
'Debug.Print "bGblInhibitTimers = false in TimeTilExitTimer() at " & Now
End If
End Sub
Bookmarks