Modifying XLA to show toolbar in XL 2003 and ribbon tab in XL 2007

[jean grey]

Hi everyone.
I have an XLA file created using Excel 2003. It has VBA code for defining
the menus and toolbars, and the macros associated with them. So for example,
my VBA code adds the "CustomFunctions" menu and a toolbar by the code:

Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cmbBar.Controls("Help").index

Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup,
before:=iHelpIndex)
With cmbControl
.Caption = "CustomFunctions"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Function1"
.OnAction = "Action1"
End With
End With

cmbBar.Visible = True

Set NewButton = cmbBar.Controls.Add(Type:=msoControlButton)
With NewButton
.OnAction = "Action1"
FaceId = 80
End With

My code works perfectly fine. However, I need the same XLA file to be opened
using Excel 2007. I already have an idea on how to create/modify ribbon tabs
in XLSM or XLMA file but what I don't know is how to modify the VBA code of
the old XLA file itself (meaning not to re-write it as XLSM or XLMA) to
change "CustomFunctions" into a new ribbon tab when opened in Excel 2007.
Currently, when I open it, the menu and toolbar are automatically transferred
to the "Add-Ins" tab.

Thanks in advance.

 

[Bob Phillips]

See http://www.rondebruin.nl/compatiblemenu.htm

 

[jean grey]

Thank you for the examples, Bob. I'm now referring to them.
But ... isn't there a way to dynamically create and load the UI
customizations?
Based on the MSDN website, this can be done in MS Access by calling
Application.LoadCustomUI, like the example in:

http://msdn.microsoft.com/en-us/library/aa338202.aspx
Section: Loading Customizations at Run Time

Does Excel 2007 have this kind of functionality?

 

[jean grey]

And oops, I have another problem.
I need IRibbonUI for the Invalidate command.
However, this is not supported in Excel2003 so an error occurs.

 

[jean grey]

To resolve the problem below, I transferred the codes that use IRibbonUI to
the XLAM file so that when the XLA file is loaded using Excel 2003, those
codes will not be read (there is a part in the code which opens the XLAM only
if the version is Excel 2007).

This is the code in the XLAM file:

Public Rib as IRibbonUI

' callback for customUI.onload
Sub ribbonLoaded (ribbon as IRibbonUI)
Set Rib = ribbon
End Sub

Sub InvalidateRibbon()
Rib.Invalidate()
End Sub

and in the XLA file, I only call InvalidateRibbon if the version is Excel
2007:

If Val(Application.Version) >= 12 Then
Application.Run "XLAFile.xla!InvalidateRibbon"
End If

The add-in now runs perfectly in Excel 2003. But in Excel 2007, I get the
error message, "Cannot run the macro 'XLAFile.xla!InvalidateRibbon'. The
macro may not be available in this workbook or all macros may be disabled."

Do you guys know what is wrong here?

Thanks in advance. I hope I get a reply.