controlling how the file is saved

[K. Georgiadis]

The other users of a shared workbook have the tendency to add a date suffix to the file name each time they edit it which creates problems because the workbook is linked to another workbook to which it supplies data.

Is there a way to automatically save the workbook each time an edit has been made, with the same file name, bypassing the message box "Do you want to save changes you made to XXXX?" ?

I realize that there are other ways to change the file name but this device might provide a useful first line of defence.

[Ken Puls]

Hi there,

Well, you can test the SaveAsUI property of the Workbook_BeforeSave method as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
ThisWorkbook.Save
Cancel = True
End If
End Sub

That will prevent users from saving the file under a different name, but it will prevent it always. If you actually have reason to do so, you'll need to disable the code.

You will still get the message asking you if you want to save if you try to close a modified workbook, though.

If you want to save the workbook every time (dangerous), then you'd add this routine to the ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

I would NOT advocate doing this, though. It means that you have no way of bailing out of the workbook without saving changes if you messed something up.

HTH,

[K. Georgiadis]

Thanks Ken. I'll test it before I implement it. Perhaps I need to have a little more confidence in the other users!

[Ken Puls]

Perhaps I need to have a little more confidence in the other users!

LOL! I think that might be more risky that the code above. ;)