Depending on user, can we protect the workbook (or unprotect it)?

[moogthemoog]

Hi

Is there a way of automatically opening the spreadsheet as Read-only depending on the user ID?

Alternatively, if the spreadsheet is protected by default, is there a way of unprotecting it when opening, depending on the user ID?

Thanks
Jon

 

[RatExcel]

It depends on what you mean by UserID. You can get currently logged in user name using Environ function:

Sub TestSub()
    Debug.Print Environ("UserName")
End Sub

Let's say your user name is "JohnDoe". If you want the spreadsheet to be opened as read-only for all other users and in read-write mode only for you, you can use below code:

Private Sub Workbook_Open()
    If Environ("UserName") = "JohnDoe" Then
        Application.DisplayAlerts = False
        On Error Resume Next
        ThisWorkbook.ChangeFileAccess xlReadWrite
        On Error GoTo 0
        Application.DisplayAlerts = True
    Else
        Application.DisplayAlerts = False
        On Error Resume Next
        ThisWorkbook.ChangeFileAccess xlReadOnly
        On Error GoTo 0
        Application.DisplayAlerts = True
    End If
End Sub

or to Protect/Unprotect:

Private Sub Workbook_Open()
    If Environ("UserName") = "JohnDoe" Then
        On Error Resume Next
        ThisWorkbook.Unprotect Password:="MyPassIsSuperStrong"
        For Each sh In Sheets
            sh.Unprotect Password:="MyPassIsSuperStrong"
        Next
        On Error Resume Next
    Else
        On Error Resume Next
        For Each sh In Sheets
            sh.Protect Password:="MyPassIsSuperStrong", DrawingObjects:=True, Contents:=True, Scenarios:=True
        Next
        ThisWorkbook.Protect Password:="MyPassIsSuperStrong", Structure:=True, Windows:=True
        On Error GoTo 0
    End If
End Sub

 

[moogthemoog]

Excellent, that looks great. I will give it a go.

I'd never heard of the Environ function.

 

[moogthemoog]

Hi, I implemented the second solution (Protect/Unprotect). Code below.

Because not every user has enabled Macros in their Trust settings, I have also put Close event code to make sure all sheets are protected. However, because this is a Shared workbook, is this likely to cause a problem.
It appears that a non-authorised user has read write access to the spreadsheet, so I'm not sure what to do (apart from abandon this attempt!)

Can you help?

Thanks
Jon

Private Sub Workbook_Open()
    Dim vUser As String
    vUser = Environ("UserName")
    Application.ScreenUpdating = False
    ActiveWindow.WindowState = xlMaximized
    Select Case vUser
    
    Case Is = "JPW", "PB", "CS"
        On Error Resume Next
        ThisWorkbook.Unprotect Password:="St0pChang1ng"
        For Each sh In Sheets
            sh.Unprotect Password:="St0pChang1ng"
        Next
        On Error Resume Next
    Case Else
        On Error Resume Next
        For Each sh In Sheets
            sh.Protect Password:="St0pChang1ng", AllowSorting:=True, AllowFiltering:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
        Next
        ThisWorkbook.Protect Password:="St0pChang1ng", Structure:=True, Windows:=False
        On Error GoTo 0
        MsgBox "Cells Protected (you may still Filter cells); see JPW if you need write access"
    End Select
    Application.ScreenUpdating = True
    Me.Saved = True
    
End Sub

Private Sub Workbook_BeforeClose(CANCEL As Boolean)
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each sh In Sheets
        sh.Protect Password:="St0pChang1ng", AllowSorting:=True, AllowFiltering:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next
    ThisWorkbook.Protect Password:="St0pChang1ng", Structure:=True, Windows:=False
    On Error GoTo 0
    ActiveWindow.WindowState = xlMaximized
    Application.ScreenUpdating = True
End Sub