Protect sheet from View with password

[Visy123]

Hi Guys
I found a thread with this same question, however my attempt to use the code failed no doubt it is me not the code. I use version 10 and have a user form populate a data sheet on another sheet (sheet 3) named"Bob" is sensitive information. How can I protect that sheet from view via a password please?

 

[Logit]

.
This is the main code :

Option Explicit




Sub ShowSheets()
Dim pWord As String
     'Prompt the user for a password and unhide the worksheet if correct
    Select Case InputBox("Please enter the password to unhide the sheet", _
        "abc")
         
    Case pWord
        MsgBox "Sorry, that password is incorrect!", _
        vbCritical + vbOKOnly, "You are not authorized!"
        'ActiveWorkbook.Close
    Case Else
        With Worksheets("Bob")
            .Visible = xlSheetVisible
            .Activate
            .Range("A1").Select
        End With
        With Worksheets("Another Sheet")
            .Visible = xlSheetVisible
            .Activate
            .Range("A1").Select
        End With
    End Select
End Sub


Sub hideSheetsAgn()
    Worksheets("Bob").Visible = xlSheetVeryHidden
    Worksheets("Another Sheet").Visible = xlSheetVeryHidden
End Sub

Take a look at the ThisWorkbook Module as well to see how the sheets are auto-hidden when closing the workbook.

Download workbook : https://www.amazon.com/clouddrive/share/71zAFBKK3ps4ATzlCgR7cnZVN7Ia7oD9Vc4VdBmHBWd

 

[Visy123]

Thank You Logit
Sorry for a dumb question, but I am just learning a little VBA the code you supplied does that go on the same page as the rest of my userform code?

 

[Logit]

.
As one of my instructors used to say "The only dumb question is the one not asked." Of course, my classmates still had a knack of making you feel inferior when you asked a question.

Anyway .. paste the macro into a ROUTINE MODULE.


Then if you want to manually access the password request, paste a CommandButton on the worksheet of your choice and attach it to the macro.

If you want the password request to display as soon as you open the workbook, you can add a small macro in the ThisWorkbook module to accomplish that.

If you'll download the sample file I created for your review, open the VBE and look at the code in the ROUTINE MODULE and in the ThisWorkbook module ... you can see what I'm referring to.
The macro in the ThisWorkbook module looks like this :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call hideSheetsAgn
End Sub


Private Sub Workbook_Open()
    'Call ShowSheets
End Sub

Notice the line " Call ShowSheets " is commented out. As it is now, when you open the workbook you'll have to click the button to see the Password Request notice.
If you un-comment out the line, when you open the workbook the Password Request will automatically show.

Play around with it ... it won't break. See how things work. You can always download it again.

 

[Visy123]

Thanks again
So I will test you (as a bad student) I have pasted the code into a new module and when i click on the sheet there is no password request the sheet opens as normal "Its me not you"
I inserted a new module and pasted the code is there something I may have missed?

 

[Logit]

Best and easiest thing is to download the sample workbook, open it and study where the code is located.

 

[Visy123]

Whoo Hoo finally got it. Thank you so much working like a dream

 

[Logit]

You are welcome.

Glad it works for you !