How to reload a Custom Ribbon using VBA?

rksample

New Member
Joined
Oct 13, 2010
Messages
47
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


HTML:
<?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
 
Last edited:
I have PMd you an email address. Will post any findings back here.
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
I have just replied to you. I really need the workbooks in a state where they will actually compile to start with to rule out that as a source of error. Also, it appears that removing the End statement from your ResetAll seems to cure the problem.
 
Upvote 0
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,

I know this post is old but I searched for a while when I had similar issues so I thought I would respond. I often run multiple workbooks based on the same base workbook. Therefore the ribbon code is identical. What I found that seems to help is to create a workbook object (thisWorkbook.RibbonUI in my case) and setting the pointer to it. It seems to help Excel avoid confusing which ribbon(s) to access. I also use a bit of code (found elsewhere on this site if I remember correctly) that saves the pointer in case the ribbon gets lost. This will help recover the ribbon fairly well in most situations. If you're interested I've pasted it below. If this is a rehash or out of place, my apologies. Otherwise, I hope it's useful.

''''''''''''' ThisWorkbook module in VBA'''''''''''''

Option Explicit
Private pRibbonUI As IRibbonUI

Public Property Let ribbonUI(iRib As IRibbonUI)
Set pRibbonUI = iRib
End Property

Public Property Get ribbonUI() As IRibbonUI
Set ribbonUI = pRibbonUI
End Property

''''''''''''' Ribbon module in VBA'''''''''''''

Option Explicit
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
Public Const cRibbonName = "Template" 'Added to facilitate easy naming of the ribbon and registry entry
Public strRegEntry As String 'Used to temporarily store the registry entry name based on the workbook name

Sub Initialize(ribbon As IRibbonUI) 'Executed when the ribbon loads
ThisWorkbook.ribbonUI = ribbon ' Used in conjunction with the object created in ThisWorkbook
strRegEntry = ActiveWorkbook.Name
SaveSetting cRibbonName, "Ribbon", "RibbonPtr", ObjPtr(ribbon) 'Store the entry in the registry (This is an Excel function)
End Sub

Function GetRibbon(ByVal lRibbonPointer As Long) As Object
'Retreive the ribbon pointer
Dim objRibbon As Object
CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
Set GetRibbon = objRibbon
Set objRibbon = Nothing
End Function

Sub RefreshRibbon()
'Reset the ribbon the the pointer stored in the registry if it gets lost
On Error Resume Next 'Ensure the code contiues to run if the pointer is lost
If ThisWorkbook.ribbonUI Is Nothing Then 'If the pointer is lost then retrieve it from the registry
Set ThisWorkbook.ribbonUI = GetRibbon(GetSetting(cRibbonName, "ribbon", "RibbonPtr"))
If Err.Number = 91 Then
MsgBox "I'm sorry, I was unable to recover the ribbon. Please save and reload the worksheet.", vbCritical, "Unrecoverable Ribbon Error"
End If
End If
ThisWorkbook.ribbonUI.Invalidate
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top