Why is the workbook protected upon Workbook_Open Event?

Question

I adapted some code found here http://www.vbaexpress.com/kb/getarticle.php?kb_id=379 for a neat solution that forces users to enable macros. This method is a really good way of getting around this problem. However, my additional requirements are: one particular user needs a more sensitive version of 2 sheets within the workbook. Depending on the answer to a MsgBox an InputBox requires password verification and then shows the sensitive versions of these sheets. I believe the answer lies in how the workbook is saved and then how all sheets are unhidden upon re-opening the workbook (since two of the sheets are effectively password locked) but somehow now when I open this workbook the password used for unhiding the two sensitive versions of the sheets is required to open the workbook. So I changed the password required for verification of the said sheets but the Original password is still required to open the workbook: Any assistance in diagnosing this problem would be a help: Creating a spread-sheet and testing the code on your system shouldn't be too difficult and a good place to start!

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False

     'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook
        If Not .Saved Then
            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
                vbYesNoCancel + vbExclamation)
            Case Is = vbYes
                 'Call customized save routine
                Call CustomSave
            Case Is = vbNo
                 'Do not save
            Case Is = vbCancel
                 'Set up procedure to cancel close
                Cancel = True
            End Select
        End If

         'If Cancel was clicked, turn events back on and cancel close,
         'otherwise close the workbook without saving further changes
        If Not Cancel = True Then
            .Saved = True
            Application.EnableEvents = True
            .Close savechanges:=False
        Else
            Application.EnableEvents = True
        End If
    End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False

     'Call customized save routine and set workbook's saved property to true
     '(To cancel regular saving)
    Call CustomSave(SaveAsUI)
    Cancel = True

     'Turn events back on an set saved property to true
    Application.EnableEvents = True
    ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
     'Unhide all worksheets


    Application.ScreenUpdating = False
    Call ShowAllSheets
    Application.ScreenUpdating = True

        Dim msg1, msg2, Pwd As String

    Pwd = "5555"

    Do

    msg1 = MsgBox("Are you the Master User?", vbYesNo)

    Loop Until msg1 = vbNo Or msg1 = vbYes

    If msg1 = vbNo Then
    Worksheets("Sensitive Sheet 1").Visible = xlVeryHidden
    Worksheets("Sensitive Sheet 2").Visible = xlVeryHidden

    ThisWorkbook.Unprotect Password:=Pwd
    ElseIf msg1 = vbYes Then

    Do
    msg2 = InputBox("Please Enter the Password", "Password Checker", vbOKOnly)
    Loop Until msg2 = Pwd

    Worksheets("Sensitive Sheet 1").Visible = True
    Worksheets("Sensitive Sheet 2").Visible = True
    Worksheets("Standard Sheet 1").Visible = xlVeryHidden
    Worksheets("Standard Sheet 2").Visible = xlVeryHidden

    End If





End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
    Dim WS As Worksheet, aWs As Worksheet, newFname As String
     'Turn off screen flashing
    Application.ScreenUpdating = False

     'Record active worksheet
    Set aWs = ActiveSheet

     'Hide all sheets
    Call HideAllSheets

     'Save workbook directly or prompt for saveas filename
    If SaveAs = True Then
        newFname = Application.GetSaveAsFilename( _
        fileFilter:="Macro Enabled Excel Files (*.xlsm), *.xlsm")
        If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
    Else
        ThisWorkbook.Save
    End If

     'Restore file to where user was
    Call ShowAllSheets
    aWs.Activate

     'Restore screen updates
    Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
     'Hide all worksheets except the macro welcome page
    Dim WS As Worksheet

    Worksheets(WelcomePage).Visible = xlSheetVisible

    For Each WS In ThisWorkbook.Worksheets
        If Not WS.Name = WelcomePage Then WS.Visible = xlSheetVeryHidden
    Next WS

    Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
     'Show all worksheets except the macro welcome page

    Dim WS As Worksheet


    For Each WS In ThisWorkbook.Worksheets

        If WS.Name <> "Sensitive Sheet 1" And WS.Name <> "Sensitive Sheet 2" Then

            If Not WS.Name = WelcomePage Then WS.Visible = xlSheetVisible

        End If

    Next WS

    Worksheets(WelcomePage).Visible = xlSheetVeryHidden

End Sub

I don't think you need to bother locking the workbook, just use `xlVeryHidden` for the two sheets. Unprotect the workbook, save it, and then remove the line of code that unprotects it in the VBA. — Tim, May 31 '13 at 02:09
Hi Tim, Thanks for getting back to me....I don't know why/how the workbook has been locked. I don't want it to be locked and that is the method I am using to hide the sheets already. If the Master User enters the correct password he sees the full versions of the sheets and if not the master user then the altered versions are shown using the method you describe. Problem is this process must have somehow locked the Workbook - how to unlock it and prevent it from happening again is the question? — tripkane, May 31 '13 at 02:17
I'm trying something out and if it works I'll have an answer for you shortly - or at least a suggestion. — Tim, May 31 '13 at 02:18
Gave you a suggestion...I'm going to edit it in a bit with a further suggestion, but this should get you going. — Tim, May 31 '13 at 02:34

