Default file type for saving excel templates

doublej41

Board Regular
Joined
Mar 9, 2011
Messages
86
Hi All

I have recently created a macro-enabled template for use within my office. Users open this template from a macro button in a second Excel spreadsheet.

Is there a way I can set the default 'save as' file type of the template to be a macro-enabled workbook, rather than the user being prompted to select it? I realise I can change the settings on my own machine to do this, but is there a way I can do it within the template file so that the entire office wont have to alter their Excel settings?

As always, any help is greatly appreciated.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Yes you can - you need to use the FileDialog to activate the SaveAs functionality and then you can specify the FilterIndex property to be wherever the xlsm file type is in the list of available saveas options (might well be position 2, but I don't have Excel 2007/2010 available at the moment to check):

Code:
With Application.FileDialog(msoFileDialogSaveAs)
  .FilterIndex = 2  'check if this is correct!
  If .Show = -1 Then .Execute
End With
 

doublej41

Board Regular
Joined
Mar 9, 2011
Messages
86
Number 2 was spot on.

The code works a treat. Thanks very much, you have cured this mornings headache!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top