Changing Workbook to "Read Only" on ActiveWorkbook.Close

[LSM1604]

Hey,

I have a Save & Close Button and I was wondering if it was possible for when I click that button for the Workbook to Save itself as Read Only (Same file name and same directory), then close.

I currently have this...

ActiveWorkbook.Close SaveChanges:=True

The reason why I would like the file to become Read Only is because I would like the workbook to open as Read Only the next time it is opened.

Thanks,
LSM1604

 

[Michael M]

Don't know about the "Read only", but have you considered protecting ALL sheets on close instead, which will do the same thing ?

 

[LSM1604]

Hi,

Thank you for your reply. All of the sheets are protected. When the workbook is opened, I have a login screen. On the log in screen, there is a choice of "Edit Mode" or "Info Only Mode". I want to limit the number of people in Edit Mode at any one time to 1, so no conflicts can occur (Shared workbook cannot be used). If edit mode is selected then read/write mode is activated and a cell value is changed to "Currently In Use", the workbook then saves.

I ideally need something which either:

1) Saves the workbook as read only.
2) When it opens tells me if someone else already has the workbook open. Then I can use that information and the information from a the aforementioned cell value to see if the person opening the workbook can have write access.

The reason for this is to ensure that the workbook has not been closed incorrectly therefore keeping the saved value of "Currently In Use", which will prevent more than one user in write mode, and will also prevent no one from being able to go into write mode.

Hope that makes sense (it almost confused me!).

Thanks again,
LSM1604

 

[Michael M]

Hmm, you're right, I'm a little confused.
In answer to the points

1) Saves the workbook as read only.

My previous post would do that !!

2) When it opens tells me if someone else already has the workbook open. Then I can use that information and the information from a the aforementioned cell value to see if the person opening the workbook can have write access.

Excel does this by default when the workbook is opened by more than one person on a network
How about a workbook beforeclose event macro that either clears or changes the "in use" cell when it saves.