Excel 2016: Custom Ribbon - Macros try to launch from the original workbook if you do a save as on the file

[ajay_gajree]

An odd one

I have a file with several macros in it, when I used buttons on the worksheet an assign macros then when I save as the file(It is under development, so I regularly save new version with a new name) it works fine.

I have now removed the button on the worksheet and moved all the macros onto a custom ribbon.
When I now save as the file then when using any of the macros on the ribbon it opens the previous version of the file and uses the macros in that file!

Does anyone know what is going on and how this an be fixed?

Thanks!

 

[Yongle]

Try this workaround
-overwrite the original file every time you save the current file

Paste this code into the ThisWorkbook module of the latest file and amend path and file name to match what Excel is using via the ribbon

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Const fPath = "[COLOR=#b22222]C:\folder\subfolder[/COLOR]"
    Const original = "[COLOR=#b22222]NameOfOriginalWorkbook.xlsm[/COLOR]"
    If ThisWorkbook.Name = original Then Exit Sub
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs fPath & "\"  & original
    Application.DisplayAlerts = True
End Sub