Password prompting to unhide worksheet allows temporary visibility

[Pierre]

Am using this code to prompt the user for a password when attempting
to unhide a hidden sheet. Unfortunately, the worksheet does become
visible as the dialogue box displays on the screen where the user
enters the password. If they click outside of the prompt field, it
will all close, but not before the sheet can be read.
Now if the worksheet is "veryhidden"(and accessible by code only), it
works fine, but the drawback is that it's removed from the list of
worksheets formatted to be hidden.
We'd like it to remain on the list of hidden sheets, but not readable
until "after" the user has selected it to be unhidden and the password
has been entered.

(aside: Have also locked the VBA Project for Viewing, so as not to
give away the code and password when other sheets are available.
Here's the code:


'Prompts for password to unhide this sheet.
Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Any thoughts are appreciated. Maybe a different approach is warranted?
Thanks again.
Pierre

 

[Don Guillett Excel MVP]

Am using this code to prompt the user for a password when attempting
to unhide a hidden sheet.  Unfortunately, the worksheet does become
visible as the dialogue box displays on the screen where the user
enters the password.  If they click outside of the prompt field, it
will all close, but not before the sheet can be read.
Now if the worksheet is "veryhidden"(and accessible by code only), it
works fine, but the drawback is that it's removed from the list of
worksheets formatted to be hidden.
We'd like it to remain on the list of hidden sheets, but not readable
until "after" the user has selected it to be unhidden and the password
has been entered.

(aside: Have also locked the VBA Project for Viewing, so as not to
give away the code and password when other sheets are available.
Here's the code:

'Prompts for password to unhide this sheet.
Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Any thoughts are appreciated. Maybe a different approach is warranted?
Thanks again.
Pierre

try
application.screenupdating=false
code
application.screenupdating=true

 

[Pierre]

Don, thank you so much. I entered the 2 lines. . .the result is the
desired action. One thing though:
If the user decides that rather than entering a password, and clicks
on CANCEL, the password prompt dialogue box goes away, but still
leaves the Hide/Unhide dialogue box at the bottom and nothing clears
the request. It locks up Excel; the only way out is to kill the
process with Task manager.
Here is the code I'm using. Thoughts? Pierre


'Prompts for password to unhide this sheet.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True


strPassword = InputBox("Enter password to remove hidden atttibutes")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select
Application.ScreenUpdating = True

On Error GoTo 0
End Sub