Workbook Prompts for Changes Upon Closing When no Changes Were Made

1
votes

I have an Excel file that runs some ActiveX components. The downside is that it asks to save each time I close the file, even when I didn't make any changes. I've tried adding the following code to the ThisWorkbook object:

Private Sub Workbook_Open()
    ActiveWorkbook.Saved = True
End Sub

However, this doesn't seem to work. It still asks me to save the file when I try to close it without making changes.

Any help?

1
excelvbaactivex
Shouldn't it be ActiveWorkbook.Save, without "d"? - Basher
Check this link for some ideas to try. - Automate This
@Basher - No, Save will save it, Saved will set the flag. It should probably be on close event if the activex controls run after opening/interacting with workbook. - Automate This

1 Answers

0
votes

Ah, yes I have had to deal with this issue myself. Since these components run AFTER the workbook is opened (and after your Workbook_Open() event), you need to run your code when you're closing your workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ThisWorkbook.Saved = True

End Sub

Should be placed in the ThisWorkbook code module.

To avoid accidentally not saving the workbook when you actually update it, you could try setting your own global variable flag - which should be located in a standard module and have the Public scope. Afterwards, you can use the Workbook_SheetChange event to set this flag when you actually change any content within the workbook:

Standard Module: Add Pub Flag

Public bPromptSave as Boolean

Workbook Module: Event Handlers

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    bPromptSave = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    if not bPromptSave then ThisWorkbook.Saved = True

End Sub