Thread: Users with different permissions

Is it possible to have a locked spreadsheet that allows different unlocked cells depending on the user?

so for me it would be all unlocked,

user 2 would be column 2 unlocked, all else locked

user 3 would be column 3 unlocked, all else locked

etc, etc.

thanks

A few approaches, one is to base things off the NT username of the current user - the below would reside in ThisWorkbook Object in VBE:

Option Explicit
Const pwd = "Password"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Sheet1")
    .Unprotect pwd
    .Cells.Locked = True
    .Protect pwd
End With
End Sub

Private Sub Workbook_Open()
Dim strUser As String, boolOpen As Boolean
strUser = UCase(Environ("username"))
With Sheets("Sheet1")
    .Unprotect pwd
    .Cells.Locked = False
    Select Case strUser
        Case "X","S"
            'Admins
            boolOpen = True
        Case "Y","A"
            .Columns(2).Locked = True
        Case "Z"
            .Columns(3).Locked = True
        Case Else
            .Columns(2).Locked = True
            .Columns(4).Locked = True
    End Select
    If Not boolOpen Then .Protect pwd
End With
End Sub

Should give you something to work from... you would of course need to ensure Macros are enabled for this to work and/or ensure all cells are locked when the file is opened initially.

So given you have no idea who's downloaded your file how exactly do you intend to differentiate the access privileges ???

I think this is a non-starter unless you go down the route of some sort of IP based approach... out of my knowledge base.

Originally Posted by 1.zer0

someone logs onto my website and downloads the spreadsheet, makes amendments to only the section they are allowed (like the column example i gave) then they would upload it back to the website for others to do the same.

What happens if the second person downloads the spreadsheet BEFORE the first person has uploaded his amendments? Any changes made by the first user will be lost when the second user overwrites the file.

I would have thought separate spreadsheets for each use would be the best method. Then some form of macro to accumulate them together which might be what DonkeyOte was heading towards. This would assume no user needs to accesss another's data.

Well yes that's true but the verification is taking place on the web client rather than in XL environment... if using php you might want to think about using a database (MySQL) and web form for the data entry rather than a spreadsheet.