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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
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
36,066
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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