Forcing a Save As

[Shrout1]

How does one prompt a user to save the file as something? I'm trying to create a locked template and therefore it would be advantageous to ask the user to save the file.

Thanks!

[Zack Barresse]

Hi,

If you create a template, it will automatically save it as an xls file (whereas templates are xlt files). Are you wanting to change the name of the file everytime? Or just the once? (This can also be done with a normal xls file.)

[Shrout1]

Trouble with template files (xlt's) is that they're only templates if they're installed into the templates folder - the users I'm going to be distributing this workbook to may not have the ability to place the file there and I doubt that they'll have the know-how either...

I've played with xlt vs. xls but there doesn't seem to be a significant difference unless you place the file in the right place. The two are also completely interchangeable - I've changed the extension and it doesn't seem to affect the operation of the file...

So, I guess I'm just curious how you can bring up a 'Save As' prompt and then kill the routin if the user decides to cancel

[CBrine]

Place this code at the This Workbook level of the vba editor and save your workbook. Anytime someone closes the workbook, you will be prompted with a save as dialog.

PS- You might want to look more into Zack's suggestion of Templates. I'm no expert with them, but once they are setup, they are available when you create a new workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim test As String
test = Application.Dialogs(xlDialogSaveAs).Show
If test <> False Then
'The code that saves you workbook is already programmed into the dialog,
'so you will only need to code in anything extra you want to do.
'Your Code
End If
End Sub

[mdmackillop]

Hi,
Here's a basic routine. It needs a bit of error handling etc. Paste it in the ThisWorkbook module of your template.
MD

Option Compare Text
Option Explicit 
Private Sub Workbook_Open()
    Dim FName
    If Right(ActiveWorkbook.Name, 3) = "xlt" Then
        FName = InputBox("Enter path and save name", "Save File")
        ActiveWorkbook.SaveAs FName & ".xls"
    End If
End Sub

[Shrout1]

All right! Thanks a lot all! You guys sure are a great bunch here - I think I've been able to use every bit of code that everyone's suggested in some capacity or another! Thanks!

(P.S. Problem solved! )

[mdmackillop]

Hi Shrout.
If you go to Thread Tools above your first enty, you can mark this as solved.
MD

[Shrout1]

Hi Shrout.
If you go to Thread Tools above your first enty, you can mark this as solved.
MD

Thanks! I hadn't seen that option the first time :-P