Default file type for saving excel templates

[doublej41]

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.

 

[Richard Schollar]

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):

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

 

[doublej41]

Number 2 was spot on.

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