On this page you can find a few examples to enable/disable custom Ribbon buttons with VBA code in Microsoft Excel 2007-2010. First download the example workbook that is in a zip file named EnableDisableTest.zip.

Download:  EnableDisableTest.zip

When you open the workbook you see two custom groups on the Home tab with in each group three buttons that are disabled. Use the Custom UI Editor to see or edit the RibbonX that create this two groups if you want.
If this is new for you visit : http://www.rondebruin.nl/ribbon.htm

By default every Custom button is disabled, but you can change this if you want by using
MyTag = "Enable" in the Workbook_Open event in the ThisWorkbook module.
See the commented code in the ThisWorkbook module if you want that all the buttons are enabled by default.
Or you can call one of the example macros below in the RibbonOnload callback to enable only the buttons you want, this callback will automatically run when you open the workbook.See also the Tip section below.

When you look at the RibbonX you see that I add getEnabled="GetEnabledMacro" and  tag="Group1Button1"  to each line that add a custom button to the custom Ribbon groups.

<button id="G1B1" label="Caption 1" size="normal" onAction="Macro1" imageMso="DirectRepliesTo"
tag="Group1Button1" getEnabled="GetEnabledMacro"/>

This are the six tag names that I use for the buttons in the example file
First group : Group1Button1, Group1Button2 , Group1Button3
Second group : Group2Button1, Group2Button2 and Group2Button3

Note: you can also use the same tag for more the one control if you want



How do we refresh (Invalidate) the Ribbon ?

In the workbook there are 6 examples, click on the buttons to test them.
Every example will call the macro named RefreshRibbon and when we call the macro we include the tag that we want to enable/disable, after testing if the ribbon connection is OK it will Invalidate the Ribbon.

When you Invalidate the Ribbon the GetEnabledMacro callback will be called for each button.
This callback will enable or disable the control depending on the tag name.
Look at all the code in the module named "RibbonModule" in the VBA editor (Use Alt F11 to open the editor)


Call RefreshRibbon(Tag:="*")
When you use the wildcard * it will match all tags so all controls are enabled


Call RefreshRibbon(Tag:="")
When you use "" it will not match any tag so all controls are disabled


Call RefreshRibbon(Tag:="Group1*")
Tag's named: Group1Button1, Group1Button2 and Group1Button3 are enabled


Call RefreshRibbon(Tag:="Group2*")
Tag's named: Group2Button1, Group2Button2 and Group2Button3 are enabled


Call RefreshRibbon(Tag:="Group1Button1")
We only use the tag of the first button in the first group


Call RefreshRibbon(Tag:="Group?Button1")
We use the tag of the first button in both groups (The ? is the wildcard that represents any single character)


Other wildcards that are useful are :

"*myword*"  Contains the word myword
"*myword"   Ends with the word myword
"myword*"   Begins with the word myword



Tips

Like I say on this page you can call one of the example macros above in the RibbonOnload callback to enable only the buttons that you want by default like this.

Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
    Call EnableControlsWithCertainTag3
End Sub

But you can also test the username for example to give some users more options then others

Sub RibbonOnLoad(ribbon As IRibbonUI)
    Dim sUserName As String
    sUserName = Application.UserName

    Set Rib = ribbon
    
    Select Case sUserName
    Case "Ron de Bruin": Call EnabledAllControls
    Case "Nancy Davolio": Call EnableControlsWithCertainTag1
    Case Else: Call EnableControlsWithCertainTag3
    End Select
End Sub

More information

Change the Ribbon in Excel 2007 or Excel 2010
http://www.rondebruin.nl/ribbon.htm

On the page above you can find links to many other example pages.

Hide or Display Custom Ribbon Tab/Group/Control with getVisible
http://www.rondebruin.nl/hidevisible.htm

loss of state of the global IRibbonUI Ribbon object
http://www.rondebruin.nl/ribbonstate.htm