After Save Event - Confirm
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" wrote in message
...
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" wrote in message
...
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
|