customUI Ribbon XML Editor

I’ve created a new addin for editing and validating Ribbon customUI XML. It’s a form that lets you modify ribbons in workbook and addins. Here’s a couple of screenshots:

form with highlighted error

form_with tips

YouTube Video and customUI Ribbon XML Editor

Here’s a YouTube video! yoursumbuddy’s first!

Here’s the link to the yoursumbuddy customUI Ribbon Editor page, where you can download it and read more.

Please let me know what you think.

MenuRighter Update Finished

A year or so ago I posted about updating MenuRighter, my free add-in for customizing Excel’s right-click menus. It’s done! You can go to the MenuRighter page for a download link and instructions. Or read on for some examples of what MenuRighter can do.

MenuRigher Customization Examples
Below I’ve modified the Row menu, with the default Excel 2016 version on the left and the modified version on the right. Copy, Cut, Paste and a few other buttons were removed and Freeze Panes is added to the top:

MenuRighter sample Row before

Post_0099_MenuRighter_sample_Row_after

Here’s the modified Row menu in use. Note that the button caption changes in context from “Freeze” to “Unfreeze”:

MenuRighter Row modification in action

Modified Cell Menu
Here’s my modified Cell right-click menu. It’s barely recognizable, so let me restate that this is the menu you get when you right-click in a cell. I’ve again deleted the Cut, Copy, Paste, Clear Contents and several other buttons. At the top I’ve added the menu for my personal addin, cleverly named “myBar.” Just one click added the whole structure to the Cell menu:

MenuRighter sample Cell

You can see that I’ve also added a whole bunch of filtering buttons. Two of the built-in ones are pulled up a level from the Cell>Filter menu, like “Filter by Selected Cell’s Value.” There’s also a couple for my own routines, like “Filter by All Selected Values.”

“Autofilter” is there so I can toggle filtering for whatever Table or range I’m in. (If you’re looking for this one, one its locations is Worksheet Menu Bar > Data > Filter > AutoFilter).

Two Other Examples

Here I’ve added a couple of my routines to the Ply menu. That’s the one you get by right-clicking a sheet tab:
MenuRighter sample Ply

And here’s the entire File menu added to the Cell menu. Everything at your fingertips!

Cell with File

Just kidding. I’d never do that.

New MenuRighter Page

Again, here’s the page with the download link and instructions for the new version. Please let me know what you think if you try it.

Missing Addin User Warning

I develop workbooks that require an addin for some of their functionality. For example, a set of school staffing models whose addin allows for selecting data sources, building pivot tables and saving timestamped copies. I like this structure because it’s very maintainable – it’s much easier to fix a bug or add a feature in one addin than it is to try to track down a bunch of workbooks and update their VBA. Easier on me and better for my users. Of course, people’s computers get re-imaged or replaced and IT departments are unlikely, to say the least, to re-install a VBA addin. Folks often don’t realize their addins are gone and that’s why their model is broken. Yesterday I got an idea about how to warn users about a missing addin. I’m still tinkering with it, but the idea is quite simple.

Use a UDF in the Addin

What I did was create the world’s simplest UDF and put it in the addin:

Public Function IsAddinLoaded() As Boolean
IsAddinLoaded = True
End Function

It doesn’t have to be boolean. It could be a string and return “Cult of the Flying Spaghetti.” That’s because it’s only purpose is to fail when it’s not there.

Missing Addin = Missing UDF = opportunity for IsError formula. So, for example, in cell A1 of the addin-dependent workbook put something like:

="Ye Olde Spreadsheet " & IF(ISERROR(isaddinloaded()*TODAY()),"requires the Green Eyeshade Addin!
Please install it.","is Good to Go")

Then add a little conditional formatting based on the missing addin condition and you get something like this. (I tried referring to the UDF directly in the conditional formatting, but it looks like that’s not allowed.)

Green Eyeshade required

The formula contains a volatile function: TODAY. That’s so if the addin is uninstalled the error is activated with the next change to the worksheet. I’d like to avoid the volatile function and I’ve tried to do this in the VBA itself, but haven’t figured out anything yet.

The message does change to the non-warning one as soon as the addin is installed.

Green Eyeshade installed

The addin a very simple one made up just for this post. It’s called Green Eyeshade and it’s for modernizing old spreadsheets like this one. Its ribbon tab is shown when a workbook meeting certain criteria is opened. Here’s the ribbon tab:

Green Eyeshade tab

And here’s the modernization button in action (wonderful what you can find by googling “Peltier pie charts”).

Green Eyeshade modernized

This idea could also be applied to the problem of warning users to enable VBA when opening xlsm files. I’ve never been a big fan of the “hide everything in the workbook except a warning page” although I do like Mike Alexander’s clear explanation. Anyways, something like this might work as an alternative in that case too.

Download

Here’s a zip file with the xlam addin and the target “olde spreadsheet” xlsx file.

Prompt to Save Addins

I’m pretty good about saving my work, and probably hit Ctrl-S a couple hundred times a day. And of course, as long as things don’t crash, Excel makes it hard to lose your work. One exception is addins, which don’t trigger a save prompt when you close them after making changes, at least when the IsAddin property is True. So I have a routine in my most-used addins that reminds me to save them. But I don’t have it in all of them. The other day this bit me, and I lost 10 minutes of work on an xlam. I decided to generalize my prompt to save addins and put it in an application-level event in my main utility addin. (These decisions come easy; what’s more fun than building a new tool?) This way I’m prompted to save any time I close an addin that I’ve changed.

If you’ve never used application-level events, Chip Pearson’s site has some good information. Okay, here’s how you can add this code to your favorite utility addin (personal.xls will do nicely).

Create a Class called “clsApplication” and paste this code into it:

Public WithEvents app As Excel.Application

Private Sub App_WorkbookBeforeClose(ByVal wb As Workbook, Cancel As Boolean)
If wb.IsAddin And Not wb.Saved Then
    If MsgBox(wb.Name & "Addin" & vbCrLf & "is unsaved. Save?", _
              vbExclamation + vbYesNo, "Unsaved Addin") = vbYes Then
        If ExcelInstanceCount > 1 Then
            MsgBox "More than one Excel instance running." & vbCrLf & _
               "Save cancelled", _
                vbInformation, "Sorry"
           Exit Sub
        Else
            wb.Save
        End If
    End If
End If
End Sub

Create a global variable to hold the class instance. At the top of a regular code module (before any procedures) put this line:

Public cApplication As clsApplication

(I like to put all my global variables like the one above in a single module, called modGlobals.)

In the ThisWorkbook WorkbookOpen event for your utility addin, put this code:

Set cApplication = New clsApplication
Set cApplication.app = Excel.Application

One problem is that when an addin is saved with more than one instance of Excel open, it gets saved to a new location (maybe the folder of ActiveWorkbook?). So I added code to the BeforeClose event to cancel the save if that’s true. Here’s the function that does the checking:

Function GetExcelInstanceCount() As Long
Dim hwnd As Long
Dim i As Long
Do
    hwnd = FindWindowEx(0&, hwnd, "XLMAIN", vbNullString)
    i = i + 1
Loop Until hwnd = 0
GetExcelInstanceCount = i - 1
End Function

One last thing to do is add code to my global error handler that re-instantiates the cApplication.Class and its App property if they’ve gotten lost, which can easily happen during debugging.