I have the following function procedu


Function read_only_status()
read_only_status = activeworkbook.ReadOnly
End Function


This returns a value of "true" when the workbook is read-only and "false"
when it is not. This works fine when the function is first entered into a
cell. However, since the function doesn't refer to a cell, if the workbook
status is changed (from/to read-only) the function is not called and the
value is not updated.

Any suggestions as to how I can get the function called when the read-write
status changes or some other work around?

Thank you.

Try putting

Application.Volatile True

as the first line in the function.



"nathan" wrote in message
...
I have the following function procedu


Function read_only_status()
read_only_status = activeworkbook.ReadOnly
End Function


This returns a value of "true" when the workbook is read-only
and "false"
when it is not. This works fine when the function is first
entered into a
cell. However, since the function doesn't refer to a cell, if
the workbook
status is changed (from/to read-only) the function is not
called and the
value is not updated.

Any suggestions as to how I can get the function called when
the read-write
status changes or some other work around?

Thank you.

Add the line Application.Volitile. This will force the formula to re-evaluate
each time the spreadhseet is calculated. As an aside you should always
specify the return value of a function in order to make it more efficient.

Function read_only_status() as Boolean
appliation.volitile
read_only_status = activeworkbook.ReadOnly
End Function

--
HTH...

Jim Thomlinson


"nathan" wrote:

I have the following function procedu


Function read_only_status()
read_only_status = activeworkbook.ReadOnly
End Function


This returns a value of "true" when the workbook is read-only and "false"
when it is not. This works fine when the function is first entered into a
cell. However, since the function doesn't refer to a cell, if the workbook
status is changed (from/to read-only) the function is not called and the
value is not updated.

Any suggestions as to how I can get the function called when the read-write
status changes or some other work around?

Thank you.

Use Chip's code. He seems a little better at spelling... Volatile
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Add the line Application.Volitile. This will force the formula to re-evaluate
each time the spreadhseet is calculated. As an aside you should always
specify the return value of a function in order to make it more efficient.

Function read_only_status() as Boolean
appliation.volitile
read_only_status = activeworkbook.ReadOnly
End Function

--
HTH...

Jim Thomlinson


"nathan" wrote:

I have the following function procedu


Function read_only_status()
read_only_status = activeworkbook.ReadOnly
End Function


This returns a value of "true" when the workbook is read-only and "false"
when it is not. This works fine when the function is first entered into a
cell. However, since the function doesn't refer to a cell, if the workbook
status is changed (from/to read-only) the function is not called and the
value is not updated.

Any suggestions as to how I can get the function called when the read-write
status changes or some other work around?

Thank you.

The "application.volatile" does the trick. However, I now have another
problem.

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??


"Jim Thomlinson" wrote:

Use Chip's code. He seems a little better at spelling... Volatile
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Add the line Application.Volitile. This will force the formula to re-evaluate
each time the spreadhseet is calculated. As an aside you should always
specify the return value of a function in order to make it more efficient.

Function read_only_status() as Boolean
appliation.volitile
read_only_status = activeworkbook.ReadOnly
End Function

--
HTH...

Jim Thomlinson


"nathan" wrote:

I have the following function procedu


Function read_only_status()
read_only_status = activeworkbook.ReadOnly
End Function


This returns a value of "true" when the workbook is read-only and "false"
when it is not. This works fine when the function is first entered into a
cell. However, since the function doesn't refer to a cell, if the workbook
status is changed (from/to read-only) the function is not called and the
value is not updated.

Any suggestions as to how I can get the function called when the read-write
status changes or some other work around?

Thank you.