After Save - 1 more question
 

Thanks Tom. One more question -
What if the user selects Save As? Does that throw a monkey wrench in the
code?

"Tom Ogilvy" wrote in message
...
That would be the basic approach.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Thanks for all of your help. Just to confirm - by combining the
suggestions
of your posts, is the below code the proper way to perform the After
Save
Event? Thanks!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'<Optional - this would be before save code
application.enableevents = false
ThisWorkbook.Save
application.enableevents = true
'<Optional - this would be after save code
End Sub

After Save - 1 more question
 

It will, but the SaveAsUI argument will be true in that case so you can test
for it

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
End If
Else
ThisWorkbook.Save
End If
'<Optional - this would be after save code
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Tom. One more question -
What if the user selects Save As? Does that throw a monkey wrench in the
code?

"Tom Ogilvy" wrote in message
...
That would be the basic approach.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Thanks for all of your help. Just to confirm - by combining the
suggestions
of your posts, is the below code the proper way to perform the After
Save
Event? Thanks!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'<Optional - this would be before save code
application.enableevents = false
ThisWorkbook.Save
application.enableevents = true
'<Optional - this would be after save code
End Sub

After Save - 1 more question
 

Thanks Bob. Can I ask a follow up? The code you have works great. I have
'before save' code and 'after save' code executing when the user hits Save
ir SaveAs.
But, if the user makes some changes, and selects Close, Excel prompts if you
want to save changes. In that case, I would only want the 'before save'
event to trigger. Is that possible? Thanks!


"Bob Phillips" wrote in message
...
It will, but the SaveAsUI argument will be true in that case so you can
test
for it

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
End If
Else
ThisWorkbook.Save
End If
'<Optional - this would be after save code
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Tom. One more question -
What if the user selects Save As? Does that throw a monkey wrench in
the
code?

"Tom Ogilvy" wrote in message
...
That would be the basic approach.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Thanks for all of your help. Just to confirm - by combining the
suggestions
of your posts, is the below code the proper way to perform the After
Save
Event? Thanks!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'<Optional - this would be before save code
application.enableevents = false
ThisWorkbook.Save
application.enableevents = true
'<Optional - this would be after save code
End Sub

After Save - 1 more question
 

Steph,

I am sure it is possible. It would probably work alonge the lines of this,
but post your code if having problems

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Bob. Can I ask a follow up? The code you have works great. I
have
'before save' code and 'after save' code executing when the user hits Save
ir SaveAs.
But, if the user makes some changes, and selects Close, Excel prompts if
you
want to save changes. In that case, I would only want the 'before save'
event to trigger. Is that possible? Thanks!


"Bob Phillips" wrote in message
...
It will, but the SaveAsUI argument will be true in that case so you can
test
for it

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
End If
Else
ThisWorkbook.Save
End If
'<Optional - this would be after save code
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Tom. One more question -
What if the user selects Save As? Does that throw a monkey wrench in
the
code?

"Tom Ogilvy" wrote in message
...
That would be the basic approach.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Thanks for all of your help. Just to confirm - by combining the
suggestions
of your posts, is the below code the proper way to perform the
After
Save
Event? Thanks!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As
Boolean)
Cancel = True
'<Optional - this would be before save code
application.enableevents = false
ThisWorkbook.Save
application.enableevents = true
'<Optional - this would be after save code
End Sub

After Save - 1 more question
 

Bob,
Thanks to Tom O, I have the following:

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

Dim fName As Variant
On Error GoTo ErrHandler

Cancel = True
If SaveAsUI Then
fName = Application.GetSaveAsFilename()
If fName = "False" Then
Exit Sub
Else
'<Optional - Before Save Code
MsgBox ("Before save")
Application.EnableEvents = False
ThisWorkbook.SaveAs fName
Application.EnableEvents = True
'<Optional - After Save Code
MsgBox ("After save")
End If
Else
'<Optional - Before Save Code
MsgBox ("Before save")
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
'<Optional - After Save Code
MsgBox ("After save")
End If

ErrHandler:
Application.EnableEvents = True

End Sub

The Save and SaveAs work perfectly. But make a change to the workbook, then
hit Close. Obviously you are prompted to save or not, which I want. But,
if the user selects yes, the code kicks in and kind of throws the file into
a loop of prompts. I just want the "before save" to fire, then close the
workbook.


"Bob Phillips" wrote in message
...
Steph,

I am sure it is possible. It would probably work alonge the lines of this,
but post your code if having problems

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Bob. Can I ask a follow up? The code you have works great. I
have
'before save' code and 'after save' code executing when the user hits
Save
ir SaveAs.
But, if the user makes some changes, and selects Close, Excel prompts if
you
want to save changes. In that case, I would only want the 'before save'
event to trigger. Is that possible? Thanks!


"Bob Phillips" wrote in message
...
It will, but the SaveAsUI argument will be true in that case so you
can
test
for it

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
End If
Else
ThisWorkbook.Save
End If
'<Optional - this would be after save code
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Tom. One more question -
What if the user selects Save As? Does that throw a monkey wrench
in
the
code?

"Tom Ogilvy" wrote in message
...
That would be the basic approach.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Thanks for all of your help. Just to confirm - by combining the
suggestions
of your posts, is the below code the proper way to perform the
After
Save
Event? Thanks!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel
As
Boolean)
Cancel = True
'<Optional - this would be before save code
application.enableevents = false
ThisWorkbook.Save
application.enableevents = true
'<Optional - this would be after save code
End Sub