Hi!

I have a spreadsheet (form) template that our staff types info into.
One field is the "Date of Issue" and another the "customer id" (among many
other fields).

This forum taught me how to have excel save my generic template with a
unique name "customer id"+today's date ex. 123_20060603.

This has worked really well, because, sometimes the same customer will have
another form. So, 123_20061231 works great.

Now, I would like to know if there is a way to prompt or automate an option:
Is this an existing form that you are appending to?
Yes, means I save the file with the existing name 123_20060603
No, means I save with a new file name 123_todays date.

I'm not certain I could be this smart, but, there is a way to tell. When you
first open this worksheet, if it was opened as a template ".xlt", then run
the macro and use the new data in J2. If opened as ".xls" overwrite the
existing file (maybe with an "Are you sure?" prompt) and do not execute the
rest of the macro.

I still very new at the macro, vba thing, so if you have any truly great
ideas, please be very specific.

Here is the existing code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
ThisWorkbook.SaveAs Filename:=Worksheets("Sheet1").Range("J2").Value, _
FileFormat:=xlWorkbookNormal
Application.EnableEvents = True
End Sub

Thank you SOoooo much!

Just look at the existing file name:

activeworkbook.name

Put an if statement in your beforesave function. If its xlt, then make
the new file. If its .xls, then just do a save. Hope this helps.

If Right(ActiveWorkbook.Name, 3)="xls" Then
**do one thing
ElseIf Right(ActiveWorkbook.Name, 3) = "xlt" Then
**Do something else
End If


DTTODGG wrote:
Hi!

I have a spreadsheet (form) template that our staff types info into.
One field is the "Date of Issue" and another the "customer id" (among many
other fields).

This forum taught me how to have excel save my generic template with a
unique name "customer id"+today's date ex. 123_20060603.

This has worked really well, because, sometimes the same customer will have
another form. So, 123_20061231 works great.

Now, I would like to know if there is a way to prompt or automate an option:
Is this an existing form that you are appending to?
Yes, means I save the file with the existing name 123_20060603
No, means I save with a new file name 123_todays date.

I'm not certain I could be this smart, but, there is a way to tell. When you
first open this worksheet, if it was opened as a template ".xlt", then run
the macro and use the new data in J2. If opened as ".xls" overwrite the
existing file (maybe with an "Are you sure?" prompt) and do not execute the
rest of the macro.

I still very new at the macro, vba thing, so if you have any truly great
ideas, please be very specific.

Here is the existing code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
ThisWorkbook.SaveAs Filename:=Worksheets("Sheet1").Range("J2").Value, _
FileFormat:=xlWorkbookNormal
Application.EnableEvents = True
End Sub

Thank you SOoooo much!