After Save Event - Confirm

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

That would be the basic approach.

--
Regards,
Tom Ogilvy

"Steph" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

Thank you!

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> That would be the basic approach.
>
> --
> Regards,
> Tom Ogilvy
>
> "Steph" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
>
>

What if the user selects Save As? Does that throw a monkey wrench in the
code?

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> That would be the basic approach.
>
> --
> Regards,
> Tom Ogilvy
>
> "Steph" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
>
>

Yes. Because you are doing a SAVE

You can tell if the user is going to be offered a file name selection dialog
by checking


Dim fName as Variant
On Error goto ErrHandler

Cancel = True
if SaveAsUI then
fName = Applicaton.GetSaveAsFileName()
if fName = "False" then
exit sub
else
Application.EnableEvents = False
thisworkbook.SaveAs fName
Application.EnableEvents = True
end if
else
Application.EnableEvents = False
thisworkbook.Save
Application.EnableEvents = True
end if

ErrHandler:
Application.EnableEvents = True
end sub


--
Regards,
Tom Ogilvy


"Steph" <[email protected]> wrote in message
news:[email protected]...
> What if the user selects Save As? Does that throw a monkey wrench in the
> code?
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > That would be the basic approach.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Steph" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

Perfect. Thanks so much Tom!!

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Yes. Because you are doing a SAVE
>
> You can tell if the user is going to be offered a file name selection
dialog
> by checking
>
>
> Dim fName as Variant
> On Error goto ErrHandler
>
> Cancel = True
> if SaveAsUI then
> fName = Applicaton.GetSaveAsFileName()
> if fName = "False" then
> exit sub
> else
> Application.EnableEvents = False
> thisworkbook.SaveAs fName
> Application.EnableEvents = True
> end if
> else
> Application.EnableEvents = False
> thisworkbook.Save
> Application.EnableEvents = True
> end if
>
> ErrHandler:
> Application.EnableEvents = True
> end sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
> > What if the user selects Save As? Does that throw a monkey wrench in
the
> > code?
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:%[email protected]...
> > > That would be the basic approach.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Steph" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Tom,
I added a few msgbox statements and tested your code. The Save works great.
But when I select SaveAs, nothing happens. I don't get the prompt to enter
the file name, and the file is not saved at all. Any ideas? Here's what I
have:

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 = Applicaton.GetSaveAsFilename()
If fName = "False" Then
Exit Sub
Else
'<Optional - this would be before save code>
MsgBox ("Before save")
Application.EnableEvents = False
ThisWorkbook.SaveAs fName
Application.EnableEvents = True
'<Optional - this would be after save code>
MsgBox ("After save")
End If
Else
'<Optional - this would be before save code>
MsgBox ("Before save")
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
'<Optional - this would be after save code>
MsgBox ("After save")
End If

ErrHandler:
Application.EnableEvents = True

End Sub

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Yes. Because you are doing a SAVE
>
> You can tell if the user is going to be offered a file name selection
dialog
> by checking
>
>
> Dim fName as Variant
> On Error goto ErrHandler
>
> Cancel = True
> if SaveAsUI then
> fName = Applicaton.GetSaveAsFileName()
> if fName = "False" then
> exit sub
> else
> Application.EnableEvents = False
> thisworkbook.SaveAs fName
> Application.EnableEvents = True
> end if
> else
> Application.EnableEvents = False
> thisworkbook.Save
> Application.EnableEvents = True
> end if
>
> ErrHandler:
> Application.EnableEvents = True
> end sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
> > What if the user selects Save As? Does that throw a monkey wrench in
the
> > code?
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:%[email protected]...
> > > That would be the basic approach.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Steph" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Well one of use can't spell application < sheepish grin> Guess it was me

fName = Applicaton.GetSaveAsFilename()

should be
fName = Application.GetSaveAsFilename()

Since there is an error handler in effect, it hides the error. Until you
get your code working you should comment out the

On error goto ErrHandler

line.

--
Regards,
Tom Ogilvy


