Save as crashes Excel - sometimes

[mooresk257]

I have this code in the ThisWorkbook object:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Fname As Variant

Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value
Application.Dialogs(xlDialogSaveAs).Show Fname

End Sub

Where F3 & F4 are text values, i.e. the cells are formatted as text. The
cells contain a serial number and machine name, and so the file name is
concatenated as "0000-Machine Name.xls"

Sometimes on the file save event Excel (v2003) crashes. Most of the time it
doesn't. The code works like it is supposed to - but is there something
missing, or some error trapping I can add to prevent the crash?

Thanks!

 

[Rick Rothstein]

While this should have no bearing on your problem, I would declare Fname as
String variable (not a Variant one) since you know you will be assigning
text to it. As for your problem... where in the process is the "crash"
taking place? Does the dialog box show up beforehand? If so, do you get to
press the OK button? Can you describe the "crash"... is an error message
generated (if so, what is it) or does Excel just cease working altogether
(and close down) or does something else happen (if so, what)?

 

[mooresk257]

Hi Rick,

I'll change the variable to String and see if that helps. Here's the sequence:

1. I click the "save" icon
2. File save dialog opens, with the filename shown correctly in the file
name text box
3. I click the "save" button and then I get the "excel encountered a problem
and hod to close. Would you like to recover your file" message.

I send an error report while the file reopens, and I find it has saved
correctly and everything is OK with the file - every time.

I've got to add some more stuff so that if F3 & F4 are "" then it skips the
rest of the code, but I wanted to sort this problem out first. Then again,
maybe that IS the problem!

Scott

 

[Rick Rothstein]

Okay, I think I see what is going on here. As soon as you hit the OK button
on the Save As dialog box, it re-executes the BeforeSave event code...
however, you still have the running BeforeSave code active from when you
clicked on Excel's Save icon in the first place. I don't think you want to
be executing your code in the BeforeSave event. Why not add a button, then
place your code in a macro and assign that macro to the button you added
(and totally remove the code you have in the BeforeSave event procedure)...
I believe this will eliminate the conflict you are generating.

 

[mooresk257]

OK - I just put this together and it's doing what I want so far, which is to
do a normal file save if F3 & 4 are empty, saving with the cell content of
one or the other cell that's not empty, andcencelling the save event on
"cancel" in the dialog box.

I'm sure there's got to be a way to clean the code up a bit though!

It remains to be seen if it crashes Excel - maybe "Fname as string" will
solve that problem.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Fname As String

If IsEmpty(Sheet1.Range("F3")) Then _
If IsEmpty(Sheet1.Range("F4")) Then Exit Sub

If Not IsEmpty(Sheet1.Range("F3")) Then _
If IsEmpty(Sheet1.Range("F4")) Then _
Fname = Sheet1.Range("F3").Value

If Not IsEmpty(Sheet1.Range("F4")) Then _
If IsEmpty(Sheet1.Range("F3")) Then _
Fname = Sheet1.Range("F4").Value

If Not IsEmpty(Sheet1.Range("F3")) Then _
If Not IsEmpty(Sheet1.Range("F4")) Then _
Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value

On Error GoTo DumpSub

Application.EnableEvents = False
Cancel = True
Application.Dialogs(xlDialogSaveAs).Show Fname

DumpSub:
Application.EnableEvents = True

End Sub

 

[Barb Reinhardt]

I've seen crashes on a workbook open event that was looking for named ranges
that didn't exist, IIRC. Rick, why wouldn't you put

Application.EnableEvents = False
Application.EnableEvents = True

Around the "secondary" save event so that it doesn't run the beforesave
event again?

Barb Reinhardt

 

[Rick Rothstein]

Rick, why wouldn't you put

Application.EnableEvents = False
Application.EnableEvents = True

Around the "secondary" save event so that it doesn't run the
beforesave event again?

Barb
================
Why? I'll tell you why.... because I'm an idiot, that's why.<g> I'm not sure
where my head was when I responded to mooresk257; but, for whatever reason,
it just didn't occur to me. Thanks for noting it.

mooresk257
================
Give Barb's suggestion a try... I think it should fix your problem.

 

[mooresk257]

Barb & Rick,

That's what I think I did with this code, and I hope nobody minds the re-post:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Fname As String

' If F3 & F4 are empty get out of this subroutine and save with original
workbook name

If IsEmpty(Sheet1.Range("F3")) Then _
If IsEmpty(Sheet1.Range("F4")) Then Exit Sub

' If F3 is not empty and F4 is, use F3 as file name

If Not IsEmpty(Sheet1.Range("F3")) Then _
If IsEmpty(Sheet1.Range("F4")) Then _
Fname = Sheet1.Range("F3").Value

' If F4 is not empty and F3 is, use F4 as file name

If Not IsEmpty(Sheet1.Range("F4")) Then _
If IsEmpty(Sheet1.Range("F3")) Then _
Fname = Sheet1.Range("F4").Value

' If F3 and F4 are not empty use both as file name separated by a dash

If Not IsEmpty(Sheet1.Range("F3")) Then _
If Not IsEmpty(Sheet1.Range("F4")) Then _
Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value

On Error GoTo DumpSub

Application.EnableEvents = False
Cancel = True
Application.Dialogs(xlDialogSaveAs).Show Fname

DumpSub:
Application.EnableEvents = True

End Sub

It seems to be working like it should, although as I said earlier I'm sure
it could be cleaned up a bit.

Thanks Barb & Rick!