Protect each worksheet individually

[andreashermle]

Dear Experts:

I wonder whether the following is feasible:

I got a multi-sheet (6 Worksheets) workbook where the following
actions are to be performed via VBA

a) perform the action 'very hidden' on all the 6 worksheets, ie., no
worksheets are visible when file is opened.
b) An individual 'Open' password is to be assigned to all worksheets.
Each worksheet has got its own 'Open' Password
b) If workbook is opened an input box prompts the user to enter his
password which opens only his worksheet. The other ones are still
'very hidden'
c) After closing the file/workbook, the just worked on worksheet gets
'very hidden' again.

Is this feasible.

Thank you very much in advance for your great/professional help.

Regards, Andreas

 

[Master Blaster]

No, you cannot hide all sheets.
I would create an other worksheet (nr 7) which is not hidden and has
no data.

Then prompt for a password which opens (un-hide) only one sheet.

On close, hide all sheets (very hidden) exept nr 7.

please bear in mind that this is still not very safe.

regards

 

[Gord Dibben]

Please note off the top that Excel's internal security is very weak.

It is designed to lock out casual users and prevent accidental overwrites and
viewing.

Are you and your users on a network?

Could you make do with code that makes use of the login username to make visible
just that user's sheet when the workbook is opened?

Requires a blank "Dummy" sheet because you cannot hide all sheets in a workbook.

Sheetnames will be "username1heet", "username2sheet"

Paste this code into Thisworkbook Module.

Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
Select Case Environ("Username")

Case Is = Environ("Username"): Sheets((Environ("Username") _
& "Sheet")).Visible = True

End Select
Sheets("Dummy").Visible = False
Exit Sub
endit:
MsgBox "Incorrect Password"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

'To allow you as Administrator to see all sheets.
'
'In a general module...............

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub

Be sure to lock the project from viewing so's users cannot access the code.

If not on a network, the code can be revised to employ actual password inputs as
you asked.


Gord Dibben MS Excel MVP

 

[andreashermle]

Please note off the top that Excel's internal security is very weak.

It is designed to lock out casual users and prevent accidental overwritesand
viewing.

Are you and your users on a network?

Could you make do with code that makes use of the login username to make visible
just that user's sheet when the workbook is opened?

Requires a blank "Dummy" sheet because you cannot hide all sheets in a workbook.

Sheetnames will be "username1heet", "username2sheet"

Paste this code into Thisworkbook Module.

Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
Select Case Environ("Username")

      Case Is = Environ("Username"): Sheets((Environ("Username") _
      & "Sheet")).Visible = True

End Select
Sheets("Dummy").Visible = False
Exit Sub
endit:
MsgBox "Incorrect Password"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
       Sheets("Dummy").Visible = xlSheetVisible
           For Each sht In ActiveWorkbook.Sheets
           If sht.Name <> "Dummy" Then
      sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

'To allow you as Administrator to see all sheets.
'
'In a general module...............

Sub UnHideAllSheets()
    Application.ScreenUpdating = False
    Dim n As Single
    For n = 1 To Sheets.Count
        Sheets(n).Visible = True
    Next n
    Application.ScreenUpdating = True
End Sub

Be sure to lock the project from viewing so's users cannot access the code.

If not on a network, the code can be revised to employ actual password inputs as
you asked.

Gord Dibben     MS Excel MVP









- Show quoted text -

Hi Gord,

thank you very much for your swift help. I will give it a try and let
you know as soon as possible.

Regards, Andreas

 

[andreashermle]

Please note off the top that Excel's internal security is very weak.

It is designed to lock out casual users and prevent accidental overwritesand
viewing.

Are you and your users on a network?

Could you make do with code that makes use of the login username to make visible
just that user's sheet when the workbook is opened?

Requires a blank "Dummy" sheet because you cannot hide all sheets in a workbook.

Sheetnames will be "username1heet", "username2sheet"

Paste this code into Thisworkbook Module.

Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
Select Case Environ("Username")

      Case Is = Environ("Username"): Sheets((Environ("Username") _
      & "Sheet")).Visible = True

End Select
Sheets("Dummy").Visible = False
Exit Sub
endit:
MsgBox "Incorrect Password"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
       Sheets("Dummy").Visible = xlSheetVisible
           For Each sht In ActiveWorkbook.Sheets
           If sht.Name <> "Dummy" Then
      sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

'To allow you as Administrator to see all sheets.
'
'In a general module...............

Sub UnHideAllSheets()
    Application.ScreenUpdating = False
    Dim n As Single
    For n = 1 To Sheets.Count
        Sheets(n).Visible = True
    Next n
    Application.ScreenUpdating = True
End Sub

Be sure to lock the project from viewing so's users cannot access the code.

If not on a network, the code can be revised to employ actual password inputs as
you asked.

Gord Dibben     MS Excel MVP

HI Gord,

works like a charm. Thank you very much for your great and
professional help.

Regards, Andreas