VBA different BeforeSave for Save and Save As

[mrMadCat]

Hello,
I need a macro to act differently on events Save and Save As.
There is Workbook.BeforeSave functionality in VBA, but is there something like BeforeSaveAs? Or is there a way around?
Thank you.

 

[Teeroy]

Have a look at http://www.mrexcel.com/forum/excel-questions/484598-telling-difference-between-save-saveas.html.

 

[mrMadCat]

Thank you for the link. After the reading I understand my question deeper.

My situation: I save into separate file info - file Name, User name and time. It's like log of user sessions.
On save it writes into the log file the end time of the session.
The problem is that if user saves as - he actually creates new file and closes the old one. This means that in the log file there should be two lines:
1) closed session of the old file (which works just fine now)
2) new active session line for the newly created file.

And I still don't understand how to do it. The next code isn't enough as I actually need 2 actions: one before old file is closed, and another after the new file is open.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
'
' do stuff
'
End If
End Sub

 

[Teeroy]

The code sample checks for save (i.e. SaveAsUI = False) and responds. If you want to respond differently to a saveas just add an Else to the If Block, e.g.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
'
' do stuff for Save
'
Else
'
' do Stuff for SaveAs
'
End If
End Sub

 

[mrMadCat]

Yes, I understand how it works else. My question is a little more complicated. I need to do two actions (macro) on SaveAs event:
One with the old name/path of the file, and another with the new name of the file. How do I show VBA the difference in this moments when?

 

[Teeroy]

Set an object to the workbook, or extract it's details, in the Workbook_BeforeSave event and use a global variable or document property to keep the the saveas state. Then in the Workbook_Aftersave event you can check the saveas state and make other changes as required to the old file accessing the object (or reopening it from the file details) and to the new Workbook using the ThisWorkbook reference.

 

[mrMadCat]

Great! Thnx! Workbook_Aftersave did the job. Didn't know it existed.
Made a variable on BeforeSave event and checked for it on the AfterSave event:

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call Sub1
    If SaveAsUI Then
        SaveAS_Check = True
    End If
End Sub

Sub Workbook_AfterSave(ByVal Success As Boolean)
    If SaveAS_Check = True Then
        Call Sub2
        SaveAS_Check = False
    End If
End Sub

 

[Teeroy]

You're welcome.

I'm glad you got a solution that works for you.