How to stop a Macro-Enabled Template from saving and adding a "1" to the file name

[AwesomeSteph]

Is there a setting or a macro that could disable the auto function of a macro-enabled template from adding a number?

I have a workbook that is used for every bit of work done in house, and is copied many times. I chose to use a template to discourage anyone from making edits to the original that everyone uses. I have a problem though that when a user goes to save the template as their workbook to use for their forms it adds a 1 to the end of the file name and since we are currently at revision 3 of the workbook it saves for the user as Rev31 (since the Rev3 has to be at the end of the file name per company policy).
Any bit of help on this is very much appreciated! I've been searching for months to try and fix this issue. I can't tell users to take off the 1 because people really like to tell me that they don't like doing extra work so I have been told to make it "user friendly"....

 

[AwesomeSteph]

Something like this:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sName As String
    Dim sSaveName As String
    Dim bSave As Boolean
    If SaveAsUI And Len(ThisWorkbook.Path) = 0 Then
        sName = ThisWorkbook.Name
        sName = Left(sName, Len(sName) - 1)
        sSaveName = Application.GetSaveAsFilename(sName, "Microsoft Excel files (*.xlsm), *.xlsm", , "Save changes")
        If Len(sSaveName) > 0 Then
            If Len(Dir(sSaveName)) > 0 Then
                bSave = (MsgBox("File '" & sSaveName & "' already exists, overwrite?", vbQuestion + vbYesNo, "File exists") = vbYes)
            Else
                bSave = True
            End If
            If bSave Then
                Application.EnableEvents = False
                ThisWorkbook.SaveAs sSaveName, xlOpenXMLWorkbookMacroEnabled
                Application.EnableEvents = True
            End If
            Cancel = True
        End If
    End If
End Sub

I put this code in the original macro enabled excel workbook within the "ThisWorkbook" object and saved the workbook as a template and once reopened it added the "1" again. Is that not the process that I should have used?

 

[Kenneth Hobson]

Put this into your ThisWorkbook object. I can not finish it until I know a path and filename. You can put jkp's code into the comment and try that if you like.

Private Sub Workbook_Open()
  If ThisWorkbook.Name = ThisWorkbook.FullName Then
    'ThisWorkbook.Save
  End If
End Sub

 

[jkpieterse]

Have you tried to save the file once opened from the template? I guess not.

 

[AwesomeSteph]

Have you tried to save the file once opened from the template? I guess not.

Yes that is when the "1" is added to the file name. The template doesn't have the "1" only what is produced from the template

 

[Kenneth Hobson]

Doing what I said in #12 :

Private Sub Workbook_Open()
  Dim sName As String
  Dim sSaveName As String
  Dim bSave As Boolean
    
  If ThisWorkbook.Name <> ThisWorkbook.FullName Then Exit Sub
  
  sName = ThisWorkbook.Name
  sName = Left(sName, Len(sName) - 1)
  sSaveName = Application.GetSaveAsFilename(sName, "Microsoft Excel files (*.xlsm), *.xlsm", , "Save changes")
  If Len(sSaveName) > 0 Then
    If Len(Dir(sSaveName)) > 0 Then
      bSave = (MsgBox("File '" & sSaveName & "' already exists, overwrite?", vbQuestion + vbYesNo, "File exists") = vbYes)
      Else
        bSave = True
    End If
    If bSave Then
      Application.EnableEvents = False
      ThisWorkbook.SaveAs sSaveName, xlOpenXMLWorkbookMacroEnabled
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[AwesomeSteph]

Have you tried to save the file once opened from the template? I guess not.

Additionally when I open the template with the Workbook_Open code I get a Compile error: Procedure declaration does not match description of event or procedure having the same name.

 

[jkpieterse]

Very strange. I've tested the code and when you open a file from the template it does show the 1, which gets removed if you try to save the copy.

 

[AwesomeSteph]

Very strange. I've tested the code and when you open a file from the template it does show the 1, which gets removed if you try to save the copy.

Got it! I wasn't following through with the save process since I am just testing it and it kept showing the 1 in the name of the file until I tried to actually save it and it dropped the number. thank you so much!!!