LOGIN Enabled Workbook - Problems Closing one of the sheets

[sorensjp]

Hello,

I'm trying to close a workbook and using Macros to Loop thru each worksheet to set as xlSheetVeryHidden with the exception of the LOGIN Sheet.

The attached workbook is an .xlsb (recommend changing to.xlsm) file to reduce size. The MACRO operates properly if I'm on the LOGIN sheet (Normally Hidden after logging in). But when I'm on any other sheet and close the workbook I get a Run-time error '1004: Method 'Visible' of object'_Worksheet' failed.

User Name & Password are either:
USER -------- Pword
admin ------- Cville62!!
sorensjp -------- 515457

I cannot understand why every sheet closes properly but Sheet 9 (WEAPS_QUAL_RPT).

Thank you for taking the time to look at this issue and appreciate any advice.

V/r,
Jim

[Mumps1]

The reason you were getting the error was that only one sheet was visible at the time the CloseWorkbook macro was run and the macro was trying to make that one and only visible sheet not visible. Excel must have at least one sheet visible at all times. The macro below makes the LOGIN sheet visible first and then hides all the other sheets. I hope this makes sense. Also, you get a lot of screen flickering when you close the file. Try placing the code in your other macros between "Application.ScreenUpdating = False ….. Application.ScreenUpdating = True" statements as I have done.

Try replacing your current macro with this one:

Sub CloseWorkbook()
    Application.ScreenUpdating = False
    Dim WkSht As Worksheet
    With Sheets("LOGIN")
        .Visible = True
        .Range("B9").ClearContents
    End With
    For Each WkSht In ThisWorkbook.Worksheets
        If WkSht.Name <> "LOGIN" Then WkSht.Visible = xlSheetVeryHidden
    Next WkSht
    ThisWorkbook.Save
    Application.ScreenUpdating = True
End Sub

[sorensjp]

Mumps1,

Thank you. You solved it. I was going to hard code each sheet as to state:

SheetXX.Visible = xlSheetVeryHidden

vice

For Each WkSht In ThisWorkbook.Worksheets
    If WkSht.Name <> "LOGIN" Then WkSht.Visible = xlSheetVeryHidden
    Next WkSht

Does that make any sense?

I just made this minor adjustment to what you wrote and works great:

Sub CloseWorkbook()
Application.ScreenUpdating = False
Sheet11.Activate
Sheet11.Visible = xlSheetVisible
Sheet11.Range("B9").ClearContents
Dim WkSht As Worksheet
For Each WkSht In ThisWorkbook.Worksheets
    If WkSht.Name <> "LOGIN" Then WkSht.Visible = xlSheetVeryHidden
    Next WkSht
ThisWorkbook.Save
Application.ScreenUpdating = True

End Sub

Thank you, again for solving my issue. I was thinking I needed to do way more than just a couple lines.

V/r,
Jim

[Mumps1]

You are very welcome.