Protecting individual sheets Excel 2003

[giles]

Hi all,

I have a workbook with a number of sheets. I would like to restrict access to individual sheets to designated people via a password. I have used Sheet Protection to restrict ranges in each sheet to control data entry, however what I would like to do is assign a password to each sheet so that to enter into the unprotected ranges of a sheet a password is required. The challenge is that the people I want to enter data are outside of the organisation.

 

[Datsmart]

This earlier thread might give you some ideas:
http://www.mrexcel.com/forum/showthread.php?p=1173936

It uses Environment usernames to limit read/write status, but you could use the same idea and have it only allow the worksheets you want allowed for individual users.

I would make the pages you do not want allowed "xlveryhidden" and then password protect the VBA project with your own password. Doing it this way would only allow certain pages based on their PC log-on username, they would not have to use a password in Excel at all.

 

[giles]

Thanks John,
because I don't use VBA code at all, I assume the code would look like this when I've completed it

Sub SetAsReadOnly()
'** Test for PC User Name
Dim strUser As String
strUser = Environ("giles")
'** MsgBox strUser
'**Set Read only File Access for each Office's specific version
Select Case strUser
'** Full Workbook Access
Case Is = "giles","dloubey"
If ActiveWorkbook.ReadOnly Then _
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, WritePassword:="admin"
'** Limit Access
Case Is <> "giles"
If Not ActiveWorkbook.ReadOnly Then _
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
End Select

End Sub

with giles being my user name and dloubey being a colleagues user name etc.
does this look right?

 

[giles]

Thanks John

I just realise this will secure access to a workbook, I was looking to retrict a worksheet within a workbook, would I be able to mdofy the code to say sheet instead of workbook?

 

[Datsmart]

The below three macros should do what you are asking.
The first macro calls either Hide or UnHide macros to hide or unhide worksheets.

Sub SetWorksheetsPerUser()
Dim strUser As String
'** Get PC User Name in variable
    strUser = Environ("USERNAME")

'**Set Worksheet Access for each Office's specific version
Select Case strUser
'** Full Workbook Access
Case Is = "giles", "dloubey"
    Call Un_HideListOfSheets
'** Limit Access
Case Is <> "giles"
    Call HideListOfSheets
End Select

End Sub

Sub HideListOfSheets()
Dim ws As Worksheet
    For Each ws In Worksheets
        Select Case Mid(ws.CodeName, 6)
            Case 1, 3, 4 'Sheets with codename 1, 3, and 4 will be hidden
                ws.Visible = xlVeryHidden
            Case Else
                ' do nothing
        End Select
    Next ws
End Sub

Sub Un_HideListOfSheets()
Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        Select Case Mid(ws.CodeName, 6)
            Case 1, 3, 4
                ws.Visible = True
            Case Else
                ' do nothing
        End Select
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Datsmart]

In the first macro above, change the line of code from,

Case Is <> "giles"

To:

Case Else

Note:
The Codename of a worksheet is the number that the sheet is given by Excel when the worksheet is created.
This is different than the worksheet index number which changes with that sheets position in the workbook.
The number will never change, not if the worksheet is moved or renamed.
That is what makes the codename desireable when you want code to always refer to a particular sheet.
The easiest way to see what a worksheet's codename number is, is by looking at the Project Explorer window in VBA editor.
Alt-F11 will open VBA editor, Ctrl-R will open the Project explorer window in the left panel.
The codename for sheet1 will show as "Sheet1(Sheet 1)", if you have changed the name, it will show as "Sheet1(MyName)".
Where MyName is the name you have changed it to.

 

[Cavedwella]

This is absolutley brilliant...

Is it possible to use a list of usernames with their access requirements on a hidden sheet rather than hard coding into the macro?

 

[Datsmart]

Instead of the line that assigns hard coded names use something like this:

Case Is = Sheets("Sheet1").Range("A1").Value, Sheets("Sheet1").Range("A2").Value

Where Sheet1 is the name of your hidden worksheet.

 

[giles]

Thanks John,

much appreciated