1

I've been trying to get the typical hide sheets/unhide sheets code to work for encouraging Macro enabling. Since I lock down saving, I needed to do it slightly differently than putting it in the Workbook_BeforeClose Sub as usually is done.

But...my hide sub

Sub HideSheets()
'Error Handling when workbook is unprotected.
On Error GoTo EH
'Unprotect the workbook to allow conditional formatting changes.
ThisWorkbook.Sheets("Field Service Report").Unprotect Password:="x"
ThisWorkbook.Sheets("Prompt").Unprotect Password:="x"

'Main Sub Code
    Application.EnableCancelKey = xlDisabled
    Sheets("Prompt").Visible = xlSheetVisible
    Sheets("Field Service Report").Visible = xlSheetVeryHidden
    Application.EnableCancelKey = xlInterrupt

'Reprotect worksheet before ending sub.
ThisWorkbook.Sheets("Field Service Report").Protect Password:="x", UserInterfaceOnly:=True
ThisWorkbook.Sheets("Prompt").Protect Password:="x"

Exit Sub
EH:
Call EH
Resume Next

End Sub

and my unhide sub

Sub UnhideSheets()
'Error Handling
On Error GoTo EH
'Unprotect the workbook to allow conditional formatting changes.
ThisWorkbook.Sheets("Field Service Report").Unprotect Password:="x"
ThisWorkbook.Sheets("Prompt").Unprotect Password:="x"

'Main Sub Code
    Application.EnableCancelKey = xlDisabled
    Sheets("Field Service Report").Visible = xlSheetVisible
    Sheets("Prompt").Visible = xlSheetVeryHidden
    Application.EnableCancelKey = xlInterrupt

'Reprotect worksheet before ending sub.
ThisWorkbook.Sheets("Field Service Report").Protect Password:="x", UserInterfaceOnly:=True
ThisWorkbook.Sheets("Prompt").Protect Password:="x"

Exit Sub
EH:
Call EH
Resume Next

End Sub

....works fine when called form the immediate window. Sheets hide and unhide appropriately.

But, when I step through the sub it doesn't actually do anything. The idea is to set the sheets to the "prompt" sheet before saving, save, then revert to usable after saving. But I can't even see if that code is working correctly (it appears to) because stepping through the actual hide/unhide subs doesn't do anything.

Edit: No errors, just doesn't change any settings to hide or unhide sheets.

Thoughts?

Edit: So given the comments below, my subs work when run from the immediate window and when stepped through via debugger. They hide and unhide the worksheets appropriately. So, the only thing that can be wrong is the code that calls these subs. So, here are two more subs. One is the button code for a save button, and the other is the Workbook_BeforeSave Sub.

Sub Save_Form()

'Error Handling ...
On Error GoTo EH

'Unprotect the workbook ...
ThisWorkbook.Sheets("Field Service Report").Unprotect Password:="x"

'Variable to disable any other save but this button.
Module1.SaveChk = 1

'Code to automatically save a copy ...
Module1.UserPath = Environ("USERPROFILE")
Module1.Path = UserPath & "\Desktop\"
If Module1.EditChk = "Y" Then
    Module1.SaveName = "FSR Master"
Else
    Module1.SaveName = Range("AF6").Value
End If

ThisWorkbook.SaveAs _
    Filename:=Path & SaveName & ".xlsm", _
    FileFormat:=52

If Module1.SaveError <> 1 Then
    'User Display of Save Success
    MsgBox "Filename = " & SaveName & vbNewLine _
    & "File is saved to your desktop."
Else
    Module1.SaveError = 0
End If

'Reset SaveChk variable
Module1.SaveChk = 0

'Reprotect Worksheet
ThisWorkbook.Sheets("Field Service Report").Protect Password:="x", UserInterfaceOnly:=True

Exit Sub

EH:
Call ErHa
Resume Next

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Error Handling ...
On Error GoTo EH

