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

[miauwington]

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 :

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

 

[miauwington]

bump, sorry. Anyone ?

 

[RoryA]

How are you running the macros?

 

[miauwington]

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]

If you use XML in the CustomUI part of the template, it won't be necessary to rebuild anything.

 

[miauwington]

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 :

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