The Ken Puls (Excelguru) Blog

If you are working with RibbonX code in Microsoft Office, you no doubt use the CustomUI editor from OpenXML Developer.

The tool has is an essential one for working with RibbonX and Office as it offers us the following advantages:

• Much easier than manual adjustments to the Office file
• A button to validate the XML for correctness
• An easy way to import and rename pictures
• A tool to generate callbacks with the correct syntax
• Ability to generate sample XML code to start from

I will honestly say that I will not try to attempt to modify RibbonX code without it.

There is one thing that you need to be aware of, however, and that is the danger of overwriting your file by accident.

To modify your XML markup, you open your file in the CustomUI tool, edit it, and save it.  If you’re anything like me, though, you then open it up in your application without closing the CustomUI file.  Why?  Because you have to ensure your controls are correctly referenced in VBA code and the spelling is important.

The danger of this is making updates to your XML.  If you’ve already added a ton of VBA code, saved the file (Excel workbook, Word doc, etc…) and exited it, well… no big deal.  The issue comes in if you edit the XML in the document that still sits open in your CustomUI editor, then save it.  *Poof!*  There goes the code you programmed.

It’s like a copy of the file is opened in the CustomUI tool.  You can’t overwrite the file until it’s closed, but when you do, it’s overwritten with the copy that is open in the CustomUI tool, not updated.

So, rule of thumb, if you are keeping the XML open to review, is to close the source file, then close and re-open the file showing in the CustomUI editor.

Hopefully it saves someone from re-writing a bunch of code. 

As mentioned in Part 5 of this series, this post will display how to change the value of a Ribbon control from a VBA procedure. For this example, we will continue to work with the case presented in Part 5, using a checkbox.

Essential Background
Basically, the application of the RibbonX code works like this:

• We create our XML markup via the CustomUI editor or manually
• We open the Excel file
• The RibbonX code is executed, and the Ribbon item is created

If Macros are enabled, then the following will occur next:

• Any Workbook_Open macros are executed (providing macros are enabled)
• Any OnLoad macros (specified in the XML code) are executed (providing macros are enabled)

When a Ribbon item is first presented (the first time your controls are shown), RibbonX will also launch “callbacks” to check what values your controls will have.

To effectively work with RibbonX, one of the first things we need to understand is that RibbonX does not work the same way that VBA collections do. There is no collection of RibbonX items, so we need to store all of our values in our own code. To do this, my preference is to create workbook properties and store my values there. This approach offers the benefit that we can easily refer to our RibbonX items, as well as makes it obvious which workbook we’re dealing with.

Modifying the XML Markup

Before we create a custom property to hold our defaults, we might as well get the XML Markup adjustments out of the way. We’re going to add two things to our XML for this example; and OnLoad statement, and a GetPressed statement. The use of these will become apparent later, so for now, change the XML of your file to read:

Creating the Workbook Properties

We are going to create two new workbook properties for both reading and writing. The first will hold the default state of our checkbox. The second will hold our RibbonUI object so that we can refer to it later. All the following code will go in the ThisWorkbook module of the project:

Integrating the Checkbox Value Property
In addition to the properties we defined, we’ll need one more piece in the ThisWorkbook module, and that is the code to actually set the default value when we open the workbook. Copy the following procedure below the rest in the ThisWorkbook module:

If you have continued on from Part 5, then you already have the “CallControl” procedure in a standard module. We’ll need to add a line to it to store the toggled value in our custom workbook property, so adjust your code to match the following:

Integrating the RibbonUI Property
Now, as I covered in the introduction, when we open the workbook, our Ribbon items will be created, and the Workbook_Open routine will fire, setting the value of the checkbox to True. How do we get the checkbox to actually return this value, though?

This is where the “OnLoad” procedure that we specified in the XML comes in. It forms the stepping stone to being able to do this. If you recall, I stated that there was no Ribbon collection and we’d have to hold our own objects if we wanted to use them. This is the exact purpose of the OnLoad procedure.

Copy the following code into the Standard module which holds the “CallControl” procedure:

What this code does is feed the RibbonUI object into our workbook property, allowing us to “hold onto” it for later use.  We know have a way to access our Ribbon object!

“Great!”, you say, “But how?”

Before we go there, we want to do just one more thing.  We are going to set up one more routine to feed information back to the RibbonUI.  Remember that we added a “GetPressed” specification to the XML?  This also needs to call a procedure, (again in the standard module,) which looks like this:

The purpose of this routine is to feed the checkbox value back to the RibbonUI so that it can be updated to reflect the value we have on hand for it.  It can be initiated in a couple of different ways.  The first time the control is presented, this routine will be called to return the default value.  Also, we can invalidate the XML, which will trigger this routine as the Ribbon is “rebuilt”.  The first happens naturally after the Ribbon is loaded, but it’s the second that we’re interested in, as it gives us the ability to rebuild the Ribbon control when we want to change the value.

