VBA IF Then protect hidden worksheets code question

[Lyryx]

I'm trying to combine an IF Then function with my password protect hidden worksheet codes.

The idea is: when a worksheet is being unhidden, it will request a password, if the Password doesn't work the sheet will be hidden again.

Unfortunately, every time a worksheet that uses this code is accessed the password is requested again. Whether it has already been unhidden or not.


I know what I need to be done can be accomplished with a simple IF Then function, but I can't for the life of me get it to work.


here is an example of the code I am using.

Private Sub Worksheet_Show()If Sheet.Visible = False Then
Call Worksheet_unhide
End Sub
Private Sub Worksheet_unhide()
    pword = InputBox("Please Enter a Password", "Unhide Sheets")
    If pword <> "ILCbogo" Then ActiveSheet.Visible = False
End Sub

The second Private sub works fine, I thought the top sub would solve the issue I described earlier, but it just makes it so the worksheet never asks for a password.


Thanks in advance for your assistance.

There is no worry about hiding the password for this work book. It's not going to be used by anyone with an understanding of excel.

 

[jeffreybrown]

Why do you have visible = false for both routines?

Also, if the sheet is hidden, then how can it be the active sheet?

Can you explain what you are tying to accomplish?

 

[Lyryx]

Can you explain what you are tying to accomplish?

It was sort of a bandage solution to the trouble I am having.

Private Sub Worksheet_Activate()
    pword = InputBox("Please Enter a Password", "Unhide Sheets")
    If pword <> "ILCbogo" Then ActiveSheet.Visible = False
End Sub

This is the code I am using the top portion I linked earlier was my attempt at fixing the problem.

Essentially: The workbook I am making is going to be accessed by ground level and supervisor employees
There are several worksheets in the workbook that can only be accessed by supervisors through a password.

These worksheets are hidden until the password is entered then they will be made accessible until hidden once more. Ideally the supervisors will be passing between the password protected worksheets frequently, so it will be quite irritating if they are asked to reenter their password each time the worksheet is accessed.

TL;DR: I'd like the hidden worksheets to only request a password upon being unhidden, once they are shown they should no longer require a password unless hidden and unhidden again.

 

[Kentetsu]

I don't see specific instruction to unprotect or unhide the sheet if the password is correct.

Will they always be opening/viewing multiple sheets? Of those that need access to those sheets, do they all require the same sheets or do they each need access to different combinations?

If they all access the same sheets, then I'd probably just give them a single button. When that button is clicked, verify the password and then open everything up for them. That way, they don't have to go through the sheets one at a time to open them.

Just a thought...

 

[jeffreybrown]

Ok, not sure I'm seeing the full picture, but would this work for your situation...

Private Sub Worksheet_Activate()
    If Me.Visible = True Then Exit Sub
    pword = InputBox("Please Enter a Password", "Unhide Sheets")
    If pword <> "ILCbogo" Then Me.Visible = False
End Sub

 

[Lyryx]

Ok, not sure I'm seeing the full picture, but would this work for your situation...

Private Sub Worksheet_Activate()
    If Me.Visible = True Then Exit Sub
    pword = InputBox("Please Enter a Password", "Unhide Sheets")
    If pword <> "ILCbogo" Then Me.Visible = False
End Sub

This works perfectly! Thank you very much.
It's a pretty unique project I was asked to do at the office, so I can't really explain it in much more depth than I have already. (Because I don't entirely understand the need for this on the workbook myself) but your code has worked perfectly. Thank you.

 

[jeffreybrown]

You are most welcome and thanks for the feedback.

 

[Lyryx]

I don't see specific instruction to unprotect or unhide the sheet if the password is correct.

Will they always be opening/viewing multiple sheets? Of those that need access to those sheets, do they all require the same sheets or do they each need access to different combinations?

If they all access the same sheets, then I'd probably just give them a single button. When that button is clicked, verify the password and then open everything up for them. That way, they don't have to go through the sheets one at a time to open them.

Just a thought...

This is a much better alternative...

How would I go about doing something like this?

 

[Kentetsu]

Okay, so you start out with a Workbook_Open event to hide and protect all of the sheets that should be hidden. This will ensure you are starting out with the right sheets visible, and the right ones hidden. You may also want a Workbook_Close event to lock everything up again before closing/saving. This is a bit redundant, but it would keep someone from stopping the Workbook_Open event, which could leave you exposed.

Private Sub Workbook_Open()

With Worksheets("Greetings Page")
                .Visible = True
                .ScrollArea = "A1:B26"
                .Protect Password:="*****"
                End With
                
                With Worksheets("CuredCook4HrChill")
                .Visible = xlveryhidden
                .ScrollArea = "A1:B26"
                .Protect Password:="*****"
                End With
                
                With Worksheets("UncuredCook4HrChill")
                .Visible = xlveryhidden
                .ScrollArea = "A1:B26"
                .Protect Password:="*****"
                End With

End Sub

Then, you give them a command button with code similar to this, which will unhide all of the sheets and hide the greetings page (or not hide things if you want to just comment out those lines).

Private Sub CommandButton1_Click()

                With Worksheets("CuredCook4HrChill")
                .Visible = True
                .ScrollArea = "A1:B26"
                .unProtect Password:="*****"
                End With
                
                With Worksheets("UncuredCook4HrChill")
                .Visible = True
                .ScrollArea = "A1:B26"
                .unProtect Password:="*****"
                End With

With Worksheets("Greetings Page")
                .Visible = xlVeryHidden
                .ScrollArea = "A1:B26"
                .Protect Password:="*****"
                End With


End Sub

I hope this helps

 

[Lyryx]

You are most welcome and thanks for the feedback.

I may have spoke too soon.....
The code worked... the first time.
But after making one of the hidden sheets visible and entering its password I was never asked to use a password on that worksheet for the next time it was unhidden.

I know my requirements were a little difficult to understand before so I'll try and explain it a little better here.

The process of the workbook

1. Information is input into the main non hidden worksheet from Employee
2. That information is used to populate a list.
3. Supervisor accesses the workbook
4. Supervisor goes to look at the hidden worksheets
5. Supervisor enters the password to view the worksheets
6. Supervisor rehides their worksheet
7. Supervisor closes the workbook

The goal for the code I'm needing is: every time the worksheet is going from hidden to visible the user will be asked to enter a password. If the password is entered correctly, than they can access that sheet as much as they like without being asked to enter the password again. If the password is entered incorrectly then the sheet will hide itself.

When the user is done accessing the hidden worksheet, they will rehide it. Prompting the next person to unhide the worksheet to be asked to enter the password again.