Search code examples
excelvbaexcel-2003

Saving Personal.xls automatically

I use Excel 2003 on a Windows 7 Professional setup. In my Personal.xls file, I have compiled snippets of code/formulae that I have picked up from different places, to be available for ready reference when I use Excel. These contain a number of volatile functions such as cell(), rand(), today() etc. As a result, when I close Excel, it asks me whether I would like to save Personal.xls.

I would like to keep my Personal.xls as it is, and yet disable the popup somehow. I am fine with saving, not saving, either way, as I won't be changing Personal.xls.

I have tried the following code in my personal.xls in the Workbook_BeforeClose section

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

But it doesn't seem to work. I have tried some variations, including .Save and .Saved=True, and also tried to alternatively use ThisWorkbook., Me. & Workbooks("PERSONAL.xls"). However, Excel still asks me save Personal.xls

I have also tried to disable calculations in my Personal.xls viz.

Private Sub Workbook_Open()
For Each ws In ThisWorkbook.Worksheets
ws.EnableCalculation = False
Next
End Sub

This doesn't solve the problem either. Finally I tried to do a 'ThisWorkbook.Save' after changing calculation mode to manual, but that doesn't change anything either.

Am I missing something here? Any advice would be appreciated. Thanks in advance!

Solution

  • You want:

    Me.Save

    In your workbook BeforeClose event.