Solved: Save File routine

[snowdyce]

Is there a default excel routing that runs when a file is saved? I want to write some code that is run when the file is saved by the user. For Example I have Public Sub Auto_Open that runs when the files is opened. What is the routing to run when saved? Thanks.

[Paul_Hossler]

There are 2 workbook events that you probably want to use

In the VB editor, select 'This Workbook' and at the top right where it probably says (General) select Workbook


Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub
Private Sub Workbook_Open()

End Sub


Paul

[snowdyce]

Thanks Paul. I added that code along with this Msg Box, but when I hit the Save or Save As button it is not being prompted. Is there something I am missing.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lReply As Long
If SaveAsUI = True Then
lReply = MsgBox("Do you wish to save this workbook?", vbQuestion + vbOKCancel)
Cancel = (lReply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End Sub

[Paul_Hossler]

The way I understand it, SaveAsUI is = True if the FileSaveAs dialog was used to call

SaveAsUI = false if the WB is saved some other way, including F12



Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lReply As Long

If SaveAsUI Then
MsgBox "FileSave dialog called"

Else
lReply = MsgBox("Do you wish to save this workbook?", vbQuestion + vbOKCancel)
Cancel = (lReply = vbCancel)
If Cancel = False Then
Application.EnableEvents = False
Me.Save
Application.EnableEvents = True
End If
Cancel = True
End If
End Sub


Paul