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)
---
Thanks for any advice you VBA RockStars can provide,
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?


--
Regards,
Tom Ogilvy



"Jay" wrote in message
...
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)
---
Thanks for any advice you VBA RockStars can provide,
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


"Tom Ogilvy" wrote:

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?


--
Regards,
Tom Ogilvy



"Jay" wrote in message
...
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)
---
Thanks for any advice you VBA RockStars can provide,
Jay

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


"Jay" wrote in message
...
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


"Tom Ogilvy" wrote:

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?


--
Regards,
Tom Ogilvy



"Jay" wrote in message
...
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)
---
Thanks for any advice you VBA RockStars can provide,
Jay

That completely nailed the issue, Tom. If I can ever do anything for you,
let me know.
--
Thanks a bunch,
Jay


"Tom Ogilvy" wrote:

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


"Jay" wrote in message
...
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


"Tom Ogilvy" wrote:

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?


--
Regards,
Tom Ogilvy



"Jay" wrote in message
...
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)
---
Thanks for any advice you VBA RockStars can provide,
Jay