Enable or Disable custom Ribbon buttons

If you are not familiar with creating Custom Tabs or Groups on the Ribbon with RibbonX follow the steps on this page first before you start with the examples on this page : Change the Ribbon in Mac Excel with RibbonX

Download Example file

Download the example workbook
16-Jan-2021 (1 file)

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.

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 (with or without wildcards) 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 Option F11 to open the editor)

Tips

Like I say on this page you can call one of the example macros 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
26-February-2021
Web design by Will Woodgate