macros

B

Becca

I am trying to create a workbook where sheet 1 can be viewed by all but to
click on the other sheet tabs and open the sheet you need to enter a
password. any ideas?
 
U

ufo_pilot

If the sheet can be invisibe, then you can use this:

Format > Sheet > Hide

Tools> Protection> Protect workbook
 
B

Becca

this seems a lengthy way to do it. is there not an easier way rather than
having to keep un-hiding the sheets?
 
B

Bob Phillips

Here is some code. It is at best basic

Option Explicit

Private Sub Workbook_Open()
Worksheets("Sheet1").Activate
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ans
Dim sh2 As Worksheet
Application.EnableEvents = False
If ActiveSheet.Name <> "Sheet1" Then
Set sh2 = ActiveSheet
Sh.Activate
Application.ScreenUpdating = False
ans = Application.InputBox("Supply password")
If ans <> False Then
If ans = "password" Then
sh2.Activate
End If
End If
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Becca

thank you :)

Bob Phillips said:
Here is some code. It is at best basic

Option Explicit

Private Sub Workbook_Open()
Worksheets("Sheet1").Activate
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ans
Dim sh2 As Worksheet
Application.EnableEvents = False
If ActiveSheet.Name <> "Sheet1" Then
Set sh2 = ActiveSheet
Sh.Activate
Application.ScreenUpdating = False
ans = Application.InputBox("Supply password")
If ans <> False Then
If ans = "password" Then
sh2.Activate
End If
End If
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
A

Ams

I have one question regarding this thread.
How can we protect one sheet with one password and different password for
other. For example, I have 4 excel sheets, I would like to give different
password for each. Lets say sheet1 is default active sheet. Now when I enter
password as "password" from your coding, I can open sheet2 and sheet3. But
how can i write code so that If I enter password as "pass" then only I can
open sheet3.

Other thing is, can we set one master password which can open all sheets at
a time. My requirement is: master password should open all sheets but there
should be different password for different sheet, so user who knows the
password for individual sheet can only open that individual sheet. Please
help me to do this.

Thank you.
 
Top