PDA

View Full Version : Save As File Type 'Options'



LarryLaser
10-01-2008, 01:16 PM
Hey folks,

A minor Question, mainly for Ken Puls's work.

In his KB article "Force users to enable macros in a workbook",
In creating a template workbook, when you go to save as a "Template" you do not get the option in the "File Type" drop down box, only the .xls option.

the Original code
'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

How do you change it??

CreganTur
10-01-2008, 01:51 PM
What do you want to chage it to?

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: :doh: I'm a moron- just saw that you want to use this with a templtate.

Have you tried:
'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xlt), *.xlt")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

LarryLaser
10-02-2008, 09:04 AM
Hey "CreganTur"
I understand your comment, the issue that I am dealing with is I want to save the changes when I am working on the Template. When I am finished with the template, then it will be used as a workbook.
I know that I can change the

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xlt), *.xlt")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If to

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If
or back again, each time I Mod the Template.

The question is, Is there a way to mod the VBA so I don't have to keep making the changes? Is there a way to write the code so the Save As options are available?


What do you want to chage it to?

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: :doh: I'm a moron- just saw that you want to use this with a templtate.

Have you tried:
'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xlt), *.xlt")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

GTO
10-02-2008, 03:51 PM
It sounds as though, while you (as the developer) are making mods to the template or underlying code itself - you want to be able to SaveAs to a new template name?

If this is the case, a short function (just so it doesn't show up in the macros dialog box for the users):

Function ToggleEnable()
Application.EnableEvents = Not Application.EnableEvents
MsgBox "EnableEvents is currently set to: " & Application.EnableEvents
End Function

WHen you run the function 'manually' (F5), it will disable events (or re-enable) and w/events disabled, the stuff in BeforeSave will not occur. Thus, you'll be able to do a 'regular' SaveAs, like a file w/no coding...

Though I typed the function in the msg window, it "should be" error free...

Also - you may wish to review the following thread, as by chance (and presuming I'm correct on which of Mr. Puhls' articles you are referring to), it covers possible issues in BeforeClose etc...
http://vbaexpress.com/forum/showthread.php?t=22414

Hopefully this helps,

Mark

PS. Jumpin' jimminies! I almost forgot to mention: re-run the function AFTER your SaveAs - so that events are again occurring as normal; then do a regular save so that sheets are hidden...