Prevent people from opening a read only

[Metaripley]

I have a excel workbook where multiple people work in (not shared).

But I want to prevent that people create data and then dont save. I can make a macro for them to automatically save every change they do.
But when people work in a read only version (because someone else has the file open) they cannot save or save a local version which is not allowed.

So can i prevent them from opening the file when it is read only? So they first have to ask the other person to close it before they can open it and everything is always saved?

 

[oldbrewer]

Explain to your people that they MUST NOT work in a read only version because changes will be lost when they close the read only version. Get them all to sign a statement saying that they understand this instruction, and will obey it. This should "remind" them not to do it.

 

[Metaripley]

People will be people, I dont know ho is or is going to use it.
I just need a foolproof excel.

I found this online but it is not working:

Sub Workbook_Open()    If ActiveWorkbook.ReadOnly Then ActiveWorkbook.Close False
    End If
  
  End Sub

 

[oldbrewer]

if you are using workbook1, and I try to open workbook1, I am offered a read only copy. I say yes, and it opens, an exact copy of the workbook1, maybe before you opened it. So you need to figure out how to run a macro in my read only, as soon as I open it. I believe there is a way to trigger a macro with any key stroke, ******* or something. But beyond me, sorry.

 

[Fluff]

Hi
Try this

Sub Workbook_Open()

    If Me.ReadOnly Then
        MsgBox "This file is readonly & will close"
        ActiveWorkbook.Close False
    End If
  
End Sub

It will bring up a message box to alert the user what is happening.
Otherwise they may just think something has gone wrong, & keep trying to open it.

 

[oldbrewer]

Well done Fluff !

 

[Metaripley]

Hi
Try this

Sub Workbook_Open()

    If Me.ReadOnly Then
        MsgBox "This file is readonly & will close"
        ActiveWorkbook.Close False
    End If
  
End Sub

It will bring up a message box to alert the user what is happening.
Otherwise they may just think something has gone wrong, & keep trying to open it.

Thanks! Works great!
(works only in sheet code and not in workbook code)

 

[Fluff]

Thanks! Works great!

Glad to help & thanks for the feedback.

Not quite sure what you mean by

(works only in sheet code and not in workbook code)

The code supplied has to go into the ThisWorkbook module

 

[Metaripley]

Sorry, my mistake it works under "ThisWorkbook".