From what I see, you do not need the BeforeClose event Since you are not
saving the changes to your workbook, you could simply use the SaveAs and
create the new workbook. The old workbook is no longer open, but is still in
the disc file. You basically change the name of the open workbook this way.
Try it with a file and see if it does what you want. Just open a file, do a
saveas with a different name. You will see the new name on the window
caption and only one workbook open.
The old workbook will still appear in the folder.

You only need the BeforeSave event to do special tasks related to workbook
content, such as validating certain data fields, hiding rows, columns or
sheets, etc.
You don't need it for a SaveAs when the changes to the old book are not to
be saved.

"MeistersingerVonNurnberg" wrote:

Hey again -

My problem may be where I allow a user to exit the wb in question. Under
File-Close (the wb) or File-Exit (xl altogether) - no problem - the save as
works as expected. Its when I trigger the close using ThisWorkbook.Close from
one of the available custom TB functions. The event fires for sure, I see
that, and ultimately, the wb closes. It just won't save as the file.


Maybe it has something to do w my understanding of how the events are
working. Help me if u can.


here is a public sub in its own module:

Public Sub Close_FromMe()
ThisWorkbook.Close SaveChanges:=False
End Sub


and here is ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks.Add
ThisWorkbook.Saved = True
End
End Sub


I would have thought that the close in the public sub would have triggered
the BeforeClose wb event, either at the wb or app level, and I would get a
new wb, presumably "Book1" and wb would close. It does not do that. It just
closes the wb, and leaves XL open w no wb.

If I File-Close, or "X" out of the wb, no problem, there is a new "Book1"
wb. And no code running (I presume bc its closed).


thanks a bunch pal, and ttul


"JLGWhiz" wrote:

I have tried to duplicate the condition with different protection settings
and cannot get it to perform as you described. In fact, it does saveas a new
file name with the workbook protected, so that was not the cause. I hope you
will post back if you do find the culprit.

"MeistersingerVonNurnberg" wrote:

Hi -

I performed everything as you stated. The values are correct.

Maybe it has something to do w the wb being set as readonly from a Windows /
DOS perspective...

Or, perhaps ... In the BeforeClose, all the way at the end,
ThisWorkbook.Saved = True has something to do w it



"JLGWhiz" wrote:

Seems very odd. Try putting a break point on the line after your SaveAs
line. Then
use the tool tip display to check the values of the two variables for
workbook name after it has executed and before the macro ends. If both are
correct at that point, and it still does not save or change the window
caption at the top of the screen, then there is a real problem.

"MeistersingerVonNurnberg" wrote:


Hi All -

I've got a function call to save a workbook in the ThisWorkbook BeforeClose.
The function contains the following line:

Workbooks(sWbName).SaveAs Filename:=var_FileName

where, sWbName is a String, which is passed to the function as
ThisWorkbook.Name

and, var_FileName is a local Variant that is set as follows:
var_FileName = Application.GetSaveAsFilename(not showing args)


For whatever reason, the SaveAs line is not working. It raises no Err. Through
the debugger, sWbName is set as the currently opened workbook, and is correct.
Also through the debugger, var_FileName gets set properly by
Application.GetSaveAsFilename(), and contains the full path to the file.

Any ideas? Thanks.