Password protect sheet from viewing
 

I'm using Excel 2007

I have a team of sales individuals and have created a workbook which
contains a worksheet for each individual. When they open it I only want them
to be able to view their corresponding worksheet. I would like them to do the
following:
1. Open workbook
2. Enter password to see their corresponding worksheet.
2. Enter data
4. Save workbook
5. Send back to me.

Password protect sheet from viewing
 

Sample code.......................

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible with a large message
stating "By disabling macros you have rendered this workbook unusuable.
Please close and re-open with macros enabled"

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook, just code to make a user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False
End Select
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 to see all sheets and edit them.

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

Naturally you want all this code invisible to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a password.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 14:21:01 -0700, Enrique
wrote:

I'm using Excel 2007

I have a team of sales individuals and have created a workbook which
contains a worksheet for each individual. When they open it I only want them
to be able to view their corresponding worksheet. I would like them to do the
following:
1. Open workbook
2. Enter password to see their corresponding worksheet.
2. Enter data
4. Save workbook
5. Send back to me.

Password protect sheet from viewing
 

I love your idea, checking the user name they've logged in with would be
awesome. The problem is I can't seem to make it work. I am new to Visual
Basic and have tried every which way to make your program work. I was
wondering if you could give me an exact example of what the code would look
like with three users. The user names would be bhall, gclinton, aburton. The
sheets in the workbook are named after their user names. Also, is there
anything I will need to have the IT guys do to make this work on the network?

"Gord Dibben" wrote:

Sample code.......................

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible with a large message
stating "By disabling macros you have rendered this workbook unusuable.
Please close and re-open with macros enabled"

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook, just code to make a user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False
End Select
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 to see all sheets and edit them.

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

Naturally you want all this code invisible to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a password.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 14:21:01 -0700, Enrique
wrote:

I'm using Excel 2007

I have a team of sales individuals and have created a workbook which
contains a worksheet for each individual. When they open it I only want them
to be able to view their corresponding worksheet. I would like them to do the
following:
1. Open workbook
2. Enter password to see their corresponding worksheet.
2. Enter data
4. Save workbook
5. Send back to me.

Password protect sheet from viewing
 

This was extremely helpful. It took me awhile to get it working but now it
works like a charm. Thanks Mr. Dibben (Excel MVP)

"Gord Dibben" wrote:

Sample code.......................

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible with a large message
stating "By disabling macros you have rendered this workbook unusuable.
Please close and re-open with macros enabled"

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook, just code to make a user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False
End Select
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 to see all sheets and edit them.

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

Naturally you want all this code invisible to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a password.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 14:21:01 -0700, Enrique
wrote:

I'm using Excel 2007

I have a team of sales individuals and have created a workbook which
contains a worksheet for each individual. When they open it I only want them
to be able to view their corresponding worksheet. I would like them to do the
following:
1. Open workbook
2. Enter password to see their corresponding worksheet.
2. Enter data
4. Save workbook
5. Send back to me.

Password protect sheet from viewing
 

Happy to help.

Thanks for the feedback.


Gord

On Mon, 13 Apr 2009 22:14:01 -0700, Enrique
wrote:

This was extremely helpful. It took me awhile to get it working but now it
works like a charm. Thanks Mr. Dibben (Excel MVP)

"Gord Dibben" wrote:

Sample code.......................

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible with a large message
stating "By disabling macros you have rendered this workbook unusuable.
Please close and re-open with macros enabled"

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook, just code to make a user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False
End Select
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 to see all sheets and edit them.

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

Naturally you want all this code invisible to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a password.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 14:21:01 -0700, Enrique
wrote:

I'm using Excel 2007

I have a team of sales individuals and have created a workbook which
contains a worksheet for each individual. When they open it I only want them
to be able to view their corresponding worksheet. I would like them to do the
following:
1. Open workbook
2. Enter password to see their corresponding worksheet.
2. Enter data
4. Save workbook
5. Send back to me.