 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
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 and higher. First download the example workbook below.
Download:
EnableDisableTest.zip
Be sure you also read this page if you have problems: losing the state of the global IRibbonUI ribbon object
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
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
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