PDA

View Full Version : Solved: Run macro after file save



dansam
03-07-2007, 01:33 AM
Hi,
How to run a macro just after the workbook is saved?
Regards,
Dan

Bob Phillips
03-07-2007, 01:42 AM
Caters for both Save and SaveAs



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

JimmyTheHand
03-07-2007, 02:02 AM
And an alternative to Bob's solution:

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

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

dansam
03-07-2007, 05:28 AM
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

JimmyTheHand
03-08-2007, 03:16 AM
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

dansam
03-09-2007, 09:42 AM
Hi jimmy,
the error was "Error 13 : Type Mismatch"

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

lucas
03-09-2007, 09:46 AM
Sounds like you need to change this part to match your macro that you wish to run:
"MyMacro"

JimmyTheHand
03-09-2007, 11:18 AM
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.

lucas
03-09-2007, 11:28 AM
The problem is fixed by post #8 for me. The format was incorrect.