"Steph" <[email protected]> wrote in message
news:[email protected]...
> Tom,
> I added a few msgbox statements and tested your code. The Save works
great.
> But when I select SaveAs, nothing happens. I don't get the prompt to
enter
> the file name, and the file is not saved at all. Any ideas? Here's what
I
> have:
>
> 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 = Applicaton.GetSaveAsFilename()
> If fName = "False" Then
> Exit Sub
> Else
> '<Optional - this would be before save code>
> MsgBox ("Before save")
> Application.EnableEvents = False
> ThisWorkbook.SaveAs fName
> Application.EnableEvents = True
> '<Optional - this would be after save code>
> MsgBox ("After save")
> End If
> Else
> '<Optional - this would be before save code>
> MsgBox ("Before save")
> Application.EnableEvents = False
> ThisWorkbook.Save
> Application.EnableEvents = True
> '<Optional - this would be after save code>
> MsgBox ("After save")
> End If
>
> ErrHandler:
> Application.EnableEvents = True
>
> End Sub
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Yes. Because you are doing a SAVE
> >
> > You can tell if the user is going to be offered a file name selection
> dialog
> > by checking
> >
> >
> > Dim fName as Variant
> > On Error goto ErrHandler
> >
> > Cancel = True
> > if SaveAsUI then
> > fName = Applicaton.GetSaveAsFileName()
> > if fName = "False" then
> > exit sub
> > else
> > Application.EnableEvents = False
> > thisworkbook.SaveAs fName
> > Application.EnableEvents = True
> > end if
> > else
> > Application.EnableEvents = False
> > thisworkbook.Save
> > Application.EnableEvents = True
> > end if
> >
> > ErrHandler:
> > Application.EnableEvents = True
> > end sub
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Steph" <[email protected]> wrote in message
> > news:[email protected]...
> > > What if the user selects Save As? Does that throw a monkey wrench in
> the
> > > code?
> > >
> > > "Tom Ogilvy" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > That would be the basic approach.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Steph" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Thanks Tom! Never even thought to look becasue of the error handler. Good
advice to add at the very end! Otherwise I'd be staring at hthe code
forever!!

Can I ask a follow-up?
As I have things set up now, the 'before save' code and 'after save' code
execute when the user hits Save
or 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!

"Tom Ogilvy" <[email protected]> wrote in message
news:etuj%[email protected]...
> Well one of use can't spell application < sheepish grin> Guess it was me
>
> fName = Applicaton.GetSaveAsFilename()
>
> should be
> fName = Application.GetSaveAsFilename()
>
> Since there is an error handler in effect, it hides the error. Until you
> get your code working you should comment out the
>
> On error goto ErrHandler
>
> line.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
> > Tom,
> > I added a few msgbox statements and tested your code. The Save works
> great.
> > But when I select SaveAs, nothing happens. I don't get the prompt to
> enter
> > the file name, and the file is not saved at all. Any ideas? Here's
what
> I
> > have:
> >
> > 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 = Applicaton.GetSaveAsFilename()
> > If fName = "False" Then
> > Exit Sub
> > Else
> > '<Optional - this would be before save code>
> > MsgBox ("Before save")
> > Application.EnableEvents = False
> > ThisWorkbook.SaveAs fName
> > Application.EnableEvents = True
> > '<Optional - this would be after save code>
> > MsgBox ("After save")
> > End If
> > Else
> > '<Optional - this would be before save code>
> > MsgBox ("Before save")
> > Application.EnableEvents = False
> > ThisWorkbook.Save
> > Application.EnableEvents = True
> > '<Optional - this would be after save code>
> > MsgBox ("After save")
> > End If
> >
> > ErrHandler:
> > Application.EnableEvents = True
> >
> > End Sub
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > Yes. Because you are doing a SAVE
> > >
> > > You can tell if the user is going to be offered a file name selection
> > dialog
> > > by checking
> > >
> > >
> > > Dim fName as Variant
> > > On Error goto ErrHandler
> > >
> > > Cancel = True
> > > if SaveAsUI then
> > > fName = Applicaton.GetSaveAsFileName()
> > > if fName = "False" then
> > > exit sub
> > > else
> > > Application.EnableEvents = False
> > > thisworkbook.SaveAs fName
> > > Application.EnableEvents = True
> > > end if
> > > else
> > > Application.EnableEvents = False
> > > thisworkbook.Save
> > > Application.EnableEvents = True
> > > end if
> > >
> > > ErrHandler:
> > > Application.EnableEvents = True
> > > end sub
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Steph" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > What if the user selects Save As? Does that throw a monkey wrench
in
> > the
> > > > code?
> > > >
> > > > "Tom Ogilvy" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > That would be the basic approach.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > > "Steph" <[email protected]> wrote in message
> > > > > news:%[email protected]...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

