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.

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.

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.

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.

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.

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.