P
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
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