How to prevent "Changed ... Save?" Message after disabling Shape?

[Joe HM]

Hello -

I posted this question before but I have narrowed down what the problem
is.

I have a workbook with a button that I want to disable when it is
opened in read-only (just to prevent people from pressing it). So I
use

If ThisWorkbook.ReadOnly Then
ThisWorkbook.Sheets("Sheet1").Shapes(1).ControlFormat.Enabled =
False

The problem is that this somehow triggers Excel to detect a change and
as whether to save the workbook upon closing.

I used ThisWorkbook.Saved = True after this but it did not make a
difference. I even used the Application.EnableEvents = False/True
around it.

Any ideas?

Thanks,
Joe

 

[JON JON]

Sorry I do not fully undestand your question but you have tried this kind of
code in the thisworkbook module?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

 

[Joe HM]

Yes ... if I put the Saved = True in _BeforeClose() and it it works
there but I still want Excel to prompt the user if they made any actual
changes to the data (even if opened in read-only).

The problem is that ThisWorkbook.Saved stays true after execution of
the _Open(). Only right before the _BeforeClose() is called, Excel
decides to set it to False (thus prompting the "...do you want to save"
message.

Thanks,
Joe

 

[Les]

You can use the custom properties of the worksheets to help.

part1
====
Add a custom property to each worksheet when the workbook is opened. In my
example I call it "Dirty"
--------------------
Private Sub Workbook_Open()

Dim x As Worksheet

If ThisWorkbook.ReadOnly Then
ThisWorkbook.Sheets("Sheet1").Shapes(1).ControlFormat.Enabled = False
For Each x In ActiveWorkbook.Worksheets
x.CustomProperties.Add _
Name:="Dirty", Value:="False"
Next x
End If
End Sub
--------------------

part 2
====

In each worksheet add code to the chnage event to update your custom property

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.CustomProperties(1).Value = "True"
End Sub

part 3
====

Finally, in the workbook close event, check your custom properties and set
the saved property if required.

Dim x As Worksheet

If ThisWorkbook.Sheets("Sheet1").Shapes(1).ControlFormat.Enabled = False Then
For Each x In ActiveWorkbook.Worksheets
If x.CustomProperties(1).Value = "True" Then
ActiveWorkbook.Saved = False
Exit For
Else
ActiveWorkbook.Saved = True
End If
Next x
End If

I ran this a few times and it seems to function like you require.
One annoyance is that I had to use the index value 1 to refer to the custom
property.
I assume that there is a way to access it by name, but I'll leave that to
you to try.


Hope this helps/works for you!

 

[Les]

Sorry, in my previous post I forgot to add a check for custom properties in
the worksheet change. Please substitute this, or an error will occur if the
workbook is not opened in read-only mode:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.CustomProperties.Count <> 0 Then
ActiveSheet.CustomProperties(1).Value = "True"
End If
End Sub

 

[Joe HM]

Great ... I'll go ahead and give that a try. Too bad, though, that the
ThisWorkbook.Saved property does not work in that case.

Thanks a lot!
Joe