Make excel read-only 2

[nickjar2]

A very basic question, but I cannot seem to be able to do it.

I have some code that checks the name of an .xls and brings up a msgbox if the file is the master spreadsheet. I also want it to become readonly if it is the master spreadsheet. The only thing i could think of is:

thisWorkBook.readonly = true

but i assume that the readonly property in this instance is in itself readonly and cannot be set. Is there a way to make a workbook readonly in code?

Thnaks,

Nick

 

[JVFriederick]

Not sure what you mean by a workbook readonly in code.

I use the following :
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly ' Switch to Read only

 

[nickjar2]

Thanks JV.
No new code can be added but I can still key in info into the spreadsheet. Is there a way of stopping users from doing absolutely anything?

Thanks,

Nick

 

[DaleWatson123321]

Hi Nick,

Sometimes all of us overlook the obvious. So, just in case you did, ...what about protecting the entire workbook - with "Tool, Protection, Protect Workbook ?

Hope this is your solution.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[nickjar2]

Hi Dale,

Thanks for responding.
Most of the sheets within the workbook have an element of protection to prevent users from typing into certain cells. All users have a master spreadsheet. Once they open this master sh they are prompted to perform a 'Save as', but there is nothing stopping them from amending the Master file.

So I need something like:
if thisworkbook.name = "....xls" then
thisworkbook.readonly = true

If i have certain cells protected, can a bit of VB overwrite these settings an protect the whole workbook?

Hope this makes sense,

Nick

 

[DaleWatson123321]

Hi Nick,

Again, I need to eliminate the "easy" (obvious) route.

What about the option to change the properties of all the master files. While in Explorer, right-click on the file. Then under General, Attributes, check-off "Read-Only".

The user will still be permitted to make changes to the Master file (in the areas which are not protected), BUT when the user goes to save the file, he/she will be FORCED to save the file under a DIFFERENT name.

Hope this helps,

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[nickjar2]

I see. That will probably be a good solution. I would never have thought of that.

Thanks a lot Dale.