First off, an apology. In trying to find the best way to post code, I’ve used a couple of different methods in my various posts, and if you’re trying to utilize the code, it can be a bit disjointed. So I apologize for that. I’m still trying to find the best way to post code on WordPress.com, since the “” tags work like dogcrap. I think I finally landed on the best way, which I used in this post. If it works well (and easily), I’ll use it going forward.

That said, here’s an interesting one.

I was playing around with some code in Microsoft Project. I’m in the Project Management Office at work, so I have reason to deal with MS Project on a fairly regular basis.

While I’m no means an expert on MS Project, I did find the need recently to do a bit of a proof of concept with it. Specifically, I was looking to see how easy it would be to add a small amount of code that would be used to save a project plan to a central network location. In essence, a submission of that plan to a centralize place for PMO consumption.

The code to save to project plan was easy. And so to, apparently, was the code to add a new tab and button to the MS Project ribbon.

Below is the code I used, which was lifted largely from the following site: http://msdn.microsoft.com/en-us/library/office/ee767705(v=office.14).aspx

Sub AddPMORibbon()
' Description:  Adds a button to submit the project to the PMO.
    
    ' Variable declarations.
    Dim ribbonXml As String
    
    ' Build the ribbon XML string.
    ribbonXml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
    ribbonXml = ribbonXml + "  <mso:ribbon>"
    ribbonXml = ribbonXml + "    <mso:qat/>"
    ribbonXml = ribbonXml + "    <mso:tabs>"
    ribbonXml = ribbonXml + "      <mso:tab id=""pmoTab"" label=""PMO"">"
    ribbonXml = ribbonXml + "        <mso:group id=""pmoGroup"" label=""PMO Actions"" autoScale=""true"">"
    ribbonXml = ribbonXml + "          <mso:button id=""pmoSubmitProjectPlan"" label=""Submit Project Plan"" "
    ribbonXml = ribbonXml + "imageMso=""ImexRunExport"" size=""large"" onAction=""btnSubmitPlanToPMO_Callback""/>"
    ribbonXml = ribbonXml + "        </mso:group>"
    ribbonXml = ribbonXml + "      </mso:tab>"
    ribbonXml = ribbonXml + "    </mso:tabs>"
    ribbonXml = ribbonXml + "  </mso:ribbon>"
    ribbonXml = ribbonXml + "</mso:customUI>"
    
    ' Build the ribbon UI.
    ActiveProject.SetCustomUI (ribbonXml)
    
End Sub

When I started playing with this, the very first thing that jumped out at me was the SetCustomUI property. What on earth was that deliciousness?

This is from the Office Dev Center webpage for MS Project:

Sets the internal XML value for a custom ribbon user interface of the project.

Oh, that is so beautiful. Using a string variable, you can set and customize the ribbon using VBA. Or, let me put it another way: I didn’t have to use the damn CustomUI Editor in order to create a custom ribbon.

Why is this available in MS Project? More to the point, why is this NOT available in Excel? Excel, which has to be the most heavily programmed and customized of the Office products, does not have this feature. Whiskey tango foxtrot?

Before this becomes more of a rant than I intended, let’s stay focused on the point of this post, which is to point out and show how to correct an error in the code. Because if you lift the sample code right from the page from where I pulled it, you may encounter an error.

I say may, because If you copy and paste it directly as it is, it will work. If however, you start to program a little on your own (for example, as I have below), you just might get the error that follows.

Option Explicit
Option Private Module

Sub btnSubmitPlanToPMO_Callback()
    SubmitPlanToPMO
End Sub

I won’t lie, part of me, the really cynical part, thought “Good. If Excel can’t have it, no one should.” But that’s a lonely way to live, so I started hunting down causes. Turns out that the issue lies in the accessibility of the module housing the callback code. If the module is set to Option Private Module as it is above, you get that error. If you comment it out or remove it, such as below, it works.

Option Explicit
'Option Private Module

Public Sub btnSubmitPlanToPMO_Callback()
    SubmitPlanToPMO
End Sub

The trick, apparently, is not to hide the code from the users. The example on the webpage does not have Option Private Module in the code sample, which is why I say it may work. But that moment you go off and code something and try to secure it from view from the user, you’re hit with the error.

I have no idea way this works the way it does, as I’m not privy to, nor would I understand, the base code of MS Project itself. Quite frankly, I don’t really care why this small change fixes it, as I don’t do nearly enough coding in MS Project for it to make a difference to me. I do find it annoying, but not nearly as annoying as the fact that SetCustomUI is not a native part of Excel.