CODEWITHSUNDEEP
excelvbaexcel-2003
playercharlie
playercharlie

Reputation: 629

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!

Upvotes: 1

Views: 434

Answers (3)

user1204868
user1204868

Reputation: 606

I just remove the Cancel As Boolean

Private Sub Workbook_BeforeClose()
ThisWorkbook.Save
End Sub

Upvotes: 0

Shevy
Shevy

Reputation: 57

Why not just force-shut the document?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close savechanges:=True
End Sub

For what I was trying to do I am actually using savechanges:=False. Both ways worked.

Upvotes: 0

Jesse
Jesse

Reputation: 1935

You want:

Me.Save

In your workbook BeforeClose event.

Upvotes: 0

Related Questions