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

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

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" <unixverse@yahoo.com> wrote in message
news:1154692924.938618.247380@s13g2000cwa.googlegroups.com...
> 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
>

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


JON JON wrote:
> 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" <unixverse@yahoo.com> wrote in message
> news:1154692924.938618.247380@s13g2000cwa.googlegroups.com...
> > 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
> >

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 Torchia-Wells


"Joe HM" wrote:

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

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


--
Les Torchia-Wells


"Joe HM" wrote:

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

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


Les wrote:
> 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
>
>
> --
> Les Torchia-Wells
>
>
> "Joe HM" wrote:
>
> > 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
> >
> >