3

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

enter image description here

Community
  • 1
  • 1
user3714330
  • 679
  • 12
  • 32
  • @Davesexcel I edited my question and added the "main" `Sub` procedure there the macro goes when you click the button. – user3714330 Nov 12 '15 at 15:49
  • Can you step through the code and see when it goes to the different workbook? When you are in VB Editor, press F8 to step through the code. – Davesexcel Nov 12 '15 at 18:34
  • @Davesexcel it works normally in debugging. It opens the previous workbook when I click the button in the custom ribbon. For some reason, when I created the newer workbook with "Save As", it copied everything, even the Macro-button, but the button is linked to the original workbook. – user3714330 Nov 12 '15 at 22:35

4 Answers4

5

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.

sumgai
  • 51
  • 1
  • 5
  • Thank you Nathan for finding and exhibiting that inline code. I'll figure out for myself how to do that, the next time I post. (Read as: I'll consult the Help file first.) – sumgai Mar 14 '17 at 02:43
  • it works for me for Office 2016. Thank you! I would like to add more information in the step 4. After select `Export all customizations` you will get a file with extension `ExportedCustomizations.exportedUI`. You need to right click on that file and use any text editor to open it, e.g. Notepad++. After that you can edit the reference as @sumgai mentioned above – hoang tran Mar 08 '20 at 22:36
  • Thank you so much for this!!! It was driving me crazy for 2 weeks. I was using an Excel add-in file (.xlam) that had a customized ribbon control that I had converted from old command bars from earlier versions of Excel. When I saved the "source" .xlsm as an Add-In file, the macro buttons on the toolbar always referenced the source .xlsm file and I couldn't figure out where that reference was occurring. I just did a search-and-replace on "xlsm" to "xlam" within the exportedUI file, re-imported it and it was fixed. Huge relief! – rickj_65 Feb 18 '22 at 04:02
0

Just to confirm what sumgain have write above.

It works perfectly just do as he said : remove the part after the "x1:" that refers to a specific workbook until the begining of the maccro's name. example : When you export your custom ribbon with the maccro attached to it it will be write like below : idQ="x1:C:_FolderName_Filename.xlsm_Fill_Formulas_Cells"

THen you remove the part mentionned and it will become like that :

idQ="x1:Fill_Formulas_Cells"

Same for onAction keep only the Maccro Name Then it will works perfectly as long as you the maccro's name in the workbook stay consistent if you modified the Macros name then you have to modified it in the exportedUI file.

Then when you will reload the new file you can check in the Excel Options customize ribbon on the customize button if you put the pointer on you will see "Maccro: Name of your maccro"

And not the path of the file the maccro was from.

No need to use custom UI editor or any other things such as personnal maccro at least for that and if you are not bother to have custom ribbon in all of your woorkbook.

As well it is obvious but still good to remind it, you need to have the maccro in the workbook this procedure is just there to call the maccro that are associate to the workbook, it doesn't contain the code of the maccro.

Cheers Romain

Romain P-H
  • 1
  • 2
0

Does this still work? I have done this in the past with success but can't seem to get it to work now. I export the file, edit it and import it back in. it appears to work, but when i close the Ribbon options pane, my custom buttons disappear.

Jeremy
  • 1
-1

Same exact thing happened to me. There's a way to get around this.

By default, when you create a macro in Excel and run that macro through a custom ribbon button, that ribbon button macro works only in the workbook that contains it.

To get around this and have the button macros work in all workbooks, you'll need to create a Personal Macro Workbook. Then any macros that you store in your personal workbook on a computer become available to you in any workbook whenever you start Excel on that same computer.

Create a Personal Macro Workbook

To get the same ribbon button macros to work on another computer, you'll need to copy the Personal Macro Workbook to another computer and store it in the XLSTART folder. The link above has all the information you'll need.

Note: Delete the old ribbon button macros. Make sure you create new ribbon button macros that reference the macros from your Personal Macro Workbook.

roncruiser
  • 19
  • 1
  • 4