[VBA CODE] Password Protect a hidden sheet while using other code

**cityinbetween** · 09-25-2014, 01:52 PM

Hi, Everyone.

I am currently using a VB code to enforce the enabling of Macros as my workbook heavily relies on VB and Macros.

The code is as follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
Dim ws As Worksheet
        Sheets("START").Visible = xlSheetVisible
        For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "START" Then
        ws.Visible = xlVeryHidden
    End If
    Next ws
End Sub

Private Sub Workbook_Open()

Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
    Next ws
    Sheets("START").Visible = xlVeryHidden
    Sheets("DO NOT DELETE").Visible = xlVeryHidden
    Sheets("Data Sheet").Visible = xlVeryHidden
    Sheets("Supervisor Review").Visible = xlHidden
End Sub

the sheet "Supervisor Review" is soft hidden (can be un-hidden by right clicking on the sheet tabs). What I am trying to do is require a password to unhide this specific sheet and this sheet only. I tried to use the basic "Protect Workbook" structure, but that interfers with the enforce macro VB code. Any ideas?

Thanks in advance!

**Tsjallie** · 09-25-2014, 03:17 PM

Try something like this the sheet's code module.

Private Sub Worksheet_Activate()
   ans = InputBox("Enter password")
   If Not ans = "abc" Then Me.Visible = xlSheetHidden
End Sub

Downside is that each time you (re)activate the sheet the password is required. But that can be solved by setting some public variable.
Let me know if you need help with that.

**cityinbetween** · 09-25-2014, 03:30 PM

This works almost perfect! I really do not mind having to enter the password every time the sheet is hidden/unhidden, in fact I think I prefer it in case the workbook is emailed to someone after unhiding said sheet.

I noticed that when you right click > unhide the sheet, prior to requesting the password it still loads the sheet. Although you cannot scroll to see the full data, without entering the password, you can still see a good chunk. Any way to block that?

**Tsjallie** · 09-25-2014, 03:38 PM

If you change the code from post #2 to this that will be solved.

    Me.Visible = xlSheetHidden
    ans = InputBox("Enter password")
    If Not ans = "abc" Then
        Me.Visible = xlSheetHidden
    Else
        Me.Visible = xlSheetVisible
    End If

**Tsjallie** · 09-25-2014, 03:33 PM

Here's an demo not requiring a password when the sheet's already visible.

**cityinbetween** · 09-25-2014, 03:38 PM

EDIT: I see why you need that addition now, Thank you!

I wonder though, if there is a way to make a dummy sheet, or white out the screen, etc. until someone enters the password to prevent them from seeing ANY data.

**Tsjallie** · 09-25-2014, 03:47 PM

This demo will not require a password all the time and not predisplay the sheet.

**cityinbetween** · 09-25-2014, 04:19 PM

This worked great, I had to modify the "Activate" line but seems to be working great now!! Thank you very much! Your knowledge and time is very much appreciated! (rep added)

If you do have sometime, I am pretty new to VB and am eager to learn. Could you explain why this particular code needed the separate module to run? (this is the first time I run into that..)

Originally Posted by Tsjallie

This demo will not require a password all the time and not predisplay the sheet.

**cityinbetween** · 09-25-2014, 03:52 PM

hmm, there seems to be an issue with the last code. It does indeed ask for a password prior to showing the sheet, but after the password is entered, it throws you on another sheet. When you try to click on the needed sheet it automatically hides it again...

Here is blank copy of the workbook.
(removed attachment)

BTW, Thank you for all your help!!!

[VBA CODE] Password Protect a hidden sheet while using other code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

[VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Re: [VBA CODE] Password Protect a hidden sheet while using other code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Password protect sheet - modify code

[SOLVED] Unprotect VBA - Run Code - Protect again (password is known)

Need to rewrite protect code so that it will work with a hidden sheet.

Protect Sheet & Password Protect Code

[SOLVED] Protect Sheet - VB code to specify password

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions