Can .xls make itself ReadOnly?

[Jay]

I'm looking for transparent alternatives to the built-in procedures that make
a workbook 'read-only.' Specifically, I'm looking for a way for a workbook
(that is not read-only) to make itself read-only each time it opens.

I'm searching for this solution because in my application, it is confusing
for users to see the "Password" dialog that states that "...the file is
reserved by... Enter a password for write access, etc....". The standard
approach to suppressing this dialog with application.displayalerts=false does
not work when implemented in the WorkBook_Open event procedure of the
ThisWorkbook object.

Also, I've checked the File's ReadOnly attribute in Windows Explorer, but
when emailed to another user, this attribute gets cleared (unchecked)

 

[Tom Ogilvy]

Why don't you want to use the built in capability for Read only
recommended. It doesn't require any password. The user can reject it.

Your reserved by message is a bit confusing. Are multiple people trying to
use the workbook at the same time?

 

[Jay]

Hi Tom-
My workbook helps users append multiple text data files together to produce
a database list on a sheet. A custom Export procedure (assigned to a
button) saves the list sheet as a separate excel file and clears the list
preserving the template nature of the workbook so that it can be used to
produce another list (from another set of text source files).

When users see "Open as Read Only?" prompt, they think "Why would I open the
workbook as read-only if I'm going to be importing data into it; wouldn't I
need to save the workbook after I import data? I'm going to dismiss this
recommendation and open it as not Read-Only." Note: if you could customize
the 'read-only recommended prompt', I would agree that this would be the way
to go, but I don't think you can...).

As for the 'reserved by' message, that is produced when I save the workbook
with a writereservation (using Excel's File, SaveAs, Tools, GeneralOptions,
Password to modify). Opening a file saved with this general option produces
the 'reserved by...' message.
--
Jay

Why don't you want to use the built in capability for Read only
recommended. It doesn't require any password. The user can reject it.

Your reserved by message is a bit confusing. Are multiple people trying to
use the workbook at the same time?

 

[Tom Ogilvy]

If you can insure your users will enable macros, then you can use event code
to run this macro command:

ThisWorkbook.ChangeFileAccess mode:=xlReadOnly


Then not use the writereserved option.

--
Regards,
Tom Ogilvy

Hi Tom-
My workbook helps users append multiple text data files together to
produce
a database list on a sheet. A custom Export procedure (assigned to a
button) saves the list sheet as a separate excel file and clears the list
preserving the template nature of the workbook so that it can be used to
produce another list (from another set of text source files).

When users see "Open as Read Only?" prompt, they think "Why would I open
the
workbook as read-only if I'm going to be importing data into it; wouldn't
I
need to save the workbook after I import data? I'm going to dismiss this
recommendation and open it as not Read-Only." Note: if you could
customize
the 'read-only recommended prompt', I would agree that this would be the
way
to go, but I don't think you can...).

As for the 'reserved by' message, that is produced when I save the
workbook
with a writereservation (using Excel's File, SaveAs, Tools,
GeneralOptions,
Password to modify). Opening a file saved with this general option
produces
the 'reserved by...' message.

 

[Jay]

That completely nailed the issue, Tom. If I can ever do anything for you,
let me know.