Enable/Disable Ribbons at runtime

Feb 17, 2013 at 6:54 PM
Hi,

I'm currently working on an excel plugin where I want to enable/disable Ribbons at runtime. To be more specific - depending on existing document properties - I want certain ribbons to be enabled/disabled. (clickable or grayed out).

In an earlier post, artnib mentioned the getEnabled method which could be used for ribbons. From what I observed, this method is only evaluted once - while the add-in is initialized.

I don't really understand, how I can bring this in line with events, e.g. when a new workbook is opened.

Any hints how this can be achieved?
Developer
Feb 19, 2013 at 2:27 AM
Edited Feb 22, 2013 at 5:26 PM
In your ribbon markup specify
  1. method which gets ribbon reference (onLoad attribute)
  2. method which returns Enabled property value for the ribbon control
<?xml version="1.0" encoding="utf-8" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
          onLoad="RibbonLoaded" >
  <ribbon>
    <tabs>
        <tab id="ExcelRibbonAddin" label="ExcelRibbonAddin">
        <group id="group1" label="Example">
          <button id="nextButton" label="Go Next Sheet" size="large"
                  onAction="OnAction" getEnabled="GetEnabled" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
In your addin code
  1. add field which holds ribbon reference (_ribbon)
  2. add method which gets ribbon reference (RibbonLoaded)
  3. add method which returns Enabled property value for the ribbon control (GetEnabled)
  4. application event handler which invalidates the ribbon control
Dim WithEvents _application As Excel.Application
Dim _ribbon As NetOffice.OfficeApi.IRibbonUI

#Region "Ribbon callbacks"

  Public Sub RibbonLoaded(ribbon As Office.IRibbonUI)
    _ribbon = ribbon
  End Sub

  Public Function GetEnabled(ByVal control As Office.IRibbonControl) As Boolean
    Select Case control.Id
      Case "nextButton"
        Return _application.ActiveWorkbook.Sheets.Count > 1
      Case Else
        MessageBox.Show("Unkown Control Id: " + control.Id)
    End Select
  End Function

#End Region

Public Sub WorkbookOpen(wbk As Excel.Workbook) Handles _application.WorkbookOpenEvent
    _ribbon.InvalidateControl("nextButton")
 End Sub

Public Sub OnAction(ByVal control As Office.IRibbonControl)
    Try

      Select Case control.Id
        Case "nextButton"
          Dim wbk As Excel.Workbook
          wbk = _application.ActiveWorkbook
          Dim sheet As Excel.Worksheet
          sheet = wbk.ActiveSheet
          Dim index = sheet.Index
          If index < wbk.Sheets.Count Then
            wbk.Sheets(index + 1).Activate()
          Else
            wbk.Sheets(index - 1).Activate()
          End If
        Case Else
          MessageBox.Show("Unkown Control Id: " + control.Id)
      End Select

    Catch throwedException As Exception

      Dim details As String = String.Format("{1}{1}Details:{1}{1}{0}", throwedException.Message, Environment.NewLine)
      MessageBox.Show("An error occured in OnAction: " + details, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Try
  End Sub
The sample code above demonstrate addin which adds to Excel ribbon Example tab with Go Next Sheet button. The project was generated with NetOffice VS Project Wizard. The button state is invalidated when workbook is opened. The button allows go next/previous sheet and is enabled only for workbooks with more than one sheet. Of course this is unfinished solution: we should handle WorkbookActivate event for the proper button behavior.
Actually there is no need to invalidate control in this example as GetEnabled always works with active workbook. So WorkbookOpen method is only for demonstration how you can update ribbon control state.