This post is a follow-on to my earlier Adding a button (and a ComboBox) to the Ribbon post. I'd suggest reading that post first if you have not already ...
For this post, though, the example code provided below will work only in Excel ... though it won't take much effort to make it work in Word or PowerPoint once you understand the principles involved.
I'm also assuming you have downloaded and installed the Office RibbonX Editor (referred to as the 'Editor' in the rest of this post) ... if not, the above mentioned post includes details on how to do that.
So first, in Excel, create a macro-enabled workbook (ie .xlsm) file, ensure it has at least two Sheets (one of them called 'Sheet1' the other called anything you want), save it then close it. Open it in the Editor and insert an Office 2010+ Custom UI Part into it. Then paste in the following RibbonX code:
Some explanation is in order I think:
The 'onLoad' in the second line is the key line for making the Ribbon controls dynamic. This causes the instance of the IRibbonUI object (effectively, 'the Ribbon') to be passed into your VBA code in a callback procedure (in this case, a procedure called OnLoadRibbon in a standard Module called RibbonCallbacks)
Contrasting with the above mentioned post (in which we added our custom controls to a built-in Tab), we are adding our own custom Tab (use of the 'id' attribute instead of the 'idMso' attribute) ... in this case, the Tab will appear before the built-in 'Home' Tab
We're also adding a 'DialogBoxLauncher' ... this is the tiny little 'tick' that you see in the lower right-hand corner of many of the Groups in the existing Ribbon ... for example, in the 'Home' Tab of the Ribbon in Excel, you can see this in the 'Clipboard', 'Font' and other Groups
As always, 'Validate' your RibbonX then save, then click Generate Callbacks and copy the procedure signatures. For now, you can close the file in the Editor and then open it in Excel. As happens often when adding RibbonX, you will see an error when opening the file in Excel but not to worry, its just that we've added the callbacks in the RibbonX but not in the VBA code ... let's do that now.
Open the VBE, add a standard Module and rename it to 'RibbonCallbacks' then paste in the 4 callback procedures. You also need to add a Module-level variable to hold the IRibbonUI object, code to assign it and some test code. Like this:
The DialogBoxLauncher will now work and you will see the appropriate MsgBox if you click it ... but the Button will be disabled. For the OnLoadRibbon callback to run, you will need to save, close and re-open the file (if prompted by Excel, accept any security warnings) ... try it now. When you re-open the file, you will see the "The Ribbon was loaded" MsgBox and the Button will now be enabled.
Now, there is a key thing to learn when working with the IRibbonUI object. I'm gonna demo this for you. Open the VBE and put the cursor in the 'TestTheRibbon' procedure and press F5 to run. You will see a message, not surprisingly, telling you that "The ribbon type is IRibbonUI". Now change the name of the procedure ... any change will do ... perhaps to 'TestTheRibbonAgain' ... ensure the cursor is on, or within, the renamed procedure and again press F5 to run. This time you will see "The ribbon type is Nothing". What happened? Well, when you make a Module-level change to your VBA code (or, when running your code, there is an error) then all Module-level variables lose their state ... including mRibbon in which we are storing the IRibbonUI object. This is normal behaviour. But what's worse when working with the Ribbon is that the IRibbonUI object is only passed-in when the file is first opened. There is no way (using pure VBA) to get it back other than closing and re-opening the file. This, you can imagine, makes working with the IRibbonUI object a complete pain in the <insert body part here>. However, you do only need the IRibbonUI object for the purposes of making the Ribbon dynamic ... you'll see that the callbacks when you click the Button and/or DialogBoxLauncher still work fine (try them and see).
Anyway, knowing that, if the dynamic-ness of the Ribbon stops working, you now know the likely reason why. It also means that you also need to check that the mRibbon object is valid before using it.
Another question you should have at this point ... how come the Button was disabled initially but enabled after the VBA code was added and the file re-opened. The magic is in the ButtonCountGetEnabled() procedure/callback in which we set the value of the returnedVal parameter to true. returnedVal is ByRef meaning that it is passed 'by reference' ... the caller of ButtonCountGetEnabled() gets the value that we give returnedVal ... and the caller is the Button itself. In the RibbonX, ButtonCountGetEnabled is the value given to the Buttons 'getEnabled' attribute so, you guessed it, whatever (Boolean) value we set to returnedVal determines whether the Button is enabled or not. If you don't set a value to returnedVal then the Button will be disabled (because the default value for a Boolean in VBA is False).
So, now replace the code in the RibbonCallbacks Module with this:
And in the ThisWorkbook Module, add:
Save, close and re-open the workbook (cause, remember, mRibbon will have lost state) then have a play around with your creation. If you did what I said at the start and have multiple Sheets one of which is called 'Sheet1' then you will see the Button is enabled when it (ie Sheet1) is showing and is disabled otherwise. Hopefully you can see how this all hangs together:
When you change which Sheet is showing, the SheetActivate event handler is called in ThisWorkbook
That event handler calls the RefreshTheRibbon procedure
The RefreshTheRibbon procedure tells the Ribbon to invalidate itself *
The Ribbon invalidates ALL of its controls
The controls call various of the callbacks that have been assigned to them in RibbonX (for the details of which callbacks, see the MS Docs, for example for a Button https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/846e8fb6-07d3-460b-816b-bcfae841c95b) - one of those callbacks that we assigned in our RibbonX is getEnabled
The getEnabled attribute is set to "RibbonCallbacks.ButtonCountGetEnabled" and so our ButtonCountGetEnabled() callback is called and it returns (via returnedVal) a True or False value based on the name of the active Sheet
* this is fine for demo purposes but not great for production code ... really we should be using the InvalidateControl method of the IRibbonUI object and passing in the name of the specific control we want to invalidate (ie "ButtonCount" in this case) rather than invalidating every control ... this is demonstrated below.
The above is the principle used for making the Ribbon dynamic - whether in Excel or in Word or PowerPoint or any other Office app that uses VBA and has a Ribbon: identify the event that triggers when you want to update a Ribbon control, hook it up so that it calls Invalidate (or InvalidateControl or InvalidateControlMso for a built-in control). For the MS Docs on the IRibbonUI object, see https://learn.microsoft.com/en-us/office/vba/api/office.iribbonui.
So how about that dynamic ComboBox then?
This is what you will get:
The following example adds a ComboBox in which the items match the names of the Sheets (Worksheets and Chart sheets) in the the workbook … it also allows you to enter a name into the ComboBox to add a new Worksheet (though it doesn’t check whether the name is valid ... this post is about making the Ribbon dynamic, not about validating Worksheet names!).
I'm not going to provide much explanation (relative to the amount of RibbonX and VBA code that follows!) as the principles involved are all outlined above. However, a few key points to note are:
The 'index' parameter used by various of the callbacks is 0-based whereas the collection of Sheets in Excel is 1-based - hence we have to adjust by 1
The id returned by the getItemId must be unique across the workbook (ie not just unique for the particular control) so recommend prefixing the id with the id of the parent control
Following my own advice (above), I updated the RefreshTheRibbon procedure to use the InvalidateControl method
I added a cheeky little Separator in the RibbonX, between the Button and the ComboBox ... this just helps to split up separate elements within a single Group
I went to town on the number of callbacks ... likely you wouldn't ever add so many ... for the MS Docs on what each one does, see https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/3caf6b18-34cb-4352-bd31-77a26980c00e
This is the entire RibbonX (close the workbook in Excel, use the Editor to replace all of the existing RibbonX with the following):
And this is the entire contents of the RibbonCallbacks module:
You need to have the SheetActivate event handler in ThisWorkbook, just as it was before. And, as mRibbon will have lost state, you need to save, close and re-open the workbook before this will work.