| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
Thank you!
"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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
Perfect. Thanks so much Tom!!
"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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
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" wrote in message ... 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" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
In the beforeclose event put in
ThisWorkbook.Saved = True and the user shouldn't be prompted. -- Regards, Tom Ogilvy "Steph" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
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" wrote in message ... In the beforeclose event put in ThisWorkbook.Saved = True and the user shouldn't be prompted. -- Regards, Tom Ogilvy "Steph" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
After Save Event - Confirm
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" wrote in message ... 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" wrote in message ... In the beforeclose event put in ThisWorkbook.Saved = True and the user shouldn't be prompted. -- Regards, Tom Ogilvy "Steph" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Example After Save Event | Excel Programming | |||
| After Save Event help | Excel Programming | |||
| Before Save as Event needed | Excel Programming | |||
| save as event | Excel Programming | |||
| After save event | Excel Programming | |||
![Steph[_6_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