In the beforeclose event put in

ThisWorkbook.Saved = True

and the user shouldn't be prompted.

--
Regards,
Tom Ogilvy

"Steph" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom! Never even thought to look becasue of the error handler.
Good
> advice to add at the very end! Otherwise I'd be staring at hthe code
> forever!!
>
> Can I ask a follow-up?
> As I have things set up now, the 'before save' code and 'after save' code
> execute when the user hits Save
> or 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!
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:etuj%[email protected]...
> > Well one of use can't spell application < sheepish grin> Guess it was
me
> >
> > fName = Applicaton.GetSaveAsFilename()
> >
> > should be
> > fName = Application.GetSaveAsFilename()
> >
> > Since there is an error handler in effect, it hides the error. Until
you
> > get your code working you should comment out the
> >
> > On error goto ErrHandler
> >
> > line.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Steph" <[email protected]> wrote in message
> > news:[email protected]...
> > > Tom,
> > > I added a few msgbox statements and tested your code. The Save works
> > great.
> > > But when I select SaveAs, nothing happens. I don't get the prompt to
> > enter
> > > the file name, and the file is not saved at all. Any ideas? Here's
> what
> > I
> > > have:
> > >
> > > 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 = Applicaton.GetSaveAsFilename()
> > > If fName = "False" Then
> > > Exit Sub
> > > Else
> > > '<Optional - this would be before save code>
> > > MsgBox ("Before save")
> > > Application.EnableEvents = False
> > > ThisWorkbook.SaveAs fName
> > > Application.EnableEvents = True
> > > '<Optional - this would be after save code>
> > > MsgBox ("After save")
> > > End If
> > > Else
> > > '<Optional - this would be before save code>
> > > MsgBox ("Before save")
> > > Application.EnableEvents = False
> > > ThisWorkbook.Save
> > > Application.EnableEvents = True
> > > '<Optional - this would be after save code>
> > > MsgBox ("After save")
> > > End If
> > >
> > > ErrHandler:
> > > Application.EnableEvents = True
> > >
> > > End Sub
> > >
> > > "Tom Ogilvy" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Yes. Because you are doing a SAVE
> > > >
> > > > You can tell if the user is going to be offered a file name
selection
> > > dialog
> > > > by checking
> > > >
> > > >
> > > > Dim fName as Variant
> > > > On Error goto ErrHandler
> > > >
> > > > Cancel = True
> > > > if SaveAsUI then
> > > > fName = Applicaton.GetSaveAsFileName()
> > > > if fName = "False" then
> > > > exit sub
> > > > else
> > > > Application.EnableEvents = False
> > > > thisworkbook.SaveAs fName
> > > > Application.EnableEvents = True
> > > > end if
> > > > else
> > > > Application.EnableEvents = False
> > > > thisworkbook.Save
> > > > Application.EnableEvents = True
> > > > end if
> > > >
> > > > ErrHandler:
> > > > Application.EnableEvents = True
> > > > end sub
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Steph" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > What if the user selects Save As? Does that throw a monkey wrench
> in
> > > the
> > > > > code?
> > > > >
> > > > > "Tom Ogilvy" <[email protected]> wrote in message
> > > > > news:%[email protected]...
> > > > > > That would be the basic approach.
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > > "Steph" <[email protected]> wrote in message
> > > > > > news:%[email protected]...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