Updating the Ribbon Control’s Value via VBA

And here it is, at long last.  A very simple routine to do exactly that.  We’re going to pretend that we have a lot more code, and a real reason to do this.    At some point, we decide that we need to toggle the value of the checkbox and make it reflect in the menu.  This routine works by toggling the checkbox property and invalidating the XML for the menu item, which forces the “GetPressed” method to be called and rebuild the menu:

Final Notes

The “CallControl” routine here is only to demonstrate the use of the property for toggling the value of the checkbox property when manually clicked.  The “ToggleChkBox1″ routine demonstrates the ability to change the Ribbon display via VBA code alone.

If you are curious about the order that things fire, either step through the code, or put a “Stop” command at the beginning of the following routines:

• Workbook_Open
• OnLoad
• GetPressed

That should give you a fairly good feel for the order things go in. 

Previous posts on this subject:

• -Part 1
• -Part 2
• -Part 3
• -Part 4

Creating a CheckBox

Until now, all the posts have focussed on creating buttons in a variety of styles, shapes and sizes. Today, we’ll take a look at a simple example of a checkbox. What we’ll do is create a single checkbox on a custom tab.

The XML to create a checkbox is once again similar to what we’ve seen. One big difference is that we cannot use a picture, of course, since the actual check box will take it’s place. The XML for our test is as follows:

This will create a custom tab called “XLG”, and on it will be the lonely control as shown below:

Now, the checkbox is there, but we need to be able to figure out if it is clicked. If you recall Part 1 of this series, we set up a routine called “CallControl” (specified in our XML), that started like this:

To determine the value of the checkbox, however, we need a slightly expanded VBA routine. We still use the basic framework as above, but we expand it to also pick up the “Pressed” portion as shown below:

Again, the code above goes in a standard module. Once you have it there, try clicking the checkbox on the Ribbon, and notice that it will feed back if it is checked or not.

This example is educational only, of course. In a real setting you would replace the message boxes with code that you would wish to execute based on the user’s selection.

In the next part, we will look at how to toggle the values of the checkbox via code, and have it update the Ribbon automatically… stay tuned!

Previous posts on this subject:

• -Part 1
• -Part 2
• -Part 3

Using Your Own Picture on a Button

Well… maybe not your own picture… unless you’re feeling vain today. I really meant using an image of your choice. This is actually very easy, provided that you use the CustomUI Editor tool from OpenXML Developer that I blogged about in Part 1 of this series. Here’s what you need to do…

First, open up your file in the CustomUI editor tool. Click the picture button (Insert Icons), browse to your picture and click OK. You’ll now see your picture on the right side of the CustomUI editor, just like in the screen capture below:

If the image does not show up there, then you’ll need to try again, as the pane does not show unless an image is attached.

Now, if you end up with a really long and weird looking file name, don’t despair. You can rename the image to something more usable simply by right clicking it and choosing Change ID. Since the CustomUI editor replaces spaces in file names with the string “_x0020_”, you may want to take advantage of this.

The next step is to edit your XML to refer to the custom picture. If you are using the “imageMso=” tags in your XML, change that to simply read “image=”. So for the example above, you would enter image=”logo”. Again, remember that this will be case sensitive.

For the following example, I downloaded my website logo, imported it my file via the CustomUI editor, and used the following XML to create my menu:

The result of this markup gave me a custom tab named “XLG”, with the following on it:

A quick notes on custom images…

You can import more than one custom image into a file, but there seems to be a size limit to how many images you can store. I tried importing a 928KB image in addition to the 57KB logo file. The result was that neither button showed and image at all. I did import 3 images into a group, but again, I added a large image file and all the button images disappeared. Despite the fact that images are automatically scaled, I would suggest that full pictures are probably not the most appropriate images (even my website logo is too big.)

Here’s the previous parts to this:

• -Part 1
• -Part 2

Using Excel’s Images on Custom Ribbon Controls

In Part 2, we built the following custom group on a custom tab:

This time, we’ll replace all the happy face images with pictures from Excel’s native ribbon images. Once you know how, it’s actually relatively easy to state which image you want. You just replace the HappyFace portion of the imageMso tag with the name of the Excel control that we want to use. The first question that we’ll obviously need to settle, however, is “How do we get the name of the control?”

What you need to do is go into the Excel Options screen, (click the Office button, then Excel Options,) and click Customize. You’ll now see the list of all the items that you can add to your Quick Access Toolbar (QAT). If you’re a developer in prior versions of Excel, you’ll remember that you pretty much had to create a custom toolbar to work out what FaceID corresponded to what picture. The good news here, is that things are much easier.

