ExcelBanter - procedure continuously being called

I think the key to this is to find the right combination of conditional code
that will only change the background when it needs to be changed, instead of
continuously setting it (the setting of it is what boggs down the workbook).
i.e. the procedure checks the read-only status, if it is already in
read-only and the read-only background is already set, don't set it again.
If it is not already set, then set it.

I can't come up with the right statemens to do this. Thanks for all your
help.

"Tushar Mehta" wrote:

Interesting. As you may suspect, I had searched both XL and VBA help
for 'readonly' and 'toggle readonly' before posting.

So, it appears the OP has four choices -- other than the current one.

1) Do nothing if the readonly status is toggled by the user. That
person is explicitly opening the file as readonly or toggling it into
readonly status and then has a 'read only' indicator in the caption.

2) If the change (or opening of the file) is done programmatically,
that would provide the OP with the perfect opportunity to add the
desired watermark to the file. Of course, by doing so, a 'readonly'
file has been modified.

3) Use a OnTime routine that runs every so often and checks the status
of the workbook. If readonly add the desired watermark -- of course,
that will cause the book to be marked as 'changed.'

4) Hook the 'Toggle readonly' button to custom code possibly with a
technique along the lines of 'Hooking command bar control events" (pp.
237-?) in Professional Excel Development by Bullen, Bovey, and Green
(http://www.amazon.com/exec/obidos/AS...tusharmehtaco-
20/103-9755819-5239853).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, Notify:=True

I sometimes open workbooks that I don't want changed in read/write mode. So
I've added the "Toggle Read Only" icon to a toolbar. It's in the File category
in xl2003.

Tushar Mehta wrote:

I am curious. How does one "toggle" a workbook that is in read-write
mode into read-only mode?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Thanks for your response. I have previously tried the Workbook_Open option
and...of course...it only works when the workbook is opened. It doesn't work
when the workbook is already open in read/write and is toggled to read-only
(which in practice is what takes place 95% of the time). However, that still
may the best option, for lack of a better.

"Tushar Mehta" wrote:

If someone were to ignore the dialog box that forces one to explicitly
open a workbook as read-only and then ignore the caption that indicates
the read-only status, are you sure they would not ignore a watermark?

An option to consider is to use the Workbook_Open event procedure to
check the workbook's ReadOnly status. If it is true protect all the
sheets so that none can be changed. And, if you still want to use a
background image, use the same event procedure to do the needful.

Bottom line. If you must do something, do it just once when the
workbook is opened.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Kind of a long story and the result of not using proper methods I guess...

Multiple users have open and make changes to a single workbook throughout
the day. File sharing is not used. So, all but one user is viewing in
"read-only". If a read-only user wants to make changes he/she has to switch
to "read-write". Often, someone will not realize they are in "read-only" and
do a bunch of work only to realize they can't save the changes.

So, we are looking for a way to set up an obvious reminder, flag or
indicator (other than the text "read-only" on the header bar) that displays
when the workbook is in read-only mode. Changing the background is an idea
we came up with. Thanks.

"Tushar Mehta" wrote:

Since a workbook cannot switch status to/from read-only by magic, why
test its status continuously? So, maybe you should start by answering
the question "when/how does the status of the workbook change?"

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I am using "application.volatile" in a function procedure in order for the
procedure to be called. The function does not refer to a cell value and
therefore won't be called unless .volatile is used.

Basically, what I am trying to do is to set the worksheet background to a
particular .gif file when the workbook is changed to/from "read-only". Since
my workbook uses DDE links, it calculates continuously. Therefore, the
function is called continuously, slows the workbook down and makes the screen
flicker as it continuously sets the background picture.

I have separated this task into the following two functions to try to keep
the function that sets the background (function #2) to only run when the
read-only status (function #1) changes, but it still does it continuously:

#1
Function read_only_status() As Byte

Application.Volatile True
If ActiveWorkbook.ReadOnly = True Then
read_only_status = 1
End If
If ActiveWorkbook.ReadOnly = False Then
read_only_status = 2
End If

End Function

#2
Function set_background(status As Byte)

If status = 1 Then
Worksheets("a.book").SetBackgroundPicture "c:watermark.gif"
End If

If status = 2 Then
Worksheets("a.book").SetBackgroundPicture Delete
End If

End Function

Any suggestions as to how I can get this procedure to NOT run continuously
and still actually work?? Thank you.