Saving from xltm to xlsm : temporarily loss of macro's

miauwington

New Member
Joined
Sep 9, 2014
Messages
9
Hi folks,

I've created an excel template for my colleagues that relies heavily on macro's.
In the Add-ons menu I've created a "save" button that makes sure people save the xltm as an xlsm during first use.

The code for the save procedure is the following :
Code:
strFullPath = "X:\Project\" & strWerfnummer & "\03_Werfdocumenten\08_Verrekeningsvoorstellen\VV"fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=strFullPath, _
fileFilter:="Excel Files (*.xlsm), *.xlsm")
ActiveWorkbook.SaveAs fileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

The issue I'm facing is that after saving the file to xlsm the macro's don't work anymore (the error when clicking a macro indicates that excel looks for the macro's in an xlsx version of the file. Which does not exist. Nor did I use xlsx anywhere in my code.).
If the user closes the (now) xlsm file and reopens it, macro's work again. How can I avoid that the user has to reopen the file after saving it the first time ? All later use of the xlsm file is without issues. This only happens when it was first saved from xltm to xlsm.

Excel 2013, Windows 7

Thank you !
Regards, Miauw
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How are you running the macros?
 

miauwington

New Member
Joined
Sep 9, 2014
Messages
9
How are you running the macros?

Whoa...spot on ! Hadn't thought of that : We run the macro's from a custom add-on menu.
Apparently when I replicate the situation and run the macro's from the developer ribbon (the normal way) it still works.
So it has to do with my custom add-on menu, which is rebuild every time the excel opens.
Guess I will have to reload the menu programmatically after the user saves.

Thanks a billion ! :D
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you use XML in the CustomUI part of the template, it won't be necessary to rebuild anything.
 

miauwington

New Member
Joined
Sep 9, 2014
Messages
9
If you use XML in the CustomUI part of the template, it won't be necessary to rebuild anything.

Well..unfortunately no XML. I was lucky enough to get it working with the code below :

Code:
Public Const bar_VV As String = "bar VV"


Sub AUTO_OPEN()
    Call CREATEBAR_VV
End Sub


Sub CreateBar_VV
Dim iCtr As LongDim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant
Call REMOVEBAR_CLIENT
MacNames = Array("OPSLAAN", "GA_NAAR_SV", "VERREKENINGSVOORSTEL", "COPY_VV", "VV_VERWIJDEREN", "RIJ_TOEVOEGEN", "MEERDERE_RIJEN_TOEVOEGEN", "RIJ_VERWIJDEREN", "SAMENVATTING_GENEREREN", "VVNAAM_WIJZIGEN", "GROEPEREN", "DEGROEPEREN", "KLAD_VERBERGEN", "KLAD_TONEN")
    CapNamess = Array("OPSLAAN", "SAMENVATTING", "NIEUW VV", "COPY VV", "VERWIJDER VV", "NIEUWE RIJ", "MEERDERE RIJEN", "VERWIJDER RIJ", "GENEREREN", "VV NAAM WIJZIGEN", "GROEPEREN", "DEGROEPEREN", "KLAD VERBERGEN", "KLAD TONEN")
    With Application.CommandBars.Add
        .Name = bar_VV
        .Left = 200
        .Top = 200
        .Protection = msoBarNoProtection
        .Visible = True
        .Position = msoBarFloating
        For iCtr = LBound(MacNames) To UBound(MacNames)
            With .Controls.Add(Type:=msoControlButton)
                .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
                .Caption = CapNamess(iCtr)
                .Style = msoButtonIconAndCaption
                .FaceId = 71 + iCtr
            End With
        Next iCtr
    End With
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,092
Messages
5,835,344
Members
430,351
Latest member
ddalton

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