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



"mooresk257" wrote:

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" wrote:

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)?

--
Rick (MVP - Excel)



"mooresk257" wrote in message
...
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!

.