Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Enable or Disable custom Ribbon buttons in Excel 2007-2016

Information

If you are not familiar with creating Custom Tabs, Groups or controls on the Ribbon with RibbonX follow the steps on this page first before you start with the examples on this page.

On the download on this page you can find a few examples to enable/disable custom Ribbon buttons with VBA code in Microsoft Excel 2007-2016. First download the example workbook below.

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.

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 and 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 EnableControlsWithCertainTag2
    End Select
End Sub