But that assumes the user saved the file before closing. So the user could
make changes, close, and all changes are discarded. I still want them to be
prompted if changes were made, but don't want the "after save" piece of code
to fire.

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> In the beforeclose event put in
>
> ThisWorkbook.Saved = True
>
> and the user shouldn't be prompted.
>
> --
> Regards,
> Tom Ogilvy
>
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Tom! Never even thought to look becasue of the error handler.
> Good
> > advice to add at the very end! Otherwise I'd be staring at hthe code
> > forever!!
> >
> > Can I ask a follow-up?
> > As I have things set up now, the 'before save' code and 'after save'
code
> > execute when the user hits Save
> > or 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!
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:etuj%[email protected]...
> > > Well one of use can't spell application < sheepish grin> Guess it was
> me
> > >
> > > fName = Applicaton.GetSaveAsFilename()
> > >
> > > should be
> > > fName = Application.GetSaveAsFilename()
> > >
> > > Since there is an error handler in effect, it hides the error. Until
> you
> > > get your code working you should comment out the
> > >
> > > On error goto ErrHandler
> > >
> > > line.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Steph" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Tom,
> > > > I added a few msgbox statements and tested your code. The Save
works
> > > great.
> > > > But when I select SaveAs, nothing happens. I don't get the prompt
to
> > > enter
> > > > the file name, and the file is not saved at all. Any ideas? Here's
> > what
> > > I
> > > > have:
> > > >
> > > > 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 = Applicaton.GetSaveAsFilename()
> > > > If fName = "False" Then
> > > > Exit Sub
> > > > Else
> > > > '<Optional - this would be before save code>
> > > > MsgBox ("Before save")
> > > > Application.EnableEvents = False
> > > > ThisWorkbook.SaveAs fName
> > > > Application.EnableEvents = True
> > > > '<Optional - this would be after save code>
> > > > MsgBox ("After save")
> > > > End If
> > > > Else
> > > > '<Optional - this would be before save code>
> > > > MsgBox ("Before save")
> > > > Application.EnableEvents = False
> > > > ThisWorkbook.Save
> > > > Application.EnableEvents = True
> > > > '<Optional - this would be after save code>
> > > > MsgBox ("After save")
> > > > End If
> > > >
> > > > ErrHandler:
> > > > Application.EnableEvents = True
> > > >
> > > > End Sub
> > > >
> > > > "Tom Ogilvy" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Yes. Because you are doing a SAVE
> > > > >
> > > > > You can tell if the user is going to be offered a file name
> selection
> > > > dialog
> > > > > by checking
> > > > >
> > > > >
> > > > > Dim fName as Variant
> > > > > On Error goto ErrHandler
> > > > >
> > > > > Cancel = True
> > > > > if SaveAsUI then
> > > > > fName = Applicaton.GetSaveAsFileName()
> > > > > if fName = "False" then
> > > > > exit sub
> > > > > else
> > > > > Application.EnableEvents = False
> > > > > thisworkbook.SaveAs fName
> > > > > Application.EnableEvents = True
> > > > > end if
> > > > > else
> > > > > Application.EnableEvents = False
> > > > > thisworkbook.Save
> > > > > Application.EnableEvents = True
> > > > > end if
> > > > >
> > > > > ErrHandler:
> > > > > Application.EnableEvents = True
> > > > > end sub
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "Steph" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > What if the user selects Save As? Does that throw a monkey
wrench
> > in
> > > > the
> > > > > > code?
> > > > > >
> > > > > > "Tom Ogilvy" <[email protected]> wrote in message
> > > > > > news:%[email protected]...
> > > > > > > That would be the basic approach.
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Tom Ogilvy
> > > > > > >
> > > > > > > "Steph" <[email protected]> wrote in message
> > > > > > > news:%[email protected]...
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

I think you need to program the before close (this fires before the before
save). Check the ThisWorkbook.Saved property - this should tell you if the
user will get the save prompt. If ThisWorkbook.Saved is True, then do
nothing.

If ThisWorkbook.Saved is False,
then you can put up a message box and ask if the user wants to save. If
so, set EnableEvents to False and save the workbook. Turn events back on
and let it continue. It the user says no, don't save, then set
ThisWorkbook.Saved = True and let it continue.



--
Regards,
Tom Ogilvy