Tim · Accepted Answer · 2013-05-31T06:05:46.060

1

You don't need to protect the whole workbook to hide the sheets. xlVeryHidden will hide the sheet and prevent it from being shown on the Hide/Unhide list.

First, unprotect the workbook. You can do this by the Review tab, and then clicking on the Protect Workbook and clear any protection you have checked. I'd also click on the Protect Sheet button for each sheet and clear any protection set there.

Then, modify your Workbook_Open subroutine to do something like this:

Private Sub Workbook_Open()

    Dim msg1, msg2, Pwd As String

    Pwd = "5555"

    Do
        msg1 = MsgBox("Are you the Master User?", vbYesNo)
    Loop Until msg1 = vbNo Or msg1 = vbYes

    If msg1 = vbNo Then
        Worksheets("Sensitive Sheet 1").Visible = xlVeryHidden
        Worksheets("Sensitive Sheet 2").Visible = xlVeryHidden
    ElseIf msg1 = vbYes Then
        Do
            msg2 = InputBox("Please Enter the Password", "Password Checker", vbOKOnly)
        Loop Until msg2 = Pwd

        Worksheets("Sensitive Sheet 1").Visible = True
        Worksheets("Sensitive Sheet 2").Visible = True
        Worksheets("Standard Sheet 1").Visible = xlVeryHidden
        Worksheets("Standard Sheet 2").Visible = xlVeryHidden
    End If
End Sub

Basically, the workbook is unprotected when opened. If the user is not the special user, then it hides the two special sheets. If they are the special user, then it hides the two special sheets and they can't be unhidden.

Additional Suggestion

You'll run into a problem with the sheet visibility when the user saves - if it's the special user, once the save is complete they won't be able to see the sensitive sheets, because your ShowAllSheets subroutine will hide them.

To get around this, set a global variable to record whether the user is the special user or not, and use that to determine what sheets to show in ShowAllSheets, like this:

Option Explicit

Public IsMasterUser As String

Private Sub Workbook_Open()

    Dim msg1, msg2, Pwd As String

    Pwd = "5555"

    IsMasterUser = "N"

    Application.ScreenUpdating = False
    Call ShowAllSheets
    Application.ScreenUpdating = True

    Do
        msg1 = MsgBox("Are you the Master User?", vbYesNo)
    Loop Until msg1 = vbNo Or msg1 = vbYes

    If msg1 = vbNo Then
        IsMasterUser = "N"

        Worksheets("Sensitive Sheet 1").Visible = xlVeryHidden
        Worksheets("Sensitive Sheet 2").Visible = xlVeryHidden
    ElseIf msg1 = vbYes Then
        Do
            msg2 = InputBox("Please Enter the Password", "Password Checker", vbOKOnly)
        Loop Until msg2 = Pwd

        IsMasterUser = "Y"

        Worksheets("Sensitive Sheet 1").Visible = True
        Worksheets("Sensitive Sheet 2").Visible = True
        Worksheets("Standard Sheet 1").Visible = xlVeryHidden
        Worksheets("Standard Sheet 2").Visible = xlVeryHidden
    End If
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

    Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets
        If Not WS.Name = WelcomePage Then
            If IsMasterUser = "N" _
               And WS.Name <> "Sensitive Sheet 1" _
               And WS.Name <> "Sensitive Sheet 2" Then
                WS.Visible = xlSheetVisible
            ElseIf IsMasterUser = "Y" _
               And WS.Name <> "Standard Sheet 1" _
               And WS.Name <> "Standard Sheet 2" Then
                WS.Visible = True
            End If
        End If
    Next WS

    Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

This code will set a flag (IsMasterUser) depending on the selection in Workbook_Open. Then when ShowAllSheets is called, it will determine whether to show the standard sheets or the special sheets based on IsMasterUser. Note that IsMasterUser is defaulted to "N" when its declared.

EDIT

A couple of minor problems with the code above. First, the global variable needs to be declared as Public IsMasterUser As String, and secondly it needs to be set to a value inside a function, so I set it to "N" at the start of the Workbook_Open subroutine.

I tested the posted code (the second set), along with the unaltered code from the original post in a Excel 2010 workbook with 6 sheets - Macros, User, Standard Sheet 1, Standard Sheet 2, Sensitive Sheet 1 and Sensitive Sheet 2, and it worked just fine.

edited May 31 '13 at 06:05

answered May 31 '13 at 02:33

Tim

28,212
8
63
76

