This post is a follow-on to my previous Creating an AddIn in Excel post which explains what an AddIn is, what would you use one for, how to create an AddIn and how to handle events with an AddIn. If you want to follow along with this post, you will first need to have created the AddIn in that post.
I've also added a zipped-up copy of the final AddIn at the end of this post ... but please try to create the AddIn yourself so that you understand the mechanics of it all and use the copy of the AddIn if you get really stuck?!
If you do download the copy of the AddIn, once you have unzipped it, ensure you 'unblock' the file before trying to use it ... for more details on this see my Unblock a file containing VBA code post.
Adding 'static' controls to the Ribbon
The benefit of using an AddIn to add Ribbon controls is that they are then visible (and usable) no matter which Workbook is active (or even if no Workbook is active). The process of adding controls is pretty much identical to doing so for a (normal … not an AddIn) Workbook and you can read about this in my post Adding a Button (and a ComboBox) to the Ribbon. The key differences are:
The file that you open (when adding the RibbonX) is the AddIn file (so if you followed my naming from the Creating an AddIn in Excel post it will be called 'My AddIn.xlam')
To be able to save the RibbonX that you create with the Office RibbonX Editor, you should close Excel entirely (as AddIns are loaded when Excel starts and unloaded when it closes … or you can manually unload and re-load the AddIn before and after saving the RibbonX)
Make sure that you add the 'RibbonCallbacks' Module to your AddIn … not to any other Workbook
Unlike in the above mentioned post where the VBA code uses ThisWorkbook (as the code was added to a specific Workbook in order to 'function on' that same Workbook), AddIn code will typically use the ActiveWorkbook (as the code is now being added to an AddIn in order to 'function on' whichever Workbook is active in the Excel user-interface, not to 'function on' itself … which is what ThisWorkbook would now do as an AddIn is just a special kind of Workbook)
Knowing the above, now follow the Adding a Button (and a ComboBox) to the Ribbon post to add 'static' controls to the AddIn before you then move on to add 'dynamic' controls in the next section of this post.
If you find that, after adding the RibbonX (and validating it) to the AddIn, that no controls appear in the Ribbon then unloaded and re-load the AddIn (in the Excel UI select File > Options > Add-ins > Manage > Excel Add-ins > Go then uncheck the box next to the AddIn, close the 'Add-ins' dialog then follow the process again but this time check the box next to the AddIn) ... sometimes Excel can be stubborn!
Adding 'dynamic' controls to the Ribbon
By 'dynamic' I mean controls that update based on some criteria e.g. a button that is only enabled when a specific Sheet is shown or a DropDown that dynamically updates the options available when it 'drops down' based on the names of Sheets in the active Workbook.
Noting the previous 4 'key differences', you can follow the Making Ribbon controls dynamic post, but:
Add the RibbonX to the AddIn (not to the macro-enabled Workbook that post refers to) replacing all of the existing RibbonX ... and ignore the comment about having 'at least two Sheets' (as AddIns don't have visible Sheets)
You will already have a RibbonCallbacks Module in the AddIn - replace all of the existing code in that Module with the new code from that post
Once you get to 'So, now replace the code in the RibbonCallbacks Module with this' (about half way) come back to this post
Again, if you find that, after adding the RibbonX (and validating it) to the AddIn, that no controls appear in the Ribbon then follow the above process to unloaded and re-load the AddIn.
At this point the 4th 'key difference' becomes critical. We don't want the Button and the DialogBoxLauncher to 'function on' the AddIn rather we want them to 'function on' whichever Workbook is active and so we change the code from using ThisWorkbook to using ActiveWorkbook … and because there is no guarantee that there is an active Workbook then we need to check that ActiveWorkbook is not Nothing. To do this, replace (again!) all of the code in the RibbonCallbacks Module with this:
And the Workbook_SheetActivate() procedure (that you would've added to the ThisWorkbook Module if you'd followed the Making Ribbon controls dynamic post) rather we want it in the CApp Module … as explained in the 'Handling events with an AddIn' section of the Creating an AddIn in Excel post, you should use the two DropDowns to add it by selecting 'xlApp' and then 'SheetActivate' and then add the call of RibbonCallbacks.RefreshTheRibbon so you have:
Note that mRibbon (in the RibbonCallbacks Module) is a Module-level variable which means that its value will be destroyed when there is a 'loss of state' (see the Making Ribbon controls dynamic post for more background). If you've got to this point after reading the Creating an AddIn in Excel post, the difference between mRibbon and both mApp and xlApp is that mRibbon cannot (using pure VBA) be re-instantiated without re-starting Excel as the IRibbonUI object is only ever passed to VBA code once and that is at the point when the Excel application starts and the RibbonX is loaded (and the VBA procedure pointed at by the RibbonX 'onLoad' is called and passed the IRibbonUI object). Note that it is only the dynamic aspect of the Ribbon that will stop working following a 'loss of state' … for example, button onAction callbacks will still function correctly.
So, compile to check all your code is valid, then save the AddIn. Close and re-open Excel. Have a play around with the Button (note it is enabled when any Sheet named 'Sheet1' is active in any Workbook and not active when a Sheet of any other name is active).
Dynamic ComboBox in an AddIn
Replace the AddIn's entire RibbonX with that from the 'So how about that dynamic ComboBox then?' section of the Making Ribbon controls dynamic post. If you didn't already, you might want to read through the explanation of the RibbonX which is in that post immediately before the RibbonX itself.
And then this is the new contents of the RibbonCallbacks Module, updated to work with the ActiveWorkbook (when opening Excel, just click OK to all the 'Cannot run the macro …' dialogs).
You need to have the xlApp_SheetActivate() event handler in the CApp Module, just as it was before. And, as before, because mRibbon will have lost state (because you have made Module-level changes), you need to compile, save the AddIn and then close and re-open Excel before this will work.
To test everything, open (or create) a Workbook that has two or more Sheets (the ComboBox is only enabled when the active Workbook has two or more Sheets). Use the ComboBox to move between Sheets. Then move between Sheets using the usual tabs at the bottom of the Excel window and note that the ComboBox updates. Then open (or create) another Workbook with different Sheet names and note that the options available in the ComboBox (when it is in its 'dropped down' state) are the Sheet names of whichever Workbook is active.
What next
I reckon the best way to learn is to have a go.
Try to think of how you can change the functionality or add a new feature and give it a go. Sure, you might break things before you get everything right … that's what testing is for!
Comments