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.

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.

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.

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.

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.