VBA to specify new copies to be saved in .xlsm but keep .xltm as a selectable option?

Hi

I'm managing an excel template with lots of VBA in it, and I want to make sure that users only save a copy in the .xlsm format while I'm still able to update the template.  

Can I somehow code VBA to specify new copies to be saved in .xlsm but keep .xltm as a selectable option?
NackeyQualitative ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

You could click the Save as type box and select Excel Template (*.xltm) from drop down list;
Click the Tools > General Options.
enter a password into the Password to modify box and repeat pw

So you are the only one having the possibility to change the template

Regards
0
Roy CoxGroup Finance ManagerCommented:
To add to Rgonxo's suggestion.

You cannot directly edit an xltm file. You must open a copy as normal, edit the copy and save it as xltm  replacing the original xltm. read this for a fuller explanation

Excel Templates
0
NackeyQualitative ConsultantAuthor Commented:
Nha, doesn't really solve my problem. Workbooks and VBA code are already password protected, so i'm not really worried that anybody will mess it up.

I want to obtain two things:
1. That users (exept me) are forced to save in the .xlsm file format
2. I'm still able to modify the template

The code below  works smoothely in forcing the user to save in the .xlsm format, but it also prevents me from updating and save it as a template:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim txtFileName As String
 
   If SaveAsUI = True Then
        Cancel = True
 
       txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "You didn't save", vbOKOnly
            Cancel = True
            Exit Sub
        End If
   
       Application.EnableEvents = False
        ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
       
    End If
End Sub

Any suggestions to code or work-around?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Roy CoxGroup Finance ManagerCommented:
Add something like this to the stat of the code

 
If Application.UserName <> "your username" Then Exit Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
pls try

If SaveAsUI = True And Application.UserName <> ""your username" Then
 or before that line

If Application.UserName = "your username" Then Exit Sub
0
NackeyQualitative ConsultantAuthor Commented:
Sorry Rgonzo1971, I don't know where in my code to place your suggested code:

If SaveAsUI = True And Application.UserName <> ""your username" Then
 or before that line

If Application.UserName = "your username" Then Exit Sub
0
NackeyQualitative ConsultantAuthor Commented:
Okay, by trial and error I managed to make it work. Maybe not perfect but it works.

The code :
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Application.UserName = "My Name" Then

Exit Sub
End If

Dim txtFileName As String
 
   If SaveAsUI = True Then
        Cancel = True
 
       txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "You didn't save", vbOKOnly
            Cancel = True
            Exit Sub
        End If
   
       Application.EnableEvents = False
        ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
       
    End If

End Sub

Open in new window

0
NackeyQualitative ConsultantAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Nackey's comment #a40865251
Assisted answer: 500 points for Roy_Cox's comment #a40865209

for the following reason:

Not perfect answers to my question, but it sure put me on the right course.
0
Roy CoxGroup Finance ManagerCommented:
You need to get your user name unless it is My Name. Just run a macro to get your user name
Sub TestUserbame()
 MsgBox Application.UserName
End Sub

Open in new window


Then replace My name with your actual username

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim txtFileName As String
 
   If SaveAsUI = True And Application.UserName <> "My Name" Then
        Cancel = True
 
       txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "You didn't save", vbOKOnly
            Cancel = True
            Exit Sub
        End If
   
       Application.EnableEvents = False
        ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
       
    End If

End Sub

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
have you tried my final code?
0
NackeyQualitative ConsultantAuthor Commented:
Roy_Cox, your final code works perfect. Thanks for taking your time to help me.
0
Roy CoxGroup Finance ManagerCommented:
No problem I was working on another question so try to post guides before.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.