'Save Initiation Check
If Module1.SaveChk <> 1 Then
    Cancel = True
    MsgBox "Please Use Form Save Button", vbOKCancel + vbExclamation, "SAVE CANCELLED"
    Exit Sub
End If

If Module1.EditChk <> "Y" Then 'Skips the whole block if EditChk = Y

    'Create the final range of cells for checking
    Set Module1.Required = Application.Union(Module1.Fixed, Module1.Drive, Module1.Stage)
    'Check if all required cells are filled in
    If WorksheetFunction.CountA(Module1.Required) < Module1.Required.Count Then
        Cancel = True
        MsgBox "Please Completed Shaded Cells!", vbOK + vbExclamation, "SAVE CANCELLED"
        Module1.SaveError = 1
    Else
        'Set the report date before saving
        Application.EnableEvents = False
        Range("AE59") = Format(Now(), "mm-dd-yyyy hh:mm:ss AM/PM")
        Application.EnableEvents = True
    End If
End If
'Renable Macro Splash Screen Before Save
Call HideSheets

Exit Sub

EH:
Call ErHa
Resume Next

End Sub
jimm
  • 33
  • 9
  • 4
    You might consider commenting out your error handler and see what error message might get thrown. – Matt Cremeens Dec 04 '19 at 16:47
  • 4
    In addition, there are two statements where you are addressing the Sheets collection without the ThisWorkbook qualifier, thus calling Sheets in the active workbook which is not necessarily the same as ThisWorkbook. – jkpieterse Dec 04 '19 at 16:48
  • 2
    `EH` can't be both an error-handling subroutine label *and* an invocable procedure that you can `Call`. And if it can, then it shouldn't. Don't use conflicting names like this. – Mathieu Guindon Dec 04 '19 at 16:49
  • Hmm...interesting. Thanks. Also, I forgot to mention the error. RT-1004 Visible not able to be set in this class – jimm Dec 04 '19 at 16:49
  • Is workbook structure protected? – Mathieu Guindon Dec 04 '19 at 16:50
  • I didn't think so, the prompt page itself wasn't protected either but I added the unprotect/protect just in case. Can I do the same with the workbook? Never protected workbook before. – jimm Dec 04 '19 at 16:54
  • The RT1004 error seems intermittent. Sometimes its thrown, sometimes not. – jimm Dec 04 '19 at 17:01
  • 1
    On the "Review" Ribbon tab, under the "Protect" group; there's a "Protect Sheet" button, and a "Protect Workbook" button next to it. Workbook protection deals with the ability to add/remove/hide/unhide worksheets. If workbook structure is protected, that would be why unhiding a sheet is throwing error 1004. – Mathieu Guindon Dec 04 '19 at 17:05
  • @Matheiu Guindon - I'll check on that. Apparently, my excel crashed and i've been testing without the code I posted above. So, give me a moment to rework the code and some of the suggestions and I'll post about any errors. I say that, because at one point it seemed like there were no errors it just didn't work (I had the error handling commented out). But, when I went to double-check before posting here, I checked with old code that still had that RT error. – jimm Dec 04 '19 at 17:09
  • Ok so...1. The workbook is unprotected. 2. I changed the error-handling labels so they aren't duplicated. 3. Testing the code repeatedly no longer reveals any RT errors. It still works fine when called from the Immediate Window, it ALSO works fine when manually stepped through in the debugger. But, when I put a break in my save routines and step through the whole process, when I get to the hidesheets sub (the idea is to reset the sheets to the prompt sheet prior to save) it steps through without errors but does NOT hide any sheets. – jimm Dec 04 '19 at 17:29
  • Well, I'm stumped! If I copy the sub code directly into where its called, it still doesn't error but doesn't do anything. I'm trying to see if I have a system setting toggled that I'm unaware of but I can't see anything either. – jimm Dec 04 '19 at 18:40

0 Answers0