"Steph" <[email protected]> wrote in message
news:[email protected]...
> But that assumes the user saved the file before closing. So the user
could
> make changes, close, and all changes are discarded. I still want them to
be
> prompted if changes were made, but don't want the "after save" piece of
code
> to fire.
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > In the beforeclose event put in
> >
> > ThisWorkbook.Saved = True
> >
> > and the user shouldn't be prompted.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Steph" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Tom! Never even thought to look becasue of the error handler.
> > Good
> > > advice to add at the very end! Otherwise I'd be staring at hthe code
> > > forever!!
> > >
> > > Can I ask a follow-up?
> > > As I have things set up now, the 'before save' code and 'after save'
> code
> > > execute when the user hits Save
> > > or 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!
> > >
> > > "Tom Ogilvy" <[email protected]> wrote in message
> > > news:etuj%[email protected]...
> > > > Well one of use can't spell application < sheepish grin> Guess it
was
> > me
> > > >
> > > > fName = Applicaton.GetSaveAsFilename()
> > > >
> > > > should be
> > > > fName = Application.GetSaveAsFilename()
> > > >
> > > > Since there is an error handler in effect, it hides the error.
Until
> > you
> > > > get your code working you should comment out the
> > > >
> > > > On error goto ErrHandler
> > > >
> > > > line.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Steph" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Tom,
> > > > > I added a few msgbox statements and tested your code. The Save
> works
> > > > great.
> > > > > But when I select SaveAs, nothing happens. I don't get the prompt
> to
> > > > enter
> > > > > the file name, and the file is not saved at all. Any ideas?
Here's
> > > what
> > > > I
> > > > > have:
> > > > >
> > > > > 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 = Applicaton.GetSaveAsFilename()
> > > > > If fName = "False" Then
> > > > > Exit Sub
> > > > > Else
> > > > > '<Optional - this would be before save code>
> > > > > MsgBox ("Before save")
> > > > > Application.EnableEvents = False
> > > > > ThisWorkbook.SaveAs fName
> > > > > Application.EnableEvents = True
> > > > > '<Optional - this would be after save code>
> > > > > MsgBox ("After save")
> > > > > End If
> > > > > Else
> > > > > '<Optional - this would be before save code>
> > > > > MsgBox ("Before save")
> > > > > Application.EnableEvents = False
> > > > > ThisWorkbook.Save
> > > > > Application.EnableEvents = True
> > > > > '<Optional - this would be after save code>
> > > > > MsgBox ("After save")
> > > > > End If
> > > > >
> > > > > ErrHandler:
> > > > > Application.EnableEvents = True
> > > > >
> > > > > End Sub
> > > > >
> > > > > "Tom Ogilvy" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Yes. Because you are doing a SAVE
> > > > > >
> > > > > > You can tell if the user is going to be offered a file name
> > selection
> > > > > dialog
> > > > > > by checking
> > > > > >
> > > > > >
> > > > > > Dim fName as Variant
> > > > > > On Error goto ErrHandler
> > > > > >
> > > > > > Cancel = True
> > > > > > if SaveAsUI then
> > > > > > fName = Applicaton.GetSaveAsFileName()
> > > > > > if fName = "False" then
> > > > > > exit sub
> > > > > > else
> > > > > > Application.EnableEvents = False
> > > > > > thisworkbook.SaveAs fName
> > > > > > Application.EnableEvents = True
> > > > > > end if
> > > > > > else
> > > > > > Application.EnableEvents = False
> > > > > > thisworkbook.Save
> > > > > > Application.EnableEvents = True
> > > > > > end if
> > > > > >
> > > > > > ErrHandler:
> > > > > > Application.EnableEvents = True
> > > > > > end sub
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > > "Steph" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > What if the user selects Save As? Does that throw a monkey
> wrench
> > > in
> > > > > the
> > > > > > > code?
> > > > > > >
> > > > > > > "Tom Ogilvy" <[email protected]> wrote in message
> > > > > > > news:%[email protected]...
> > > > > > > > That would be the basic approach.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Regards,
> > > > > > > > Tom Ogilvy
> > > > > > > >
> > > > > > > > "Steph" <[email protected]> wrote in message
> > > > > > > > news:%[email protected]...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>