Protect each worksheet individually
 

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

Protect each worksheet individually
 

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

Protect each worksheet individually
 

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



On Tue, 9 Nov 2010 03:15:19 -0800 (PST), andreashermle
wrote:

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

Protect each worksheet individually
 

On Nov 9, 5:34*pm, Gord Dibben wrote:
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

On Tue, 9 Nov 2010 03:15:19 -0800 (PST), andreashermle
wrote:



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- Hide quoted text -

- 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

Protect each worksheet individually
 

On 9 Nov., 17:34, Gord Dibben wrote:
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

On Tue, 9 Nov 2010 03:15:19 -0800 (PST), andreashermle
wrote:

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

HI Gord,

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

Regards, Andreas