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.
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"
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
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)
When you use the wildcard * it will match all tags so all controls are enabled
When you use "" it will not match any tag so all controls are disabled
Tag's named: Group1Button1, Group1Button2 and Group1Button3 are enabled
Tag's named: Group2Button1, Group2Button2 and Group2Button3 are enabled
We only use the tag of the first button in the first group
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
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