thisworkbook.saved changes inexplicably

[tomluigi]

(Note: I have already posted this question in other groups but have not received any response)

My goal is to close a file without being prompted to save after changing a shape, specifically the size of a button; to this end, after changing the button, I set Thisworkbook.saved to True but if I close the file I am still prompted to save.

NB I don't want to turn off the warnings

NB in the real application, after changing the shape, the user can or cannot change other things before closing, so without this VBA reset I don't know if I have to save or not and I don't want to waste time and annoy the user if it's not the case

To reproduce the problem simply create an empty file, add a ActiveX control button on the sheet, save it and then

1) open the file
2) manually change the size of the button by pressing the button with the square (the design mode)
3) from the immediate window launch

ThisWorkbook.Saved = True

4) optional, verify that this has had an effect by typing

Debug.Print ThisWorkbook.Saved

5) it will answer "True"
6) try to close the sheet
it will ask if we want to save the changes!!!
7) answer Cancel
8) type again

Debug.Print ThisWorkbook.Saved

it will answer "False" !!!

If instead of the command 3) you do a real save all it will work as expected (i.e. at point 6 the file will close without any questions)

So what else must be done in VBA to fully simulate the Save command?

 

[Fluff]

Note: I have already posted this question in other groups but have not received any response

Thanks for letting us know, but you need to provide links to those other questions. Thanks

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Skyybot]

You will have to use Application.DisplayAlerts = False in order to not get the pop up. You can use the BeforeClose event to check the value of Workbook.Saved property and show or not show pop up accordingly.

 

[tomluigi]

You will have to use Application.DisplayAlerts = False in order to not get the pop up. You can use the BeforeClose event to check the value of Workbook.Saved property and show or not show pop up accordingly.

Thanks for the suggestion but, as written, the problem is precisely in the incorrect value that the workbook.saved property assumes, so I can't use what you indicated.

 

[Skyybot]

Okay. Well let me ask this: Would you want the size of the button you changed to be saved when you close?

 

[Skyybot]

FYI - The Workbook.Saved property will be set to False if ANYTHING in the Workbook changes. You change a cell value - False. You change the size of a Shape - False. You change the value of a cell to Bold font - False. If you make a change you will be prompted to Save on exit. What exactly are you trying to achieve?

 

[tomluigi]

Thanks for letting us know, but you need to provide links to those other questions. Thanks

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry, below the links
stackoverflow
ozgrid
excelforum

 

[tomluigi]

FYI - The Workbook.Saved property will be set to False if ANYTHING in the Workbook changes. You change a cell value - False. You change the size of a Shape - False. You change the value of a cell to Bold font - False. If you make a change you will be prompted to Save on exit. What exactly are you trying to achieve?

the real problem is a bit long to explain, here I highlighted the problem:
I make changes that should not be seen as such and therefore after having made them (like shape modification) I reset the value of .saved
I therefore leave the control to the user
=>
- if it does NOTHING the file must close without questions, as hypothesized in the sequence above
- if it makes any changes, such as those indicated by you, is correct asking to save before close

 

[Skyybot]

If you're changing the size of the shape programmatically then just enter the Workbook.Save statement after the change is made. And FYI - if you're looking for a real solution to your real problem then you best get to typing. Regardless of explanation length.

 

[tomluigi]

If you're changing the size of the shape programmatically then just enter the Workbook.Save statement after the change is made. And FYI - if you're looking for a real solution to your real problem then you best get to typing. Regardless of explanation length.

Exactly what I did! See points 2 and 3 BUT .... I'm here asking for help because it doesn't work, as indicated in point 6