Create Ribbon/Tab in Excel 2007-2010

[RonRepp]

Hi:

I've found a way to do this through regular VB, but can someone lead me to an example on how to create a tab & Ribbon in VBA?

I'm using it in XL 2010, but 2007 should work.

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[GlennUK]

How do you do it in regular VB?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

in other words, post your VB code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RonRepp]

Straight from MS Help:

Add a Reference to the IRibbonExtensibility Interface

At the top of the code window, add the following statements to set aside memory for the Word Application object that you added earlier, and to create a reference to the ribbon. 

   
Option Explicit 
  
Dim oWD As Object    
Implements IRibbonExtensibility 
 

Implement the IRibbonExtensibility Interface

In this step, you implement the IRibbonExtensibilty interface's only member, GetCustomUI. Add the following procedure at the bottom of the code window. 

   
Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String 
      IRibbonExtensibility_GetCustomUI = GetRibbonXML() 
End Function 
 

This procedure calls the GetRibbonXML method that, as its name implies, returns the customization XML to the GetCustomUI method which then adds it to the ribbon to implement when the add-in loads. 

Retrieve the XML Customization Code

In this step, you add the GetRibbonXML function. Here, the customization code is stored in a String variable that is returned to the GetCustomUI method. 

   
Public Function GetRibbonXML() As String 
   Dim sRibbonXML As String 
 
   sRibbonXML = "<customUI xmlns=""[URL unfurl="true"]http://schemas.microsoft.com/office/2006/01/customui""[/URL] >" & _ 
                "<ribbon>" & _ 
                "<tabs>" & _ 
                "<tab id=""CustomTab"" label=""My Tab"">" & _ 
                "<group id=""SampleGroup"" label=""Sample Group"">" & _ 
                "<button id=""Button"" label=""Insert Company Name"" size=""large"" onAction=""InsertCompanyName"" />" & _ 
                "</group >" & _ 
                "</tab>" & _ 
                "</tabs>" & _ 
                "</ribbon>" & _ 
                "</customUI>" 
    
   GetRibbonXML = sRibbonXML 
    
End Function 
 

Add the Procedure That Is Called When You Click the Button

Add the procedure that gets called when you click the custom button: 

   
Public Sub InsertCompanyName(ByVal control As IRibbonControl) 
   ' Inserts the specified text at the beginning of a range. 
   Dim MyText As String 
   Dim MyRange As Object 
   Set MyRange = oWD.ActiveDocument.Range 
   MyText = "Microsoft Corporation"   
   ' Inserts text at the beginning 
   ' of the active document. 
   MyRange.InsertBefore (MyText) 
End Sub

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[RonRepp]

BTW, if I was going to do it similar to the MS Help example, I'd probably just create it in VB.Net 2010, but was hoping someone could point me to a VBA example, so that it could be used in an Excel created Add-in.

Thanks,

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net