Forum Discussion

AOSPWB's avatar
AOSPWB
Brass Contributor
Nov 16, 2021

Excel File Protection for Multiple users

Due to the type of data we have in excel, we must open in the application and cannot use the web based version.  However we are encountering some issues of multiple people in the same excel file and working and when saving they are over writing each other.  Yes, there is a indication at top right of the screen if you are not the only person in the file, but lets face it... SharePoint is new to most and we all have deadlines so they are doing what they can as fast as they can.

What I would like to do is have some type of notice more visibly show if a person opens a file that is already open by someone else and either not let them open the file or open in a read only version (in the event they need data and not needing to make an update).

Perhaps this is more of an excel forum question but thought I would try here as well.  

 

Thank you to all for any guidance as this is starting to get messy with "restores" or "resets" and extra time for people.  Not to mention I spend more time being the ref vs working on things here lately.

files
security
SharePoint Online

1 Reply

  • AOSPWB's avatar
    AOSPWB
    Brass Contributor
    Nov 16, 2021
    Having some success in the below... the "open" seems to be working fine, but the close does not seem to be working... any ideas would be appreciated...

    Private Sub Workbook_Open()
    Application.Goto ThisWorkbook.Sheets("user").Range("A1")
    If IsEmpty(Range("A1").Value) = True Then
    Range("A1").Value = Environ$("UserName")
    Application.Goto ThisWorkbook.Sheets("User").Range("A4")
    'disable auto save start
    Dim AutoSv As Boolean
    If Val(Application.Version) > 15 Then
    AutoSv = ActiveWorkbook.AutoSaveOn
    If AutoSv Then ActiveWorkbook.AutoSaveOn = False
    AutoSv = ActiveWorkbook.AutoSaveOn
    End If
    'disable auto save end
    ElseIf IsEmpty(Range("A1").Value) = False Then
    If MsgBox("Open in Read Only?", vbYesNo, "File Open By Another User") = 6 Then
    ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
    Else
    ThisWorkbook.Close

    End If
    End If
    End Sub
    Private Sub Workbook_Close()
    Application.Goto ThisWorkbook.Sheets("user").Range("A1")
    If Range("A1").Value = Environ$("Username") Then
    If MsgBox("Save File?", vbYesNo, "Saving Option") = 6 Then
    Range("A1").Value.Clear
    ThisWorkbook.Save
    ThisWorkbook.Close
    Else
    Range("A1").Value.Clear
    ThisWorkbook.Close
    End If
    End If

Resources