New macro enable workbook generated from a macro enabled template

[aaldridge]

Hi
When I generate a new workbook from a macro enable template, I am not able to set the Save as type to: "Macro enable workbook" as default file type.

This macro does not wprk for the New workbook...

Sub SaveBook()
Dim sFile As String
sFile = Range("C7").Value & ".xlsm"
ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=52
End Sub
I want this macro to kick when the user is choosing File -> Save or File -> Save As

What am I missing her?

 

[JimSnyder]

If you want it to kick off from a user action, you need to capture an event. If you just want to limit the user to saving as a macro-enabled workbook, use a modified File Save dialog box with a mask for the file name. Is it this second option you are after?

 

[aaldridge]

Hi
When the users save the file (save As or Save), I want the "Save as Type" to default show Macro-enabled workbook.

 

[JimSnyder]

This code is expecting the file name for the file to open to be a cell, but the alternate is to pop a SaveAs dialogbox with a default directory. Give me a few minutes to track down the file mask (not used here).

Private Function RetrieveFilePath(ByVal cellRef As Range, _
    ByVal inFile As String) As String




    Dim pickedPath As String
    Dim truncPath As String
    Dim file1Dialog As FileDialog
    Dim hashLocat As Integer
    Dim file1Picked As Long
    Dim returnValue As Long




    If Not cellRef Is Nothing Then
        If FileExists(CStr(cellRef.Value) & "\" & inFile) Then
            RetrieveFilePath = CStr(cellRef.Value) & "\" & inFile
        Else    ' If we fail to pick it up from the spreadsheet, get it from the user
            Set file1Dialog = Application.FileDialog(msoFileDialogFilePicker)
            With file1Dialog
                .InitialFileName = "C:\"    ' Only guaranteed to be valid this far on any computer
                .title = "Select the source folder for Bluefolder report " & inFile
                .AllowMultiSelect = False
                file1Picked = file1Dialog.Show


                ' Check button chosen
                If file1Picked <> -1 Then   ' Cancel selected
                    returnValue = MsgBox(inFile & " path not selected. Program closing...", _
                        vbOKOnly, "Path not selected.")
                    RetrieveFilePath = vbNullString
                    Exit Function       ' Bypass final return assignment
                    
                Else    ' OK selected
                    pickedPath = .SelectedItems(1)
                    hashLocat = InStrRev(pickedPath, "\")
                    truncPath = Left(pickedPath, hashLocat - 1)
                    Range(cellRef.Address).Value = truncPath    ' Store path only
                End If
            End With


            RetrieveFilePath = pickedPath
            Set file1Dialog = Nothing
        End If
    End If
End Function    ' RetrieveFilePath

 

[JimSnyder]

Check here for details:
https://msdn.microsoft.com/en-us/library/office/ff196794.aspx

Example from that page:

      ' Clear out the current filters, and add our own. 
      .Filters.Clear 
      .Filters.Add "Access Databases", "*.MDB" 
      .Filters.Add "Access Projects", "*.ADP" 
      .Filters.Add "All Files", "*.*"