PDA

View Full Version : [SOLVED] Forcing a Save As



Shrout1
09-28-2004, 10:53 AM
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
09-28-2004, 11:37 AM
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
09-28-2004, 01:48 PM
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 :p

CBrine
09-28-2004, 01:57 PM
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
09-28-2004, 02:12 PM
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
09-30-2004, 06:45 AM
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! :D)

mdmackillop
09-30-2004, 09:37 AM
Hi Shrout.
If you go to Thread Tools above your first enty, you can mark this as solved.
MD

Shrout1
09-30-2004, 11:40 AM
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