Custom Ribbon: How To Enable/Disable Buttons at runtime

Oct 27, 2011 at 3:26 PM

Hello

I have a Custom Ribbon with several buttons on it setup using ExcelDNA which is great.  However, I cannot work out how to enable or disable the buttons at runtime.

Any ideas?  Help is much appreciated.

Many thanks
Jon

Coordinator
Oct 27, 2011 at 3:57 PM

Hi Jon,

You'll have to implement a "getEnabled" callback on those buttons. Then you might need to use an InvalidateControl call to trigger the callback.

Most of the ribbon properties have a callback version that allows you to set up a callback function to provide the value dynamically. 

One reference to the Ribbon controls and attributes is here: http://msdn.microsoft.com/en-us/library/aa338199(v=office.12).aspx

There was some more ribbon-related links and discussion in these threads from the Google group:

http://groups.google.com/group/exceldna/browse_thread/thread/f0492e4f9c4c94c5

http://groups.google.com/group/exceldna/browse_thread/thread/1db415c2700a650b

Regards,

Govert

Oct 27, 2011 at 5:03 PM

Hi Govert

Thanks, I have got it working in principal using onLoad to get a handle to the IRibbonUI and getEnabled callbacks to return whether a control on the Ribbon should be enabled or not.

Now, I'd like to enable/disable some of the buttons depending on whether there is a workbook loaded etc.  Are there any methods I can override that will be called when a new workbook is created, an existing workbook is opened or closed?

Kind Regards
Jon

Coordinator
Oct 27, 2011 at 5:40 PM

Hi Jon,

I think the Application object has a NewWorkbook, WorkbiookOpen, WindowsActivate/Deactivate events. Finding documentation if you are using Excel 2010 can be challenging.

For using the COM automation model I'd also suggest you check out the NetOffice project (http://netoffice.codeplex.com) which gives you version-independent interop assemblies with features like intellisense that tells you which Excel versions support each method.

-Govert

Nov 28, 2011 at 9:43 AM
Edited Nov 28, 2011 at 9:53 AM

Hi Govert

I am having no luck in getting events firing in my AddIn when the a Workbook is activate/opened, etc.  Do you have any ideas?

Kind Regards

Jon

I have added the following in the AutoOpen method of my AddIn:

// Setup the Excel Workbook Event Handlers.
((AppEvents_Event)XLApp).NewWorkbook += WorkbookAdded;
XLApp.WorkbookOpen += WorkbookAdded;
XLApp.WorkbookActivate += WorkbookActivated;
XLApp.WorkbookDeactivate += WorkbookDeactivated;

private void WorkbookAdded(Workbook workbook)
{
LOGGER.Debug("In WorkbookAdded().");

// Setup Sheet Activate/Deactivate event handlers.
workbook.SheetActivate += WorksheetActivated;
workbook.SheetDeactivate += WorksheetDeactivated;

// Invalidate the Ribbon to refresh the button states.
Ribbon.Invalidate();
}

private void WorkbookActivated(Workbook workbook)
{
LOGGER.Debug("In WorkbookActivated().");

// Invalidate the Ribbon to refresh the button states.
Ribbon.Invalidate();
}

private void WorkbookDeactivated(Workbook workbook)
{
LOGGER.Debug("In WorkbookDeactivated().");

// Invalidate the Ribbon to refresh the button states.
Ribbon.Invalidate();
}

private void WorksheetActivated(object sheet)
{
LOGGER.Debug("In WorksheetActivated().");

// Invalidate the Ribbon to refresh the button states.
Ribbon.Invalidate();
}

private void WorksheetDeactivated(object sheet)
{
LOGGER.Debug("In WorksheetDeactivated().");

// Invalidate the Ribbon to refresh the button states.
Ribbon.Invalidate();
}

Nov 28, 2011 at 10:16 AM

Hi Govert

I managed to sort this out myself, the Ribbon.Invalidate() call was giving a NullPointerException because Ribbon was not initialised until after the first WorrkbookAdded call.  So I just check for null prior to the Invalidate call.  All is fine now.

Kind Regards
Jon