Single .dna file for Ribbons and Commandbars

May 27, 2015 at 6:19 AM
Hi Govert,

Is there a way we can implement ribbons when opened from excel 2007 and above and command bars for the excel below 2003

I have implemented GetCustomUI for Ribbon

public override string GetCustomUI(string uiName)
    {
        return
        @"<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage' onLoad='OnLoadRibbonHandler'>
                <ribbon>
                <tabs>
                    <tab id='CogCustom' label='CogFinancial'> 
                    <group id='CogGroup' label='Cogencis'>
                        <button id='ButFinancials'  label='Financial'   size='large' getEnabled='GetEnabled'  image='Financial'  onAction='OnActionFinancial'/>
                        <button id='ButAggregates'  label='Aggregates'  size='large' getEnabled='GetEnabled'  image='Aggregates' onAction='OnActionAggregates'/>
                        <button id='ButSettings'    label='Settings'    size='large' getEnabled='GetEnabled'  image='Settings'   onAction='OnActionPreferences' />
                    </group >
                    </tab>
                </tabs>
                </ribbon>
            </customUI>";
    }
I want this should work for excel 2003 I tried command bars by implementing
<CustomUI>
<commandBars xmlns='http://schemas.excel-dna.net/office/2003/01/commandbars' >
  <commandBar name='Cogencis Menu'>
    <popup caption='CogFinancial' height='19'  >
      <button caption='Financial' image='Financial' onAction='ActionFinancial' ></button>
      <button caption='Aggregates' image='Aggregates' onAction='ActionAggregates' />
      <button caption='Settings' image='Settings' onAction='ActionPreferences' />
    </popup>
  </commandBar>
</commandBars>
</CustomUI>

1 . Here how can we enable disable Commandbars just like getEnabled in Ribbons
  1. How can I manage both the xml in .dna file so that according to excel version respective xml will be loaded
May 27, 2015 at 8:57 AM
Hello MShakya,

There is an example, CommandbarsAllWays.dna in the samples folder of the DNA download. This shows how to load the commandbars if you create a class that implements the IExcelAddin interface. If you want commandbars to only be shown for Excel 2003 then simply add " If ExcelDna.Integration.ExcelDnaUtil.ExcelVersion >11.0 Then Exit Sub" (I think thats the correct syntax for VB) in the first line of the AutoClose and AutoOpen Subs
Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
         If ExcelDna.Integration.ExcelDnaUtil.ExcelVersion >11.0 Then  Exit Sub
        ' MessageBox.Show("Starting up")
        Dim bars As CommandBars = ExcelCommandBarUtil.GetCommandBars()
        Dim menuBar As CommandBar = bars("Worksheet Menu Bar")
      
        Dim parent As CommandBarPopup = menuBar.Controls.AddPopup("Test Menu")
        parent.Caption = "Menu from code"
        Dim myButton As CommandBarButton = parent.Controls.AddButton()
        myButton.Caption = "Press Me"
        myButton.Style = MsoButtonStyle.msoButtonCaption
        myButton.OnAction = "MyFirstClickHandler"

        Dim myOtherButton As CommandBarButton = parent.Controls.AddButton()
        myOtherButton.Caption = "Dont Press Me"
        myOtherButton.Style = MsoButtonStyle.msoButtonIconAndCaption
        myOtherButton.FaceId = 1122
        myOtherButton.OnAction = "MySecondClickHandler"
        
        myMenuToDelete = parent

        LoadMenuFromString()

    End Sub
I think that should do the trick?
May 28, 2015 at 3:52 AM
hi pianoboy,

Thanks for the reply.
Is there any way to enable disable buttons via xml just like ribbons we provide function for getEnabled
May 28, 2015 at 6:44 AM
Hi MShakya,

To enable/disable commandbar buttons you can do it from code, in yout case it would look something like this:-

            CommandBar menu = cmdBars["Cogencis Menu"];
            CommandBarButton btnFinancial = (CommandBarButton) GetControl(menu, "Financial");
            btnFinancial.Enabled = true; // enable false disable
pianoboy