Move your mouse over one of the menu items and hover it there. A screen tip will pop up, and you’ll find the MSO image name at the end of the string! A picture is shown below of the tool tip.

What I’ve done below is grab a few random imageMso’s and put them into the XML for the example we created at the end of part 2. (Reproduced above.) The group now looks like this:

The images were selected based only on the fact that I thought they looked interesting. That’s all. Remember that these images will only do what you program them too, as shown in Part 1 of this series.

The XML code to create this group is shown below. One final word of note here… the imageMso tag is again Case Sensitive! If you mess up the case, you get no image at all on your control. The ribbon does at least get created though, which is better than the case sensitivity issues that I told you about in Part 2.

And here it is… the first snowfall of the winter in Nanaimo. This is pretty unusual for us, as we don’t typically see our snow until early in the new year. Valentine’s Day seems to usually see snow, with our heaviest concentration coming in March. Being that Nanaimo is a coastal town, snow is a rare occurrence, so to get accumulations like we’d just had is really something… er… special.

So how much? I know my friends in Sweden and other parts of Canada will laugh at this, but we’ve had seven inches in the last 24 hours. For any of you who have not driven in coastal snow, it is extremely wet, and very slippery. Not at all like the dry powdery (and relatively sticky) stuff you get inland. This picture shows the ruler stuck in the snow on my deck railing (from four and a half hours ago).

The ironic thing about this? Yesterday morning my wife and I were at Home Depot looking at snow shovels. They had this “ergonomic” one, with a bent handle, and we didn’t buy it because it’s the same model that broke on us last year. (The bent handle just folded in half on us.) They had another one, too, which looked like a big scoop, but it didn’t have a metal strip, so we passed on that one too. We walked out of the store with no snow shovel. It started snowing like crazy about two hours later, and hasn’t stopped!

Home Depot now has no snow shovels left at all. We’ve been talking about buying one for a month now, and just never got there.

Fortunately, our neighbor lent us there two shovels, one of which is the exact scoop design we passed on. They worked great, and the driveway is now clear… until we get the rest of the snow that is forecast to come tonight.

Now, this wouldn’t be a technology blog of any merit if I didn’t share some techy stuff here too. So if you are not tech inclined, stop reading now. If you are, then have a good laugh at my expense.

As is natural to happen when we have any kind of natural weather phenomena around here, the power went out at work. Who cares, right? I do. Being the guy responsible for our network, I have to drive in and restart the servers when things go down.

Oh, I can hear you now. “Ken! There is power management software to do that for you, you know!”. Oh, I know. I even have it! My servers are smart enough to shut themselves down on their own, and they even text message my mobile phone to tell me they’ve done it. In fact, they even text message me when the power comes back on to tell me that they are rebooting.

Unfortunately, we have our data stored on a SAN device (Storage Area Network), that attaches to our file servers by direct SCSI. (I tried iSCSI at one point, but the device I got wasn’t compatible with Novell at that time.) The issue, according to one of my external consultants, is that the SAN device does not initialize before the Novell servers try to contact the SCSI connection, and therefore the SAN never mounts. Basically, this means that we have no access to any of our data until I drive to work, flip the rocker switches on the SAN to off, shut down the servers, turn the SAN back on, let it fully initialize, then boot up the servers.

Honestly, it’s no big deal, and it takes me about an hour, all told, to drive to work, reboot everything, and drive home. The frustrating thing is that, as Murphy would have it, the calls always come at 2:00AM, on the weekend, or at any time that it is inconvenient. Further, the reason the power usually goes out is that is that we’ve had big storms. The last kind of weather that you want to go out in. In fact, I’ve had to journey out three times in the last month, through record rains (and huge puddles on the roads,) to get in there and clean things up.

Only now it snowed. The radio is telling people to stay off the roads as they are a mess. And now I have to go in to deal with the servers. To make matters even worse, my car would not even have made it to the resort, as there was over a foot of snow in some areas. Fortunately though, my boss is a super nice guy. He has a nice 4×4 truck and offered to give me a ride out to the resort.

Pockets of the town were powerless, many traffic lights had been turned into four way stops, cars in the ditch… it was ugly out there. But we did it, and we’re both home safely now, with the resort back up and running.

Now, the ironic thing about the whole power management thing is that we have tests scheduled, and have since a month ago, to run the servers through a full power outage on Tuesday morning. There is a time controlled power bar that I can buy, which will allow you to program how long each port starts up after power is restored. So the purpose is to shut everything down, and simulate using this power bar. Starting with the SAN, we’ll reboot the system in the order we know works. I do have some reasons to be skeptical here, but we’re going to try it out. If my system tech wins the bet, and it works, then I’ll be a happy guy. If not, I’ll blog about why.

Here’s to hoping that it works, and that I don’t have to put my life at risk to reboot a few servers!

