prompt password for a worksheet

[vicky]

how can i prompt a user to enter a password when he tries to unhide a
hidden sheet . the workbook has to be password protected i.e when i
open a workbook all the hidden sheets have to be password protected .

 

[Bob Phillips]

You could re-purpose the unhide worksheet menu option.

How do they unhide, from the menu, or do you have an app doing it.

HTH

Bob

 

[Eduardo]

hI,
right click in the tab name, view code, copy the following one

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

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub

Change the password for yours, then hide the sheet, when sheet is going to
be unhide it will ask for the password

if this helps please click yes thanks

 

[vicky]

Thanks a lot For Replying . i have figured out the logic for that . i
just need a code to hide " Format" Menu tool Bar in excel . i.e user
should not be able to access to this menu .

 

[Bob Phillips]

Application.Commandbars(1).Controls("Format").Enabled = False

HTH

Bob

 

[Gord Dibben]

Make the worksheets "veryhidden"

They won't be available under Format>Sheet>Unhide.


Gord Dibben MS Excel MVP