Excel VBA: On Close Save Prompt after ActiveX Resize Despite Thisworkbook.Saved=True

Question

I've run into an interesting situation with Excel, and I was wondering if anyone of you knew an answer / solution.

Setup: I have an empty excel worksheet with a ActiveX ToggleButton on it. In a VBA code, I change the width of the button to 0, and then change the width to 100. (Why I do that is a different question, its part of a larger project, however, this causes the problem).

Then I set the save-status of the workbook to true, without actually saving the workbook.

Normally, if I now closed the workbook, it would just close, without asking me wether I want to save. Now, due to the resizing, and even though the .Saved-Status is True, it asks me if I want to save when I close the workbook, and by clicking onto the close Icon of excel, the .Saved-Status is set to "False"

If you want to try for yourself, try the below steps. Alternatively, I uploaded for convenience the same file here: (https://filebin.ca/3aLbbRxMTdUs/SavePromptUponResize.xlsm)

1) create a new workbook and add a new ACTIVEX toggle button. 2) in the VBA code of the workbook, add the below code 3) save the workbook, close it, and reopen it. 4) You should see a messagebox and after clicking, as second one 5) Now the status of the workbook is "saved" 6) Try to close the workbook -> you will be prompted if you want to save 7) If you check the .saved status now, it would say "false"

Looking forward to your insights!

Private Sub Workbook_Open()
MsgBox "Now a macro will run and resize twice the button you see." & vbCrLf & "Afterwards, the status of the workbook will be set to 'saved'." & vbCrLf & "However, upon closing, excel will still prompt to save."
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets(1)


ws.Shapes("ToggleButton1").Width = 0
ws.Shapes("ToggleButton1").Width = 100


wb.Saved = True
MsgBox "Macro finished, save status is: " & wb.Saved


End Sub



Private Sub CheckSaveStatus()
MsgBox "Save status is: " & ActiveWorkbook.Saved
End Sub

Answer 1

The solution is as simple as this:

Private Sub Workbook_Open()
  MsgBox "Now a macro will run and resize twice the button you see." & vbCrLf & "Afterwards, the status of the workbook will be set to 'saved'." & vbCrLf & "However, upon closing, excel will still prompt to save."
  Dim wb As Workbook: Set wb = ThisWorkbook
  Dim ws As Worksheet: Set ws = wb.Sheets(1)
  Dim boolSaved As Boolean

  boolSaved = wb.Saved
  ws.Shapes("ToggleButton1").Width = 0
  ws.Shapes("ToggleButton1").Width = 100
  If boolSaved Then wb.Save

  MsgBox "Macro finished, save status is: " & wb.Saved

End Sub

This technique can be used anywhere in your code where you need to do something that 'dirties' the workbook. The beauty of it is that it preserves the save prompt if the user has modified the workbook.

As to why it's happening, best guess is - a(nother) ActiveX bug in Excel.

Answer 2

Please try the below:

Option Explicit

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer


Private Sub Workbook_BeforeClose(Cancel As Boolean)

If i = 1 Then

wb.Saved = False

Else

wb.Saved = True


End If

End Sub    


Private Sub Workbook_Open()


MsgBox "Now a macro will run and resize twice the button you see." & vbCrLf & "Afterwards, the status of the workbook will be set to 'saved'." & vbCrLf & "However, upon closing, excel will still prompt to save."

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)


ws.Shapes("ToggleButton1").Width = 0
ws.Shapes("ToggleButton1").Width = 100

End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
i = 1
End Sub

Answer 3

My experience has been that this Excel bug is triggered as a result of using VBA code to change the value of some properties of some ActiveX/OLE controls. I don't have a complete list of such properties but here's a few:

CommandButton:

• Enabled property

• Height property

TextBox:

• ForeColor property

• BackColor property

I use a generalized system (similar to Nikolaos Polygenis's solution), including extensive explanation due to this bug's subtlety, as follows:

1. In a standard module, define a project-global variable to indicate the no-save-needed condition: '************************************************************************************************************* 'EXCEL OLE/ACTIVE-X SAVE-BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND ' '(See the Workbook_BeforeClose event handler, in the ThisWorkbook module, for a complete explanation.) ' Public SuppressWkBkSaveMsg As Boolean ' 'END SAVE-BUG WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND '*************************************************************************************************************

2. In the ThisWorkbook module, place the following Workbook_BeforeClose event handler, with full explanation:

   Private Sub Workbook_BeforeClose(Cancel As Boolean) '************************************************************************************************************* 'EXCEL OLE/ACTIVE-X SAVE-BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND ' 'Excel has a very subtle bug in which, if you change the value of some properties of some ActiveX/OLE 'controls, the parent workbook's Saved property will not function correctly until the next actual workbook 'save event. That is, you can subsequently set the workbook's Saved property to True but Excel will still 'prompt the user about whether to save the workbook when closing it. In fact, if you check the value of the 'Saved property in a Workbook_BeforeClose event handler, it will be defined as False even though no changes 'have been made to the workbook after explicitly setting it to True! ' 'The most effective workaround is to defer the override of the workbook's Saved property until the Close event 'has been actually initiated: declare the project-global SuppressWkBkSaveMsg variable in a standard module, 'add this Workbook_BeforeClose event handler to the ThisWorkbook module, and then set SuppressWkBkSaveMsg to 'True whenever it's necessary to suppress Excel's user-inquiry about whether to save changes to the workbook 'before closing it. ' If SuppressWkBkSaveMsg Then Me.Saved = True ' 'END SAVE-BUG WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND '************************************************************************************************************* End Sub

3. In any location in the code, in any module, whenever it's necessary to suppress Excel's user-inquiry about whether to save changes to the workbook before closing it, set SuppressWkBkSaveMsg to True:

   'Do stuff that doesn't need to be saved, including modifications to OLE/ActiveX control-objects... ' '************************************************************************************************************* 'EXCEL OLE/ACTIVE-X SAVE-BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND - BUG WORKAROUND ' '(See the Workbook_BeforeClose event handler, in the ThisWorkbook module, for a complete explanation.) ' SuppressWkBkSaveMsg = True ' 'END SAVE-BUG WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND - END WORKAROUND '************************************************************************************************************* ' '...