whenever I hear "can't", I get this itch... "They can't be unhidden"? Suppose someone goes into the VBA editor - could they write themselves a macro to unhide the sensitive sheets? I think so... `For each sh in Workbook("some name").Worksheets, sh.Visible=True, next sh` might work? You can start by hiding the code (people might not know there are Very Hidden sheets). Is there a way to prevent "other" code (from another workbook) from operating on a workbook? – Floris May 31 '13 at 03:56
Also I think you don't want to set the standard sheets to `xlVeryHidden` for the Master User. I suspect the master user should be allowed to see everything... – Floris May 31 '13 at 03:57
@Floris - You can password protect the VBA for a given Workbook, which would prevent a user from doing that. While you probably could use VBA from another workbook, would the average user do that? I doubt it. And if the information is that sensitive/important, then Excel is the wrong tool for the job - even a protected workbook can be cracked. I don't know if there's a way to prevent code from one workbook running on another (unless the workbook is protected). – Tim May 31 '13 at 04:02
@Floris - based on both the OP's question and their posted code, the standard sheets are set to hidden because the Master User needs the special sheets *instead* of the standard sheets. – Tim May 31 '13 at 04:03
yes to your first response: this is what I meant by "hiding" the code (password protecting it); but it doesn't stop someone from doing what I did from another workbook (without needing to know the names of the sheets). Thus the question: can a workbook be made "unscriptable"? I am probably not the "average user", but security needs to be proportional to whatever is at stake. I'm pointing out (to you and OP) that this is "somewhat secure"; whether they think it's acceptable is their choice not mine. As for the second point - I didn't read far enough down the original code, you're right. – Floris May 31 '13 at 04:07
This is great stuff Tim. Thanks again. The funny thing is that as "Master "User" and/or "User" there is no protection on any sheet or the workbook. I tried protecting and un-protecting every sheet and the workbook in both states and unhiding the veryhidden sheets in User form to check if they were protected in user status but even those were without protection - the workbook protection message still comes up when the workbook is opened. – tripkane May 31 '13 at 04:27
Additionally, In my original code, the sheets I had wanted to show/hide for each type of user were working ok - as expected (I can't get your code to work either) but the original password defined by Pwd variable which was Pwd = "1234" is still required to be input to open the (.xlsm) excel file at open?!?!? – tripkane May 31 '13 at 04:29
Tim and Floris: None of the people using this workbook are "Excel power users" i.e. know what they are doing in regards to VBA, or formulas much. I do not what to confuse the man who is "Master User" by having two versions of the same spread-sheet visible so I decided to hide the "normal user" version. Security is a non-issue, and I'll probably password protect the VBA project before I send it off anyway just in case - but this is unnecessary anyway - if people really want to find out this stuff they will TBC – tripkane May 31 '13 at 04:55
http://stackoverflow.com/questions/259897/how-password-protection-of-excel-vba-code-works?rq=1 Based on this and simply knowing people, if they really want the info they will get it. Anyway it's a non-issue....the only issue is how do I get rid of this password protected status that was unintentional in the first place????? – tripkane May 31 '13 at 04:56
@tripkane - Looks like there's some issues with the code (mainly the global variable). I'm working on a full test case right now (in between monitoring/documenting a production down issue) but will try and get a working example tonight for you. As for the workbook protection, do you have any other VBA code in that workbook? If so, try checking to see if it is making a call to protect the workbook or sheet. – Tim May 31 '13 at 05:09
@Tim Thanks for your persistence! I have looked through every sheet and module in the VBA Project. The only other code is a macro which has a control box embedded on two of the sheets that updates all pivot tables & charts in the workbook. – tripkane May 31 '13 at 05:24
@Tim I thought of an alternative and simple solution: 1) Lock all cells in the appropriate columns 2) protect the sheet (with "All users of this worksheet" being able to "Select unlocked cells"). The problem with that is that I'm not sure how well the "Master User" can use Excel and his English isn't good! but the above steps could be put into a macro and assigned to a control box?!? Perhaps simpler solution but still doesn't solve the mystery about how the workbook got locked and how to undo this! Cheers, Kane – tripkane May 31 '13 at 05:29
1

@tripkane - I fixed the issues in my code and tested it in a workbook with 6 sheets (as explained in my edit). As for your alternative solution, that might work - you can bind a macro to just about any kind of control in Excel. Still not sure why your workbook is thinking it's locked....if it's not a lot of sheets and they're pretty simple, you might try starting from scratch just to see if something got corrupted. – Tim May 31 '13 at 06:07
@Tim I think you might be right on the corruption front there mate. Will see if I copy all the sheets over to a fresh Workbook and test the result without any code as a start and then introduce yours. Will let you know the result soon. Cheers – tripkane Jun 03 '13 at 06:07

Why is the workbook protected upon Workbook_Open Event?

1 Answers1