Remove option to open file as read only

[Repent]

I'm looking for a solution to remove the option for a user to open a file as read only when someone else already has the file open. Ideally, the file wouldn't open and the user would get an error message to try again later. Better yet would be to simply remove the open as read only and only have the notify and close option available.

I have users who continue to open read only copies, plug in lots of data, then save it as something else.

 

[chergh]

I'm not in front of a computer with excel just now but this may work.

You need to use the following code in the "open" event of the workbook

dim blnReadonly as boolean

blnReadonly = thisworkbook.readonly

if blnReadonly = true then
   application.quit
end if

 

[chergh]

It's also worth noting this isn't foolproof. If the person chooses not to enable macros when they are opening the file then the code won't execute, and there isn't much you can do about that.

 

[Repent]

I'm not in front of a computer with excel just now but this may work.

You need to use the following code in the "open" event of the workbook

dim blnReadonly as boolean

blnReadonly = thisworkbook.readonly

if blnReadonly = true then
   application.quit
end if

I'm new to the whole macro scene. Can you please elaborate on "You need to use the following code in the "open" event of the workbook"

thank you so much;
chris

 

[chergh]

Open excel and press alt-f11, this opens the visual basic editor (VBE). In the vbe you will have a set of icons in a window titled "VBA Project" or similar. There should be an expanded list and there should be something titled "ThisWorkbook", double click on it.

There will be 2 combo boxes near the top of the screen. On the leftmost of these select "workbook" and select "Open" from the rightmost.

Between where it says "Private Sub Workbook Open()" and "End Sub" insert the code I posted.

 

[Repent]

that worked perfectly!!!thanks a million!!

chris

 

[chergh]

You may want to change the code to the following:

dim blnReadonly as boolean

blnReadonly = thisworkbook.readonly

if blnReadonly = true then
   msgbox("Application may not open in read only mode, contact <you> for information")
   application.quit
end if

This adds a pop up box to explain why the application is closing, otherwise your users won't know why it is closing so suddenly.