Eliminating Save Dialog but still Saving Excel File

Hi ALL

I have managed to make backup copies under dates and times before closing excel each time by using the following statement:

ThisWorkbook.SaveCopyAs "C:\Backup\" & "Backup_" & Format(Now, "DDMMYYYY-HHMM") & ".xls"

However, this is insufficient - I would like to eliminate the 'save dialog' before closing and I achieved this by using the following statement:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close False
End Sub

Now, this is not ideal as it will not save the sheet - what I would like is to save the sheet on closing without Excel asking the user to save changes. Is this possible by any chance? If so, how? Thanks

Usman


Usman

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

This is very dangerous; if a user accidentally deletes a sheet, for example, he can no longer exit without saving.

Hi

Thanks!

I used:

ThisWorkbook.Save
ThisWorkbook.Saved = True

In order to accidentally protect the sheet from such problems - I felt it would be ideal to protect the new data that has been inputted (like a dynamic protection of data so to speak)? So if I enter something this week, I will not be able to make changes (whether accidental or not) to previous data i.e including that entered last week?

This way, it is safer and I also have a backup of the excel sheet each time it is closed plus it is automatically saved (without user intervention).

Any ideas on dynamic protection anyone? Thanks

Usman

ThisWorkbook.Save
ThisWorkbook.Saved = True

The first statement is the same as mine in the context of the ThisWorkbook module, and the second is redundant, but sure, that's fine.