Solved: Run macro after file save

Hi,
How to run a macro just after the workbook is saved?
Regards,
Dan

Caters for both Save and SaveAs

[vba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFile
Application.EnableEvents = False
'<Optional - this would be before save code>
Cancel = True
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> False Then
ThisWorkbook.SaveAs sFile
'<Optional - this would be after save code>
End If
Else
ThisWorkbook.Save
'<Optional - this would be after save code>
End If
Application.EnableEvents = True
End Sub
[/vba]

And an alternative to Bob's solution:

[vba]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now, "MyMacro", TimeValue("2099.12.31 23:59:59"), True
End Sub
[/vba]
Explanation:
Excel is not ready while save is in process. Setting OnTime to Now will wait until Excel is ready (i.e. save is finished), but no longer than December 31th, 2099. (long enough, I think :)) then execute the sub called "MyMacro"

You may have to alter "2099.12.31 23:59:59", as I'm not sure if this datetime format is compatible with your reginal settings.

Jimmy

Hi everyone,
I found some error in Jimmy's code but thanks Jimmy for help.
And Thankyou very much xld for a great help . that code worked !

dan

Quote:

---

Originally Posted by dansam

Hi everyone,
I found some error in Jimmy's code but thanks Jimmy for help.

---

Can you tell me where the error was? And what kind of error?
Because it worked for me OK, so I wouldn't like to discard the idea so easily.

Jimmy

Hi jimmy,
the error was "Error 13 : Type Mismatch"

Am I doing any thing wrong?
Please point me to that !!
Dan

Sounds like you need to change this part to match your macro that you wish to run:
[VBA]"MyMacro"[/VBA]

Or, as I pointed out earlier, TimeValue("2099.12.31 23:59:59") might have to be altered. I don't know what date/time format is accepted by US Excel.
Maybe TimeValue("12/31/2099 23:59:59"). I really don't know.

The problem is fixed by post #8 for me. The format was incorrect.