How to reload a Custom Ribbon using VBA?

[rksample]

Excel Gurus,

I have a problem with Custom UI tabs.

I created two applications both of which have Custom Ribbon tabs. These two applications are meant to work simultaneously. If I am working on Application 1, I have to open the workbook of Application 2.

The issue is. Sometimes, when I close one of the applications (Say application 1), the tab is that is supposed to be on Application 2 is also dissapeared.

I tried several methods to fix this but not able to fix it. However, I want to provide a macro and insert the button in the welcome sheet. This macro is supposed to re-initiate the ribbon. I added this macro to the Add-ins.

Can you please help me with this code?

I attached samples of both the files. These are replicas of the main applications stripped down of all the information and possess dummy macros. (This is done due to the sensitivity of the information it holds.)

Application 1.xlsm
Application 2.xlsm

XML Code I used in the custom RibbonUI

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI o n L o a d="R i b b o n O n L o a d" xmlns="http://schemas.microsoft.com/office/2006/01/customui">

  <ribbon>

    <tabs>

      <tab id="MyCustomTab1" label="Application 1" insertBeforeMso="TabHome" getVisible="GetVisible" tag="Admin" >
				
  	  <group id="AdminGroup1" label="Group 1">
          <button id="AdminButton1" label="Button 1" size="normal" onAction="Button1_Run" image="plus" />
          <button id="AdminButton2" label="Button 2" size="normal" onAction="Button2_Run" image="cancel" />
          <button id="AdminButton3" label="Button 3" size="normal" onAction="Button3_Run" image="modify" />
        </group>

        <group id="AdminGroup2" label="Group 2">
          <button id="AdminButton4" label="Button 4" size="normal" onAction="Button4_Run" image="admin" />
          <button id="AdminButton5" label="Button 5" size="normal" onAction="Button5_run" image="change" />
          <button id="AdminButton6" label="Button 6" size="normal" onAction="Button6_run" image="cancelchange" />
        </group>

      </tab>

      <tab id="MyCustomTab2" label="Application 1" insertBeforeMso="TabHome" getVisible="GetVisible" tag="TL" >
				
  	  <group id="TLSOGroup1" label="Group 1">
          <button id="TLSOButton1" label="Button 1" size="large" onAction="Button1_Run" image="plus" />
          <button id="TLSOButton2" label="Button 2" size="large" onAction="Button2_Run" image="cancel" />
    	    <button id="TLSOButton3" label="Button 3" size="large" onAction="Button3_Run" image="modify" />
	  </group>

        <group id="TLSOGroup2" label="Group 2">
          <button id="TLSOButton5" label="Button 4" size="large" onAction="Button4_run" image="change" />
          <button id="TLSOButton6" label="Button 5" size="large" onAction="Button5_run" image="cancelchange" />
        </group>

      </tab>

    </tabs>
  </ribbon>
</customUI>

Any help with this code will be of great use to me. Thank you,

Regards,
RK

 

[RoryA]

Hi,
The first thing I would suggest is that you change the names of all your callbacks (including the o n L o a d callback) so that they are unique to each application.

 

[Sektor]

The thing is that Excel can load ANY callback which you can't predetermine. You should make ALL callbacks different.

 

[rksample]

Dear Rorya,

Thanks for pointing that. I prepared these files in a hurry.

Anyways, it seems that I fixed the issue. I'll implement it in the main applications and check if its working. If so I shall post the file with the solution.

One quick question:
Why do you think the Custom Ribbon is failing once in a while? And that too in 2007. I didn't find this issue to be replicated in 2010.

To recap the scenario for you - I open 2 applications, both of which have two different custom ribbons with macros specific to those workbooks. I am able to use all the macros without any issues.

But when I close one workbook (say Application 1), the ribbon in the other workbook is dissappeared. And this happens only sometimes. Most of the times I find no issues.

Thanks again for your time,

Regards,
RK.

 

[rksample]

The thing is that Excel can load ANY callback which you can't predetermine. You should make ALL callbacks different.

Dear Sketor,

Do you mean that when I try to close Application 1, it is calling the callback of Application 2 to close the ribbon? Is this the reason why the ribbon is closed.

Thanks for your time,

Regards,
Ravi.

 

[RoryA]

You have callbacks to show/hide the tabs but no way of specifying which callback gets called. So it seems quite possible that your other tab is simply not getting reshown as its getvisible callback is not getting called.
It is therefore wise to always use a project specific callback. I would also suggest using different ids for your tabs and controls, personally.

 

[rksample]

Yes Rory. I did have different name for the tabs and all the controls.

But you are right. I didn't do the same for ****** and getvisible callbacks. I'll make that change and check it first. In that case, I don't think the work around is necessary at all

Regards,
Ravi.

 

[Sektor]

The Ribbon doesn't have object model. It has only interface. This interface object IRibbonUI is what you use to talk with Ribbon. The Ribbon searches all callbacks in opened workbooks and use first found. Make different ids for each control.
Also, there's a splendid book called "RibbonX: Customizing the Office 2007 Ribbon". And it's the only book fully dedicated to RibbonX.

 

[rksample]

Thanks Rory and Sketor. You saved my life

I'll try that out and will let you know the update.

Thanks again,

Ravi.

 

[rksample]

Dear Rory and Sketor,

Sorry for the late update.

I did change the IDs for all callbacks as you mentioned. But still the issue is repeating. When one workbook is closed, the custom TAB in the other opened workbook is disappearing.

I am at lost. Is it possible for one of you to take a look at the actual files if I PM then to you? I do want to nail this bug.

Regards,
RK.