I found this… weird…? Intrusive? I’m not really sure…

I was surfing around some forums for the last hour, and suddenly a little message box popped up letting me know that my system was activating Windows (Vista.) Now don’t get me wrong, my copy is legal, so there is nothing to be concerned about there. In fact, the only reason it wasn’t activated during the install phase is that my wireless router is secured, and Windows couldn’t access it at that point. I’d actually even forgotten that I hadn’t activated it yet.

What kind of bugs me, though, is that I wasn’t even asked. Here I am, just surfing around, and up pops the dialog. “Activating Windows”, followed shortly by a nice little message to say that it was successful. But what if I planned to blow the entire system away and rebuild it tomorrow, so didn’t want to activate it? I’ve only had this installation running for 3 days!

Okay, maybe I’m just being grumpy about this, but I’m sure some privacy expert could come up with a valid reason why I should be concerned about this. Again, if asked I would have done it. But to have it forced on my after only three days? To me, it just felt wrong.

Part 1 of this series can be found here.

In this post, I’m going to show a few more examples of customizing the ribbon using buttons. (We’ll get to other types of controls later.) All of these are variations of the schema set up in the first post linked to above.

Three Small Buttons to a Column

In the example from our previous section, we saw how to add three new buttons to a group in the ribbon. The following XML adds a forth button, (no major excitement there,) but it does show how to start a second column in the group. (Similar to text wrapping.)

The end result looks like this:

Adding the Group to an Existing Tab

This time, we’ll add the the group to an existing tab. We’ll go back to the three button scenario, and put it right next to the clipboard on the Home tab. (I’m not suggesting you put your items there, as this is only an example.) So here’s the code to do exactly that:

Notice that the XML to create the tab only declares the tab idMso = “TabHome”, rather than going through whole CustomTab setup portion. In addition, though we do need to specify where the new group will go, so we need to add the insertAfterMso=”GroupClipboard”

And by the way, just to make this more fun, the XML markup is case sensitive.

The net effect of the above XML is the following:

Adding Big Buttons

Now we’ll go back to the original example from the first post again. This time, however, we’ll add another custom group, with three large buttons on it. (This is in addition to the original group.)

The code:

The only real difference between the XML used to create the button is the inclusion of size=”large” in the second group. Without that, the buttons end up being small and stacked on top of each other, as in our first example. With the size clause included, they are bigger and placed horizontally in the group, as shown below:

Mixing Button Sizes

The final example of this article will display mixing the large and small buttons. This time, all the buttons are on Custom Group 1 of the Custom Tab. I’ve mixed up order though, and dropped Button 3, just to show how the mixed sizes will display.

The output from this XML is shown below:

Now this is kind of cool…

In Excel 2003, when you opened the brackets on a formula, it would launch a little help feature as shown below:

In Excel 2007, they go one step further. As soon as you hit the = sign, it starts using Intellisense to figure out which formula you might be after, displaying a list that narrows down as you type. What I find really cool is that this works for UDF’s as well as native formulas.
To demonstrate, I added the following code to a standard module…

… and then went to the UI to enter the formula. Check out the Intellisense!

Now, while it still shows the arguments for native formulas as it did in 2003, it doesn’t show the arguments required for a UDF, which is a shame. Still though, it is kind of cool.

How’s this for interesting? The picture here shows my company’s spam volume for October 2006.

The highlights essentially say that almost 26,500 messages were sent to us, of which 69% (18,260) were tagged as spam. An additional 646 (about 2%) were tagged for using nasty words, and a 32 had banned file types (zip, bat, exe files, etc…) What really surprised me in this was that there were only 55 viruses caught out of this many emails. (And no, no network infections, either.)

Sadly, everything that I’ve read leads me to believe that 70% is about right for spam volume.

We pay a third party to scan all our email before it hits my servers, as I don’t want the bandwith consumption to affect my network performance, nor do I want to pay for the bandwidth needed to transport this junk. (While I’m very happy with the provider we use, this isn’t a sales pitch, so I’m not going to name them here. If you want to know, though, use my Contact Form to get in touch with me.)

Now just sit back and think for a moment on this volume. Assume you pay your people $10 per hour, and it takes them 5 seconds to identify and delete a spam email. That means that they can delete about 12 per minute. Based on that total volume above, we’re talking about 2,208 minutes to delete that month’s spam load. That equates to 36.81 hours, or $368.00 in lost staff wages. Even if it only takes only half that time, what did you lose in the opportunity to sell something to a customer? And this doesn’t even take into consideration the cost of bandwidth, network latency, or anything else. Actually, if you want to run your own scenarios on cost, feel free to use my Automation Evaluation Form. It should do the job.

It sure makes a good case to buy a spam filter, doesn’t it?