Forum Discussion
AOSPWB
Nov 16, 2021Brass Contributor
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 ...
AOSPWB
Nov 16, 2021Brass Contributor
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
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