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

kevdragon1

New Member
Joined
Mar 8, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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 :

VBA Code:
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 :

Code:
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.
 
Upvote 0
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 :

VBA Code:
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 :

Code:
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?
 
Upvote 0

Forum statistics

Threads
1,222,437
Messages
6,166,025
Members
452,008
Latest member
Customlogoflipflops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top