MrExcel Publishing
Your One Stop for Excel Tips & Solutions

is this possible....


Posted by newby on September 10, 2000 1:56 PM

let say we have 3 sheets in a workbook called abc.xls

the sheets names r 1,2,3..

i don't want user to look in sheet2...
but i don't want to hide it..so when
the user click the sheet2 tab..it will
prompt 4 password..but they can access sheet
1 n 3..is it possible..thanks


Posted by newby on September 11, 0100 11:34 AM

thanks celia..u r excellent..regards

Posted by george on September 11, 0100 3:26 PM

Re: why won't this work for me?

I saw this an realized i might be able to use it also.
I tried copying the code into a macro and I couldn't
get it to work. I am new to macros but am trying to
learn quickly.

I was able to copy other code in and it worked.

Help please.

Thanks

Posted by Celia on September 11, 0100 4:04 PM

Re: why won't this work for me?

George
You have to put it in the Workbook module, not in a normal module.
Celia

Posted by Celia on September 10, 0100 5:42 PM

the sheets names r 1,2,3.. i don't want user to look in sheet2...


Newby
Try the following. The password is "Only4Me". Change it to whatever you want.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim passWord As Variant
If ActiveSheet.Name = "Sheet2" Then
Application.EnableEvents = False
Sheets("Sheet1").Activate
passWord = Application.InputBox _
(Prompt:="Input password to activate Sheet2")
Else
Application.EnableEvents = True
Exit Sub
End If
Do
If passWord = "False" Then
Application.EnableEvents = True
Exit Sub
ElseIf passWord = "Only4Me" Then
Application.EnableEvents = False
Sheets("Sheet2").Activate
Application.EnableEvents = True
Exit Sub
Else
MsgBox "Invalid password"
passWord = Application.InputBox _
(Prompt:="Input password to activate Sheet2")
End If
Loop
End Sub

Celia