Excel VBA - Can't unhide worksheets

Question

I have a log of incoming / outgoing test samples which I've added code to kick users out if no worksheet change is detected for more than 5 minutes (https://excelribbon.tips.net/T008192_Forcing_a_Workbook_to_Close_after_Inactivity.html). In order to make sure users have macros enabled, I have a worksheet called "Splash Screen" which is the only worksheet visible when the workbook is first opened (all other worksheets are set to xlVeryHidden).

The code I have to hide/unhide worksheets looks simple and works perfectly if the workbook is closed manually, but not if forced to close via the timeout. This is called near the end of the Workbook_BeforeClose procedure:

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case Is = "Splash Screen"
            ws.Visible = xlSheetVisible
        Case Else
            ws.Visible = xlSheetVeryHidden
    End Select
Next

Application.ScreenUpdating = True

After this code block has run, there is no change to the visibility of the worksheets. Everything which was hidden is still hidden, everything that was visible is still visible, everything that was veryhidden is still veryhidden.

The worksheets are locked, but I've tried unlocking them before hiding them and that doesn't help. I've ensured events are enabled, screenupdating is true, errors are not set to "Resume Next", but none of it seems to help. Any suggestions?

Edited to add more code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Checks for missing data in rows to be locked - if missing, user is prompted to enter
    'User can optionally cancel closing the document to enter missing data
    Call DataMissing(Cancel)    'If data missing and user wishes to enter, Cancel = True
    If Cancel Then Exit Sub     'If Cancel = True, exit without further action

    'Locks rows which were changed and Logs changes (if any were made)
    Call LockRows
    Call EmailPM("Delivered")
    Call Internal_LockRows
    If Not Not LoggedRows Then
        Call LogChanges
        Call EmailPM("Edited")
    End If
    If Not Not Internal_LoggedRows Then
        Call Internal_LogChanges
    End If
    Call EmailPM("Internal")
    
    'If changes have been made to live samples, updates lab manager
    If Not Not LiveRows Then Call EmailPM("Live")
    If Not Not Internal_LiveRows Then Call EmailPM("Internal_Live")
    
    'Clears all filters on all sheets
    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        ws.AutoFilter.ShowAllData
    Next ws
    On Error GoTo 0
    
    'Selects MainLab sheet before closing
    MainLab.Select
    
    'Ensures TimeStop and RowID will fire on worksheet change
    Application.EnableEvents = True
    Call TimeStop
    
    'Hides sheets (only unhides if macros are enabled)
    Call HideSheets
    
    'Stores who closed the file to log
    LogToFile (ThisWorkbook.Name & "; " & OpenState & "; closed by; " & Environ("Username") & "; " & Format(Now, "yyyy-mm-dd hh:mm:ss"))
    
End Sub

Sub TimeSetting()
    'Sets CloseTime to a set time in the future, then runs SavedAndClose
    CloseTime = Now + TimeValue("00:00:30") 'Timeout after 5 mins of inactivity
    On Error Resume Next
    Application.OnTime earliesttime:=CloseTime, procedure:="SavedAndClose", Schedule:=True
    On Error GoTo 0
End Sub

Sub TimeStop()
    'A sub to stop the timer ticking down
    On Error Resume Next
    Application.OnTime earliesttime:=CloseTime, procedure:="SavedAndClose", Schedule:=False
    On Error GoTo 0
End Sub

Sub SavedAndClose()
    'Closes the workbook and saves when called
    Application.CutCopyMode = False     'Empties the clipboard to avoid the potential "keep the clipboard" alert
    LogToFile (ThisWorkbook.Name & "; " & OpenState & "; auto-closed; " & Environ("Username") & "; " & Format(Now, "yyyy-mm-dd hh:mm:ss"))
    ThisWorkbook.Close savechanges:=True
End Sub

Does Workbook_BeforeClose actually run when the timer closes the workbook? What's the rest of the code in that procedure? — Tim Williams
Hi @TimWilliams - thanks for replying. Yes, Workbook_BeforeClose does run - I've stepped through the code at the point the timeout procedure triggers and all of Workbook_BeforeClose runs, including the code to hide sheets (it just doesn't do anything). I'll modify the original question to include all of Workbook_BeforeClose. — Daniel Spearman
If the code to hide the sheets actually runs then the sheets should be hidden. Note there's a potential bug there - you must have at least one visible sheet, so there would be a runtime error if the other sheets were hidden before the splash screen was made visible. Try adding some debug.print statements to that code, or step through it with F8: if it really is running then it must do something... — Tim Williams
Hi Tim - I have stepped through but it doesn't do anything. Splash Screen is made visible first, so avoided the potential bug (found that out the hard way). When I step through the code for each ws, I see the .visible state before and after the line has run. It's the same - it doesn't change despite the line commanding ws.visible = xlVeryHidden — Daniel Spearman

JosephC JosephC · Accepted Answer · 2021-08-18T15:55:03

The main reason this does not work is ThisWorkbook.saved = True. Essentially this is saying I've already saved so don't bother prompting me to save when I close. Problem is you haven't saved at the time you are calling close.

Here is a basic example of the setup. You want to: 1. Hide the sheets, 2. Save the document, 3. Close.

Sub HideSheets()
    ' Insert your hide sheet code here
    Sheet2.Visible = xlSheetHidden   ' Test line to hide one sheet
    ThisWorkbook.Save
End Sub

Sub ShutDown()
    Call HideSheets
    ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'This doesn't do much on the automated close, because you've saved already.
    Call HideSheets
End Sub

Excel VBA - Can't unhide worksheets

1 Answers