| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with BeforeSave event
Hi,
I have code in the BeforeSave to 'suggest' a filename to save as but the user needs to be able to change the 'suggestion' before saving. I'm using Application.GetSaveAsFilename to suggest the filename. When ThisWorkbook.SaveAs SaveAsFileName is executed, it fires the BeforeSave event again. Is there anyway a better way to do what I'm attempting here? If SaveAsUI = True Then ' displays SaveAs dialog box with default filename. Returns SaveAsFileName SaveAsFileName = Application.GetSaveAsFilename(PMFileName, "Microsoft Office Excel Workbook (*.xls), *.xls") ' if user clicked Cancel then SaveAsFileName = False If SaveAsFileName = "False" Then Cancel = True Exit Sub Else ThisWorkbook.SaveAs SaveAsFileName Cancel = True End If End If -- Thanks in advance! Chuck M. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help with BeforeSave event
Temporarily disable events with "Application.EnableEvents=False". Then reset
to True as follows: If SaveAsFileName = "False" Then Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) Exit Sub Else Application.EnableEvents = False ThisWorkbook.SaveAs SaveAsFileName Application.EnableEvents = True Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) End If End If -- Jay "Chuck M" wrote: Hi, I have code in the BeforeSave to 'suggest' a filename to save as but the user needs to be able to change the 'suggestion' before saving. I'm using Application.GetSaveAsFilename to suggest the filename. When ThisWorkbook.SaveAs SaveAsFileName is executed, it fires the BeforeSave event again. Is there anyway a better way to do what I'm attempting here? If SaveAsUI = True Then ' displays SaveAs dialog box with default filename. Returns SaveAsFileName SaveAsFileName = Application.GetSaveAsFilename(PMFileName, "Microsoft Office Excel Workbook (*.xls), *.xls") ' if user clicked Cancel then SaveAsFileName = False If SaveAsFileName = "False" Then Cancel = True Exit Sub Else ThisWorkbook.SaveAs SaveAsFileName Cancel = True End If End If -- Thanks in advance! Chuck M. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help with BeforeSave event
Cancel = true
means to not perform the action that triggered the BeforeSave Event, so it is certainly necessary. -- Regards, Tom Ogilvy "Jay" wrote in message ... Temporarily disable events with "Application.EnableEvents=False". Then reset to True as follows: If SaveAsFileName = "False" Then Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) Exit Sub Else Application.EnableEvents = False ThisWorkbook.SaveAs SaveAsFileName Application.EnableEvents = True Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) End If End If -- Jay "Chuck M" wrote: Hi, I have code in the BeforeSave to 'suggest' a filename to save as but the user needs to be able to change the 'suggestion' before saving. I'm using Application.GetSaveAsFilename to suggest the filename. When ThisWorkbook.SaveAs SaveAsFileName is executed, it fires the BeforeSave event again. Is there anyway a better way to do what I'm attempting here? If SaveAsUI = True Then ' displays SaveAs dialog box with default filename. Returns SaveAsFileName SaveAsFileName = Application.GetSaveAsFilename(PMFileName, "Microsoft Office Excel Workbook (*.xls), *.xls") ' if user clicked Cancel then SaveAsFileName = False If SaveAsFileName = "False" Then Cancel = True Exit Sub Else ThisWorkbook.SaveAs SaveAsFileName Cancel = True End If End If -- Thanks in advance! Chuck M. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help with BeforeSave event
Agreed; don't delete Cancel=True. Thanks Tom.
-- Jay "Tom Ogilvy" wrote: Cancel = true means to not perform the action that triggered the BeforeSave Event, so it is certainly necessary. -- Regards, Tom Ogilvy "Jay" wrote in message ... Temporarily disable events with "Application.EnableEvents=False". Then reset to True as follows: If SaveAsFileName = "False" Then Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) Exit Sub Else Application.EnableEvents = False ThisWorkbook.SaveAs SaveAsFileName Application.EnableEvents = True Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) End If End If -- Jay "Chuck M" wrote: Hi, I have code in the BeforeSave to 'suggest' a filename to save as but the user needs to be able to change the 'suggestion' before saving. I'm using Application.GetSaveAsFilename to suggest the filename. When ThisWorkbook.SaveAs SaveAsFileName is executed, it fires the BeforeSave event again. Is there anyway a better way to do what I'm attempting here? If SaveAsUI = True Then ' displays SaveAs dialog box with default filename. Returns SaveAsFileName SaveAsFileName = Application.GetSaveAsFilename(PMFileName, "Microsoft Office Excel Workbook (*.xls), *.xls") ' if user clicked Cancel then SaveAsFileName = False If SaveAsFileName = "False" Then Cancel = True Exit Sub Else ThisWorkbook.SaveAs SaveAsFileName Cancel = True End If End If -- Thanks in advance! Chuck M. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help with BeforeSave event
Thanks Jay and Tom. This works perfectly!
-- Thanks. Chuck M. "Jay" wrote: Agreed; don't delete Cancel=True. Thanks Tom. -- Jay "Tom Ogilvy" wrote: Cancel = true means to not perform the action that triggered the BeforeSave Event, so it is certainly necessary. -- Regards, Tom Ogilvy "Jay" wrote in message ... Temporarily disable events with "Application.EnableEvents=False". Then reset to True as follows: If SaveAsFileName = "False" Then Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) Exit Sub Else Application.EnableEvents = False ThisWorkbook.SaveAs SaveAsFileName Application.EnableEvents = True Cancel = True '<<<===I think this is unnecessary; suggest delete (but test) End If End If -- Jay "Chuck M" wrote: Hi, I have code in the BeforeSave to 'suggest' a filename to save as but the user needs to be able to change the 'suggestion' before saving. I'm using Application.GetSaveAsFilename to suggest the filename. When ThisWorkbook.SaveAs SaveAsFileName is executed, it fires the BeforeSave event again. Is there anyway a better way to do what I'm attempting here? If SaveAsUI = True Then ' displays SaveAs dialog box with default filename. Returns SaveAsFileName SaveAsFileName = Application.GetSaveAsFilename(PMFileName, "Microsoft Office Excel Workbook (*.xls), *.xls") ' if user clicked Cancel then SaveAsFileName = False If SaveAsFileName = "False" Then Cancel = True Exit Sub Else ThisWorkbook.SaveAs SaveAsFileName Cancel = True End If End If -- Thanks in advance! Chuck M. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to run macro on the BeforeSave event | Excel Programming | |||
| BeforeSave Event Question | Excel Programming | |||
| BeforeSave event | Excel Discussion (Misc queries) | |||
| BeforeSave event | Excel Programming | |||
| BeforeSave workbook event | Excel Programming | |||
Linear Mode
