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!
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!
Last edited by Shrout1; 09-30-2004 at 11:42 AM. Reason: Problem resolved!
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.)
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
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
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
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! )
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 :-POriginally Posted by mdmackillop