Lock Excel file so people are not in it when I need to edit it

[kevdragon1]

Hi all,

I have an important Excel file on our server that I need others to be able to see. This file needs to always stay in Read Only unless someone needs to edit it at which point they should enter a password, edit and close and save. This file can't be on a OneDrive folder, just a regular file explorer server.

I tried to Save As > Tools > General Options > Insert a Password to Modify but it did not work. Even when I set up a Password to Modify, Excel will often tell me that someone is in the file. This is a big pain point because at that point I have to contact the person that is in it so they can close the file or even worst try to guess who is in it if Excel did not mention who it was at the opening of the file.

What is even weirder is that most of the time the person will tell me that they are in Read-Only. So I don't understand how that is possible. We use our normal computers to open this file but also some virtual machines, maybe that is the issue?

Is there a fix to this? I just want people to be able to view the file. I need to be able to edit it without having them to exit the file every time they are in read-only.

Thanks

Edit: the error I usually get is something like : The document is locked for editing by another user

 

[Logit]

You can create a timer that will automatically close the file after X seconds or minutes of inactivity.

The following should be pasted in the ThisWorkbook module :

Option Explicit

Private Sub Workbook_Open()
'Starts timer when WB is opened.
    StartTimer
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'Checks for any activity on all sheets. If any data is entered, the timer is restarted.
    StartTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Checks for any activity of a Sheet Change. If a Sheet Change occurs, timer is restarted.
    StartTimer
End Sub

The following paste in a regular module :

Option Explicit

Const idleTime = 60 'seconds If NO activity of any kind occurs within 60 seconds, WB closes and auto saves.
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
      
    
    Application.Quit
    ActiveWorkbook.Close SaveChanges:=True
    
    Application.DisplayAlerts = True
End Sub

After one minute of inactivity, the workbook closes automatically.

 

[kevdragon1]

You can create a timer that will automatically close the file after X seconds or minutes of inactivity.

The following should be pasted in the ThisWorkbook module :

Option Explicit

Private Sub Workbook_Open()
'Starts timer when WB is opened.
    StartTimer
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'Checks for any activity on all sheets. If any data is entered, the timer is restarted.
    StartTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Checks for any activity of a Sheet Change. If a Sheet Change occurs, timer is restarted.
    StartTimer
End Sub

The following paste in a regular module :

Option Explicit

Const idleTime = 60 'seconds If NO activity of any kind occurs within 60 seconds, WB closes and auto saves.
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
     
   
    Application.Quit
    ActiveWorkbook.Close SaveChanges:=True
   
    Application.DisplayAlerts = True
End Sub

After one minute of inactivity, the workbook closes automatically.

Thanks for this. I will consider it. Would require me to convert every file to a macro-enabled. Was wondering if there is any native Excel feature to counter this?