Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Save Question | Excel Worksheet Functions | |||
save as text question | Excel Discussion (Misc queries) | |||
Another save as question | Excel Programming | |||
Save Macro - yet another question | Excel Discussion (Misc queries) | |||
A save question: | Excel Programming |