Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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).ControlFor mat.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent "Changed ... Save?" Message after disabling Shape?
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" wrote in message oups.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).ControlFor mat.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent "Changed ... Save?" Message after disabling Shape?
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" wrote in message oups.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).ControlFor mat.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent "Changed ... Save?" Message after disabling Shape?
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).ControlFor mat.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).ControlFor mat.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).ControlFor mat.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent "Changed ... Save?" Message after disabling Shape?
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).ControlFor mat.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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent "Changed ... Save?" Message after disabling Shape?
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).ControlFor mat.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel 2003 is there a way to prevent "Save As" and "Print"? | Excel Discussion (Misc queries) | |||
Disabling "SAVE AS" option under "File" | Excel Discussion (Misc queries) | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
prevent Excel from popping-up an "OK" (information) message | Excel Programming | |||
Prevent "XYZ.doc is locked for editing" message | Excel Programming |