Macro button under customized ribbon tab tries to open old Excel file

Question

I created a custom ribbon tab on my Excel like Excel_app_v1.xlsm, and a button under this ribbon tab is connected to a macro. So when I click this button, the macro does some table importing applications.

The first strange thing is that I created this ribbon tab and the button for only this Excel file, but the ribbon tab and the button appear in all other Excel files, even if the original Excel file Excel_app_v1.xlsm is not open.

The second problem is that I created a second version of my previous Excel file with "Save-as" option. So the new Excel file is like Excel_app_v2.xlsm. When I click the button under the ribbon tab, it opens the first Excel file Excel_app_v1.xlsm, even if it is not open. I deleted the first Excel file, but then I got an error like "Couldn't find the Excel_app_v1.xlsm on the path".

So obviously the macro button under the customized ribbon tab is linked to the first Excel file, but I couldn't find the menu option to change this. I added ThisWorkbook before all the sheet expressions in the vba code, but it didn't solve the problem. The button-click is still trying to open the old excel file.

The VBA code is below. The button is linked to the Sub ImportTable. Firstly it asks the user if the user wants to continue with the process. It opens the previous Excel file right after clicking on the button, at the same time as the Message Box appears.

Sub ImportTable()

  Application.ScreenUpdating = False
  YearMonth = ThisWorkbook.Sheets("tab1").Cells(11, 2).Value
  ' The Macro button opens the previous Excel file before clicking Yes or No on the message box
  answer = MsgBox("Warning! Brings the newest source file. You want to continue?", vbYesNo + vbQuestion, "")

  If answer = vbYes Then

     RunSASCodeViaBatFile  ' Another Sub which runs bat file to run a SAS-code. But it doesn't matter. Because the problem happens before I click on Yes or No.  
     InsertSASFileIntoExcel

   Else  ' Nothing happens if clicking No on the Message Box
   End If

End Sub

Answer 1

The clue to fixing this quickly was posted below by roncruiser, with one slight twist.

Everyone on the web seems to feel that PERSONAL.XLSB is the key here — nope. In fact, playing with that file only confounded me for even longer. Here's what I did instead:

1. Right click the Ribbon and select Customize The Ribbon;
2. Navigate to the offending macros that you've installed with buttons;
3. Find and click on Import/Export;
4. Export your custom buttons (the macros will go right along just fine);

5. Open that resulting file, and edit out the offending references to the other file that's causing you so much grief — example:

   <mso:button idQ="x1:HideRows_0_EA10D6" label="HideRows" imageMso="_3DPerspectiveDecrease" onAction="!HideRows" visible="true"/>
   

   I took out everything after idQ-"x1... up to the actual name of the macro. I also took out the same external reference found in onAction="... Take everything up to the bang mark.

   Save this under whatever name you wish, but with the same extension (for my setup, it was called ExportedCustomizations.exportedUI (yes, that long an extension));

6. Repeat the first few steps here, but this time import your edited file.

7. Voila, all is golden.

No messing around with wiping out existing work and starting all over. Worked a charm for me, so a big tip o' The Hat to roncruiser for the clue.