Hi,
How to run a macro just after the workbook is saved?
Regards,
Dan
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
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
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
Can you tell me where the error was? And what kind of error?Originally Posted by dansam
Because it worked for me OK, so I wouldn't like to discard the idea so easily.
Jimmy
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
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]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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 more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
The problem is fixed by post #8 for me. The format was incorrect.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Posting Permissions
Reply With Quote
