One Week Till Christmas

Hello everyone!

Well, it’s a week until Christmas, and our house is busy preparing for the big holiday.  I’m taking a little bit of time off over the next week, and will be back to work full bore on December 27th as we prepare to complete the year end.

So far we’ve got the tree up and the house lit, although we actually had that done a while ago.  The shopping is mostly done, and the baking is beginning. :)

We’re lucky here in that both my wife’s and my family get along really well, so we’re continuing our tradition again here.  My parents and brother come over to the Island to stay with us, and we gather both families together at our house for dinner on Christmas eve.  My wife’s parents live 5 minutes from us.)

Christmas morning is again held at our house, with my inlaws (mother, father, brother and his wife,) all coming over to open presents.  A pretty good time is had by all.

Finally, we head over to my inlaws house for Christmas dinner (including my parents).  We’re joined there by great grandmother and an aunt as well.  (I think that there will be 13 of us in total this year.)  There’ll be a great big turkey dinner with the usual fixings, as well as a great deal of wine consumed there, no doubt.  We tend to enjoy family gatherings.  ;)

The sad thing is that due to the preparations, I’m not going to have as much time to post on the blog as I’d like.  I am working on some more Ribbon examples, even a real tutorial, but the posts may be pretty sporadic over the next week.

Happy holidays, everyone! :D
Ken

Modifying the Ribbon – Part 11

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

For this installment, I thought I would focus on exploring the button control in depth, explaining what each piece of XML does when building the ribbon control, and what types of values are permitted for each one. In a follow-up article, we’ll discuss how to make these controls dynamic, but for now we’ll just focus on a very static button in a custom group.

Beginning With Static Items

Whenever we are programming, it is helpful to understand what pieces of code are assigned to what properties of the object. The following is a list of all of the components that we can set for a button, and where they show up:

Base XML Markup

The framework for the XML Markup is fairly static, although we’ll explore some minor modifications to it in the next section. For most purposes, however, it starts with a declaration of the schema, opening your Ribbon object, referring to (or creating) a tab, and referring to (or creating) a group. Within the group tag is where we will place all of our button (or other) controls. The XML below creates a new tab called “XLG” after the “View” tab, adds a group called “My Testing Tab” and puts the button there.

Rather than explain all the XML at this point, be aware that the code to create any control within the group should be surrounded by the open and close tags: “<" and "/>”

For our purposes, I have constructed the base XML we’ll use, which should be added to an Excel file by using the CustomUI editor. I would suggest creating, saving and closing a new file for this (make it an xlsm file). Once done, open it in the CustomUI editor and paste in the following XML:

  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon startFromScratch="false">
  3. <tabs>
  4. <tab id = "xlgTab" label="XLG" insertAfterMso="TabView" keytip="x">
  5. <group id="TestTab" label="My Testing Tab">
  6. <button id="button1" description="my custom description" enabled="true" imageMso="FilePrint" label="my custom button label"  keytip="zcb" onAction="rbnButton_Click" showImage="true" showLabel = "true" screentip="my custom screentip" supertip="my custom supertip" visible = "true" />
  7. </group>
  8. </tab>
  9. </tabs>
  10. </ribbon>
  11. </customUI>

The above code will be used for all demonstrations in this article. Once saved, the following group will show on the XLG ribbon tab:

buttonlarge.jpg

Reviewing the button code and properties

We’re going to begin our dissection by looking at the line that begins with the “button id” portion of the XML.

Remember! All of the XML markup is case sensitive!

“id” Tags

You must have an ID tag associated with your control, as this is how the application keeps track of what controls have what properties. You won’t see this on the ribbon, as it is a “behind the scenes” feature.

You may use any one of the following for your ID:

  • id
    • The XML form is id=”name of id”
    • The name of the id must be unique for each id added
    • Allows creation of custom buttons and controls
  • idMso
    • The XML form is idMso=”id of control”
    • The name of the id must match a valid office control name
    • Allows “repurposing” of a control (replacing aspects of an existing control with your own intentions
  • idQ
    • The XML form is idQ=”id of control”
    • Used for working within defined namespaces (and is out of the context of this article.)

For the purposes of this article, I have used the “id” tag, and assigned it an id of “button1″: id=”button1″

The “description” Tag

The description tag is not publicly shown on the ribbon, and is an optional control with no default value. In our case, I assigned a value to this property with the following piece of XML: description=”my custom description”

The “enabled” Tag

The enabled tag specifies if the control is active or not. It is specified with the following XML: enabled=”true”

A control specified with enabled = “false” will not trigger anything when clicked, and will be grayed out, as shown below:

Enabled=False

This property is optional, and will revert to a default value of “true” if not specified.

“image” Tags

When inserting images, you have the choice of specifying one of the following:

  • image
    • The XML form is image=”my image name”
    • The image tag allows you to refer to your own images that have been included in the file package
  • imageMso
    • The XML form is imageMso=”MS Office image id”
    • The imageMSO tag allows you to specify Microsoft Office images to be used on the ribbon
    • To get the image id, go into Office|Excel Options|Customize, and let your mouse hover over the names in the list. At the end you’ll see the id within brackets, as shown below:

getimagemso.jpg

For the purposes of this example, I’ve specified to use the built in Printer icon, by supplying the following XML: imageMso=”FilePrint”

The image tag is completely optional, and will not show an image if you ignore it. If you omitted any type of image tag, (leaving the rest of our XML as is,) you would end up with a button like this:

showimagefalselarge.jpg

The “label” Tag

The Label tag is used to assign a caption to a button, and is an optional tag. If you leave it blank, you will see no caption with your button at all. In our case, I assigned a value to this property with the following piece of XML: label=”my custom button label”

This piece of XML manifests itself on the ribbon as shown below:

label1.jpg

The “keytip” Tag

The Keytip tag’s purpose is to allow you to access your ribbon control via keystrokes. If you read the XML carefully, you’ll see that I’ve assigned a keytip of “x” to the XLG tab, and the following portion assigns a keytip to the actual button: keytip=”zcb”.

If you press Alt+x, you’ll see the keytip show up on the button as follows:

keytip.jpg

According to the FAQ’s in Microsoft’s Customizing the Office (2007) Ribbon User Interface for Developers (Part 3 of 3) article, they suggest that you start all keytips with the “Z” character. In my experience, however, leading with an uppercase Z, (followed by whatever characters,) will yield you a keytip of “Y”.

The “onAction” Tag

onAction is the property that specifies what code routine is run when the button is clicked. You will need to program the appropriate code for the routine yourself, and specify the name of that routine here. As there are examples of how to deal with this aspect on this blog, I will not cover it in much more detail at this point, except to say that the routine I have chosen for the example is called “rbnButton_Click”, and is specified with the XML of onAction=”rbnButton_Click”.

It should be noted that you can designate a single routine as a central handler for all of your buttons, and take appropriate action by testing the id, or you can program a separate routine for each individual button. One thing that should not be done, however, is to try and handle controls of different types through a single onAction handler. Many controls require slightly different signatures, so they could fail if you try to go that route.

The signature for a button’s onAction control in VBA is as follows:

  1. Private Sub rbnButton_Click(control As IRibbonControl)
  2.  
  3. 'Code here
  4.  
  5. End Sub

I would also suggest that it is a “Best Practice” to ensure that all onAction routines are specified as Private, and placed in a standard module.

The “showImage” Tag

This tag makes the image visible or hidden, and is specified as follows: showImage=”true”.

The showImage tag can accept values of “true” or “false” only, and will default to true if not specified.

Interestingly, and I’m not sure if this is a bug or not, my tests show that this setting only seems to apply to a button if the size is set to “normal”. I.e. “Large” images will show even if the setting is specified as false. What it should look like is as follows:

showimagefalselarge.jpg

The picture above was generated by not specifying an image or imageMso tag at all. So if you really want a large picture-less button, it looks like that is the only route for now.

For reference, the showImage tag does work correctly with a “normal” sized button, and the picture below illustrates that:

showimagefalse.jpg

The “showLabel” Tag

This tag shows or hides the button label, and uses the following XML to do so: showLabel=”true”

ShowLabel is an optional tag as well, with a default value of true.

Strangely, this tag shows the same idiosyncrasies as the showImage tag above. Specifying false in our XML should yield the button below, but for some reason this picture had to be generated by removing the label=”my custom button label” code from the XML.

showlabellarge.jpg

Again, for demonstration, the following would display (correctly) when setting the showLabel tag to “false” and the size to “normal”:

showlabel.jpg

The “size” Tag

The purpose of this tag is easy to guess… it sets the size of the button image. In the examples above we’ve been using a large button, which was declared with the following XML: size=”large”

The size tag is optional, and can accept values of either “normal” (small), or “large”. If it is not specified, it will default to “normal”

A picture of our button using a size of “normal” is shown below:

buttonnormal.jpg

The “screentip” tag

This tag allows you to set a screentip for your button by specifying the following XML: screentip=”my custom screentip”

Again, this is an optional tag that will leave the field blank if not specified. A picture showing where this information is displayed is shown here:

screentip.jpg

The “supertip” tag

This tag allows you to set a supertip (similar to a screentip) for your button by specifying the following XML: supertip=”my custom supertip”

Again, this is an optional tag that will leave the field blank if not specified. The following picture shows where the supertip information is displayed:
supertip.jpg
The “visible” tag

This tag sets the visibility of a button, and is specified as follows: visible=”true”

It is an optional tag which can accept the values of “true” or “false”, and will default to true if not specified.

As you might expect, setting the value of your control to false will hide it completely, as shown below:

visiblefalse.jpg

Conclusion

I hope that this has helped to explain the components of a RibbonX button and the code used to create it. I’m also hoping that the pictures to show the effects of changes were useful to display exactly what effect modifying pieces of the XML would do to the overall output.

While it is necessary to understand the basic construction of a static button, it is unlikely that most people will have their needs met by one. To that end, there will be a follow-up post to this, explaining how to make these pieces dynamic, allowing us to change them on the fly.

:)

Customer Service

Tonight we ran out to get groceries before the next big storm hit, and during the process we got a little hungry.  We headed out to one of our local fast food joints, and ordered our meals at the drive thru.

After placing our order, we drive up to the window, only to be told “The chicken will be about 8 minutes.  Do you want to wait?”  My wife hummed and hawed for about 2 seconds and said… “No.”  So we changed her order to a burger combo and a hot dog.

Now the girl at the window was young, maybe 18, and we won’t discuss her hair colour.  She had to read the order back to us 3 times before she could finally understand that we still wanted the rest of the stuff we ordered, one hotdog, (no not two, one!) and the fries.  Yes we actually wanted the fries that are supposed to come in the combo that we ordered.  We then proceded to wait for 8 minutes with cars lining up behind us, no doubt questioning what the heck was going on.

And do you know what happened after 8 minutes?  She asked us to park because the hotdog was going to be a couple of minutes.

I don’t get it.  Why would you do this?  “Sorry, but it will be 8 minutes for the chicken.  Would you rather wait 10 minutes to get your second choice?”  What a great idea!

I know for a fact that the food & beverage industry in BC, (and Alberta too for that matter,) are chronically understaffed.  Loyalty is not an issue anymore as there are so many jobs out there for the taking.  Young kids and adults are able to have their pick of what they want, as there are just so many openings.  Staff will move to a new company for 10 cents and hour more.  They won’t phone in sick if they have a hangover, as it’s easier to just find a new job than suffer the displeasure of your boss.

Trying to fix the issue of staff loyalty by offering higher pay only forces inflation to escalate as the employers start bidding to steal employees.  The problem is not rates, it’s a lack of physical bodies to fill the positions.  Why is loyalty what I’m focussing on?  Employers do not like spending money on training when the employee won’t stick around.
Given the above, how as a manager can you convince your staff to actually offer good customer service?  Obviousley the company that we visited tonight hasn’t figure it out, as there were several customer service failures in our experience, from the order process, to the re-order process to making the other customers wait while they dealt with us.

Mother Nature is Spiteful!

Now, for those of you who don’t know, I have a great deal of respect for Mother Nature. I am an avid skier, and have skiied the BC mountain backcountry more than once, where you must give her respect or you’re likely to have a front row seat at your own funeral. She and I have always got along fine, but over the last few weeks, and specifically 18 hours, I wonder if I did something to tick her off. ;)

As I start writing this, it’s 5:30AM. I’m up, showered, dressed, and have a cup of coffee in front of me. This after a restful night of sleep which started at 10:00PM. Again, for those of you who know me, you’ll be aware that this is an absolute absurdity. My prime forum and programming expermintation time is between 8:30 and 11:30 each night, after my daughter goes to bed. I let virtually nothing get in the way of that time…. except maybe my wife… she wins over the ribbon ;)

I get up at 7:00… ish… and am out the door around 7:30… ish… to head off to work. I’m NOT an early riser, nor am I a great morning person. (Please don’t give me tips on how to be one, as I have no interest in learning.) In fact, I keep the same schedule even when I’m getting up to go skiing!

So what gives? It’s Mother Nature, I’m telling you… Let me start by telling you at little about last week, and yesterday…

Over the last week it’s been a particular busy time at work, and on my home development front. At work we have just begun preparing for our membership renewal cycle, in which we bill all of our exisitng membership for their next year’s dues. The thing is that last year was the first year we used the system we’re using, so a lot of stuff was done on the fly, and the superstar that managed it is now on maternity leave. Naturally, this is a pretty large concern to us, as it incoporates a very big piece of our annual cash flow.

Being the systems guy/accountant, I am the person who knows the system second best. So the first three days of last week were spent flowcharting, meeting about, explaining and adjusting the system. And following that I had to dig into a database to pull out the existing information to send to people for review… wait… did I mention that the link to actually get data from this database was only made possible a week before? Right… so I used my considerable experience with the database (a whole week’s worth) to leverage my considerable SQL skills… wait… I only actually have rudimentary SQL skills… to create a report to show these folks what they needed.

Now, I’m not complaining here at all. It’s good to be busy, but there are limits. During this whole time, we were (actually still are) processing our month end reports. Every month end, we have five business days to create financial statements for our ownership. We’ve actually become quite good at this, and manage to pull it off most of the time. (Quarter and month ends actually have a shorter time frame, but fortunately we’re not at one of those.)

Oh… remember our snow storm? Well, it shut the resort down for about a week. You’d think that would be easy to do the accounting for, but let me tell you… Our night auditors couldn’t get in, so they couldn’t roll the hotel over to the next day for a three day period. Once that was finally done, they just did the hotel (by my direction), and left our five point of sale partitions. So now one of my staff is there trying to post them and balance for what transactions needed to be balance. And, of course, she was away during snow time as well, which is forcing her to catch up on her own work, so now she’s WAY behind…

So due to the time lost to the billing process and the snow storm, we put ownership on notice that we’d be late this month. Fortunately they were okay with it, and we started plugging along, but not really getting into the full month end swing of things until last Thursday. So if you ran that out, you’d expect statements to be done by the following Wednesday evening (tomorrow). Let’s not get carried away here now… Vancouver didn’t suffer as much as we did under the snow, so that is pushing it a bit. ;)

At any rate, here we are pushing ahead on month end. Oh yeah! And I’m doing a server upgrade this week too! This week we are virtualizing two Novell servers from our Novell cluster, as well as at least one Citrix server from our server farm. So I’m planning that with my third party techinician as well, to make sure it all goes smoothly. It looks like we’re going to be installing Ubuntu as the host OS on this server, and using VMWare server to run the Novell and Citrix server on one box. The idea here is that if it works well, we’ll move to a full VMWare ESX machine (dropping the Unbuntu interface), and have everything needed to run our systems on each server box. But regardless, the plannign is just one more added thing. ;) I should say that normally I won’t touch our systems during month end, but in this case he would be working on a server that was available for, but not in production, so we figured that it would probably be safe.

We all left work on Friday, feeling pretty good about things. The accounting side was coming along very nicely, with only about a day’s work left. The systems side was planned, and we were ready to go. There was only one thing we forgot to think about… Mother Nature doesn’t like Mondays. ;)

So along comes Monday morning. For my own part, I get up (at 7:00 ish, NOT 5:00) and carpool in to work with my boss. He tells me the weather forecast for the day: Rain warning of up to 50mm and a wind warning of 70-90 kmph winds. One of the ferry terminals is already shut down due to the wind, and they haven’t even come close to peaking yet. Great. Just flipping wonderful. We want to finish our month end, and we already know that the power goes out every time the wind blows where I work. Oh… and better yet… remember the “power bar” thing that I bought for my servers? It’s at my contractor’s place, but not installed yet. :(

So we get to work and start scrambling. Push those pencils! Hammer on those keys! Flood those printers! Let’s get this month end finished before the power goes out! Then in walks my systems guy… smacking his head as he forgot the power bar in his office. :(

If this were a novel, (well it kind of is now,) it would be the most pathetic piece of foreshadowing every written. I’m sure you see that. But regardless, when we’re in the middle of life, somehow we can’t read that…

At any rate, we conferred and he assured me that there were no real “if the power goes out here we’re dead” parts to this setup, so I let him fly at it.

So there we all are, all pushing to get our jobs done. 10:00 comes and goes… we still have power. The eaves are overflowing and it looks like I’m working behind a waterfall, but we still have power. 11:00 comes and goes… we still have power. There is a waterfall cascading down the cliff behind our office, but we still have power. 12:00 comes and goes… we still have power! There are pieces of trees flying past the windows, but we still have power! We decided to break for lunch. After lunch and more hard work, the clock rolls past 2:00 and… we still have power? Something is wrong, but we’re almost ready to print statements. Don’t look a gift horse in the mouth! Work harder! Focus turns back to computer. 3:00… I press the print button on the statements, and they start churning off the printer… my boss heads out for a meeting… the lights start to flicker every now and then. But we still have power!!!

3:30 on Monday afternoon, I have 70 pages of financial statements in my hand, still warm from the printer… but I can’t see them…

It was actually quite a scene. We have three phase power in our building, and one of the phases went out. Then it came back on… a different one went out… and came back on… and then another… it was like we were standing in the middle of a rolling blackout. Heaving a great big sigh of relief that I have printed statements in my hand, I decided to check on the IT guy.

He’s scrambling to shut down servers left and right. The power is going up and down like a yo-yo, so he’s working under strobe-light like conditions. Why is he in a panick? He’s trying to get VMWare server to install and having issues! Of course it had to happen that way, and we’d even joked about it earlier. When will the power go out? When we’re half way through statments and the server migration, right?

Then we lose power completely. The emergency lights are on, and the glow of all the PC monitors being fed by their UPS’s. Those get shut down, and now the only light there is shines through the windows at 3:45 on a very dark and rainy December afternoon. Needless to say, there isn’t much… and there is NONE in the server room, since it’s in the middle of the building. The light of the IT guy’s laptop screen glimmers faintly from there.

We quickly shut down all the servers and SAN array, and leave him with only a monitor, the server he’s working on, and his laptop running off it’s own battery. The intention, of course, is to let it complete the installation complete. Knowing the stress he’s under, I backed away to let him concentrate as much as possible until I hear him yell for help. “You got a flashlight? I can’t see the keyboard!” He was using a text based HTML browser for soemthing, which had a black background. :)

Just then my boss comes back from his meeting. It’s now about 4:15, so we sit down to review statements by flashlight… and wouldn’t you know that we need to make changes? So we bailed for home at about 4:30, as power was not forecasted to come back on until at least 11:00PM. And despite the perfect opportunity to install that power bar… it’s in the IT guy’s shop!

An exciting day, to be sure. :) But as you’re aware if you read my blog before, until I get that power bar installed, I have to go in to restart servers. :(

I had the power bar delivered to my home, so it’s ready to go in with me when the power goes back on. I called the hotel rooms supervisor, and discussed the occupancy. With only 1 guest in house tonight, we decide that it’s prudent for my to skip going in to restore the servers in the middle of the night, and I’ll do it early in the morning. If I get in there by 6:00 and get things going, night audit should still have enough time to complete their function before the guest checks out.

So I apologize for not posting a new Ribbon article last night. I’m part way through one, but there just wasn’t enough time to finish my beloved article before I went to bed. Sadly, work had to come first… So the alarm got set for 5:00AM (shudder) and off to sleep I went.

At shortly after 5:00, I stumled into the shower, which is where I tend to wake up. In there, though, I started thinking… “that’s weird”… My cell phone never rang in the night. I can’t remember if I mentioned this in my last blog post on the subject, but my UPS sends me a text message whenever an “event” occurs. (A full power off and back on cycle sends about 11 in all, and two cycles fills the memory on my phone!) I did not recall hearing that phone. Not unusual for me, maybe, as I’m a pretty heavy sleeper, but I’m sure my wife would have kicked me to shut it off, as she isn’t, necessarily. No kicks that I recall either… hmm…

I get out, I dry off, I check my phone. Nope. No messages. That can’t mean… surely the power… am I up at 5:00 for NOTHING????? grrr….

I phone the hotel. Sure enough, they still do not have power.

So there we are… Mother Nature, I’m not sure what I did to irk you, but I’m puttin in the power bar today. That’s the last time you’re going to have this much fun with me!

Modifying the Ribbon – Part 10

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

The purpose of this post is to demonstrate how to use the Ribbon to interact with our application. Specifically, this example has an edit box which will update with the worksheet name every time you change worksheets. In addition, if you change the name in the edit button, then press the “commit” button, it will change the name of the worksheet to your new selection. (Providing the new name is allowed, of course.)

XML Markup Required

Our XML for this example uses the onLoad assignment to capture our ribbonUI object, allowing us to force a rebuild of our ribbon items when needed, as well as the onAction, onChange and getText callbacks.

  1. <customUI onLoad="OnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon startFromScratch="false">
  3. <tabs>
  4. <tab id = "xlgTab" label="XLG" insertAfterMso="TabView">
  5. <group id="TestTab" label="My Testing Tab">
  6. <editBox id="editBox1" label="New Sheet Name:" onChange="rbnEditBox1_Change" getText="rbnGetText" />
  7. <button id="button1" label="Commit Name" onAction="rbnButton_Click" imageMso="HappyFace" />
  8. </group>
  9. </tab>
  10. </tabs>
  11. </ribbon>
  12. </customUI>

VBA Code Required – ThisWorkbook Module

Again, we have the need to create workbook properties to set and retrieve the values of our ribbon controls. This time, we need to store the name in the editBox, which can be applied as a worksheet name, as well as the RibbonUI object. The editBox property is first used immediately upon opening the workbook, when we assign the active worksheet’s name to it. It is then subsequently updated every time a new sheet is activated in the workbook. The code required in the ThisWorkbook module to accomplish this is shown below:

  1. Option Explicit
  2.  
  3. 'Private variables to hold state of Ribbon and Ribbon controls
  4. Private pRibbonUI As IRibbonUI
  5. Private sEditBox1Text As String
  6.  
  7. Public Property Let EditBox1Text(s As String)
  8. 'Store the button name
  9. sEditBox1Text = s
  10. End Property
  11.  
  12. Public Property Get EditBox1Text() As String
  13. 'Retrieve the button name
  14. EditBox1Text = sEditBox1Text
  15. End Property
  16.  
  17. Public Property Let ribbonUI(iRib As IRibbonUI)
  18. 'Set RibbonUI to property for later use
  19. Set pRibbonUI = iRib
  20. End Property
  21.  
  22. Public Property Get ribbonUI() As IRibbonUI
  23. 'Retrieve RibbonUI from property for use
  24. Set ribbonUI = pRibbonUI
  25. End Property
  26.  
  27. Private Sub Workbook_Open()
  28. 'Store the name of the active worksheet
  29. ThisWorkbook.EditBox1Text = ActiveSheet.Name
  30. End Sub
  31.  
  32. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  33. 'Set the name of the worksheet to the text property
  34. With ThisWorkbook
  35. .EditBox1Text = ActiveSheet.Name
  36. .ribbonUI.InvalidateControl editBox1Name
  37. End With
  38. End Sub

VBA Code Required – Standard Module

The following code contains all the callbacks necessary to:

  • Store the ribbonUI object to the Workbook property (via the onLoad routine)
  • Return the text for the editBox control when the RibbonX code requires it (rbnGetText routine)
  • React to manual changes of the text values in the EditBox control (via the rbnEditBox1_Change routine)
  • Respond to the button click requesting a worksheet name change (via the rbnButton_Click routine)
  1. Option Explicit
  2.  
  3. Const Btn1Name = "button1"
  4. Public Const editBox1Name = "editBox1"
  5.  
  6. Private Sub OnLoad(ribbon As IRibbonUI)
  7. 'Set the RibbonUI to a workbook property for later use
  8. ThisWorkbook.ribbonUI = ribbon
  9. End Sub
  10.  
  11. Sub rbnGetText(control As IRibbonControl, ByRef returnedVal)
  12. 'Get the text value for the editBox
  13. If control.ID = editBox1Name Then returnedVal = ThisWorkbook.EditBox1Text
  14. End Sub
  15.  
  16. Private Sub rbnEditBox1_Change(control As IRibbonControl, text As String)
  17. 'Store the text value of the editBox for later use.
  18. If control.ID = editBox1Name Then
  19. ThisWorkbook.EditBox1Text = text
  20. End If
  21. End Sub
  22.  
  23. Private Sub rbnButton_Click(control As IRibbonControl)
  24. 'Rename the worksheet
  25. Dim sNewSheetName As String
  26. Dim ws As Worksheet
  27.  
  28. If control.ID = Btn1Name Then
  29. 'Retrieve intended sheet name
  30. sNewSheetName = ThisWorkbook.EditBox1Text
  31.  
  32. 'Check if name is nothing
  33. If Len(sNewSheetName) = 0 Then
  34. MsgBox "I need a name, please.", _
  35. vbOKOnly + vbCritical, "No name entered"
  36. Exit Sub
  37. End If
  38.  
  39. 'Check for sheet name in use
  40. For Each ws In ThisWorkbook.Worksheets
  41. If ws.Name = sNewSheetName Then
  42. MsgBox "Sheet name already used." & vbNewLine & _
  43. "Please pick another", vbOKOnly + vbCritical, _
  44. "Name in use!"
  45. Exit Sub
  46. End If
  47. Next ws
  48.  
  49. 'Would not be here if name not okay so set it
  50. ActiveSheet.Name = sNewSheetName
  51. End If
  52. End Sub

End Result

Once you’ve saved all the code, close and re-open the workbook. You’ll have a custom XLG tab (that you’ve become used to if you’re following the series.) On this tab you’ll find a group that looks like this:

The code fires in this order when the workbook is opened up (assuming Macros are enabled):

  • The RibbonX code is loaded, and the XLG tab is added
  • The Workbook_Open event is fired, setting the EditBox1Text property to the name of the active sheet in the workbook
  • The OnLoad procedure runs, capturing the RibbonUI object to a property for later use in triggering callbacks

At this point, you have the XLG tab showing on the Ribbon. When you click that tab, the “GetText” callback for the editBox is executed. This queries the property we just set, and adjusts the text in the editBox to the name of the active worksheet (which we just stored in the EditBox1Text property, as explained above. Because the button’s properties are set in the XML, no additional callbacks are required to build the button.
Our Ribbon is now fully loaded, the editbox has a name in it, and the button is ready to be clicked. First though, try navigating to other sheets in the workbook. Every time you activate another worksheet in the workbook, you will trigger the following chain of events:

  • The Workbook_SheetActivate routine is fired as the new worksheet is selected
  • The name of the new sheet is sent to the EditBox1Text property and stored for later use (in the “sEditBox1Text” private variable)
  • The editBox is invalidated, forcing the RibbonX code to be rebuilt
  • During the rebuilding, the rbnGetText routine is called, which subsequently retrieves the worksheet name from the EditBox1Text property and builds it into the refreshed ribbon control

The only thing that looks any different now is that the sheet name will have changed in the edit box. You can click back and forth between sheets to watch it change, although that will probably get stale rather quickly. ;)
So now, try typing something in the editbox and clicking the “Commit Name” button. If you check the active worksheet’s tab, you’ll see that the name has been updated. This was accomplished by the following actions, triggered when you clicked the button:

  • As control passes from the editBox field to the button, the rbnEditBox1_Change routine is fired, which sends the value of the editBox to the EditBox1Text property for later use
  • Once the above routine is complete, the rbnButton_Click routine is fired
  • Control id is checked to make sure that we are reacting to the correct button. (Not really needed here, but would be if we added another button which used the same onAction routine.)
  • The editBox’s value is assigned to a string for checking
  • The string is evaluated to make sure it is not blank. If it is, we tell the user and exit the routine without changing the name
  • The string is checked to make sure that it doesn’t conflict with an existing sheet name. If it is, the user is informed and we exit the routine without making changes
  • If the string passed the above tests, we change the worksheet name.

While I can’t really think of a good reason that you would want to add this functionality to the ribbon, I hope that it does a decent job of explaining the “how” of ribbon control and application interaction. :)

Modifying the Ribbon – Part 9

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

In this article I’m going to demonstrate using an “edit box” to change the description of a button on the Ribbon.

XML Markup

Unlike in prior examples, the XML Markup we’ll use here uses a callback to assign the button name, which allows it to be dynamic:

  1. <customUI onLoad="OnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon startFromScratch="false">
  3. <tabs>
  4. <tab id = "xlgTab" label="XLG" insertAfterMso="TabView">
  5. <group id="TestTab" label="My Testing Tab">
  6. <button id="button1" getLabel="GetLabel" onAction="btnControl" imageMso="HappyFace" />
  7. <editBox id="editBox1" label="New Button Name:" onChange="EditBox1_Change" />
  8. </group>
  9. </tab>
  10. </tabs>
  11. </ribbon>
  12. </customUI>

VBA Code Required – ThisWorkbook Module

Like the checkbox example in part 7, we’ll need to add properties to the workbook. In this case, however, it will allow us to capture the name of the button, and also the RibbonUI object. Next, we’ll need to ensure that the button is assigned a default name when we start the workbook. The code to accomplish this is shown below.

  1. 'Private variables to hold state of Ribbon and Ribbon controls
  2. Private pRibbonUI As IRibbonUI
  3. Private sBtn1Nm As String
  4.  
  5. Public Property Let Btn1Nm(s As String)
  6. 'Store the button name
  7. sBtn1Nm = s
  8. End Property
  9.  
  10. Public Property Get Btn1Nm() As String
  11. 'Retrieve the button name
  12. Btn1Nm = sBtn1Nm
  13. End Property
  14.  
  15. Public Property Let ribbonUI(iRib As IRibbonUI)
  16. 'Set RibbonUI to property for later use
  17. Set pRibbonUI = iRib
  18. End Property
  19.  
  20. Public Property Get ribbonUI() As IRibbonUI
  21. 'Retrieve RibbonUI from property for use
  22. Set ribbonUI = pRibbonUI
  23. End Property
  24.  
  25. Private Sub Workbook_Open()
  26. 'Set the name of the original button
  27. ThisWorkbook.Btn1Nm = "Set on Open"
  28. End Sub

VBA Code Required – Standard Module

Like in part 7, I again chose to assign the button and editbox name to constants. The remaining code required will:

  • Use the OnLoad routine (with the same name as specified in our XML) to capture the RibbonUI object to a workbook property
  • Set up the GetLabel callback which will return the name of our button
  • Set up routines (again as specified in our XML) to react to the changing of the editbox value and the button clicks.
  1. Const Btn1Name = "button1"
  2. Const editBox1name = "editBox1"
  3.  
  4. Private Sub OnLoad(ribbon As IRibbonUI)
  5. 'Set the RibbonUI to a workbook property for later use
  6. ThisWorkbook.ribbonUI = ribbon
  7. End Sub
  8.  
  9. Private Sub EditBox1_Change(control As IRibbonControl, text As String)
  10. 'Change the name of the button by invalidating the control.
  11. If control.ID = editBox1name Then
  12. With ThisWorkbook
  13. .Btn1Nm = text
  14. .ribbonUI.InvalidateControl Btn1Name
  15. End With
  16. End If
  17. End Sub
  18.  
  19. Private Sub btnControl(control As IRibbonControl)
  20. 'Give the user some kind of feedback that they clicked the button
  21. MsgBox "You got me!"
  22. End Sub
  23.  
  24. Private Sub getLabel(control As IRibbonControl, ByRef returnVal)
  25. 'Return the label for the selected control to the Ribbon
  26. If control.ID = Btn1Name Then
  27. returnVal = ThisWorkbook.Btn1Nm
  28. End If
  29. End Sub

The End Result

Once you’ve saved all the code, close and re-open the workbook. You’ll have a custom XLG tab (that you’ve become used to if you’re following the series,) which has a button bearing the description “Set on Open”, and an editBox showing “New Button Name:”
The code fires in this order when the workbook is opened up (assuming Macros are enabled):

  • The RibbonX code is loaded, and the XLG tab is added
  • The Workbook_Open event is fired, setting the values of the button name to “Set on Open”
  • The OnLoad procedure runs, capturing the RibbonUI object to a property for later use in triggering callbacks

At this point, you have the XLG tab showing on the Ribbon. When you click that tab, the “GetLabel” callbacks for the button is executed. This queries the property we just set, and adjusts the button name to that value.
Our Ribbon is now fully loaded, the editbox is clear, and the button bears its default caption. Typing something in the editbox and hitting enter will set off the following chain of events:

  • The EditBox1_Change routine is fired
  • The name in the editbox is assigned to the button name property
  • The button control is invalidated, forcing the RibbonX code to be rebuilt by calling the GetLabel routine

The end result is that the value in the editBox is sent back to the button, and the name of the button changes to what you just submitted. :)

New RSS Feed Link

It’s been a bit of a busy week for me at work, and my evenings have been spent doing a little more behind the scenes tweaking of the site… well… not Monday. We had our work Christmas party on Monday, so that night was kind of shot. :)

So what did I do? A few things actually, from testing a new web gallery software to installing a couple of web statistics packages for the blog. I’ll chat quickly about the web stats packages.

Google Analytics
Earlier this week I installed Google Analytics for the blog, in an attempt to start capturing the stats on readership. I.e. how many I get, and where they come from. If you’re not familiar with their services, Google offers the analytics services for free at this URL. Snapping it in to WordPress is pretty easy too. If you don’t feel like editing the page templates yourself, you just download an Analytics plug-in, activate it, and drop in your ID. Within 24 hours, you’re up and running.

So why use it? It captures some pretty cool stats on your readers… and no, nothing personal. In fact, there is so much captured, that I haven’t even explored it all. I have attached an overview from my main site to show the dispersal of my viewers:

Mouse over each of those dots, and it tells you the city and number of visitors. I find this pretty cool to see where my traffic is coming from.

There is much, much more than this, of course. I have full graphs of my unique visits, the percentages of new versus returning users, what sites referred and even an overlay showing exactly what links were clicked from what pages. All in all, very cool stats. Especially for free. :)

Feedburner

One thing I haven’t figured out from Google Analytics is how to track the number of RSS Subscribers to my blog. After a little searching, I came across FeedBurner, which is another free service to capture and track RSS feeds. This is pretty cool, as I also would like to know how many people subscribe, but don’t necessarily visit every time I post new content. To that end, the new RSS feeds (which I’m told will work with any RSS reader) are:

Main Feed: http://feeds.feedburner.com/excelguru

Comments Feed: http://feeds.feedburner.com/ExcelguruComments

Now this install takes a little more work. Based on what was in and inferred in this article, I downloaded and installed the Feedburner plugin and set off adjusting my template code. The Feedburner site itself actually has some pretty good pointers on what to adjust, so it wasn’t hard. The only part that I’ve shied away from for now is trying to redirect the old RSS feed to the new one, as it looks a bit more difficult. I figure the blog is new enough that I probably don’t have a huge number of subscribers at this point anyway, although hopefully that will change.

Once I’ve been using FeedBurner for a while, I’ll post back with an opinion on it. :)

At any rate, I think I’m done tweaking for now, so should be back to posting some more RibbonX examples soon.

Visio Flowcharting Trick

Okay, so I don’t use Visio much, but I stumbled on this and figured I’d share it. :)

This last week I’ve been busy at work flowcharting our “Member Dues Renewal” process, and jumped in to Visio 2003 to do it.  By accident, I found out that if you click the line connector tool, then drag your shapes from your stencil to the page, that it starts connecting them for you.  Pretty neat!

Now, if you have a shape on the page, click the tool, then drag a new shape on to the page, don’t expect it to connect though.  But the next one will.  I guess it wouldn’t know which exisitng shape (if you had many) to connect to, and it just connects to the last one you place while the line tool is active.

Just thought I’d share.  :)

Rant – Clear Your Sidewalks

My wife and I went for a walk tonight.  As some of you have read in a previous post, we’ve had a bit of snow lately, (over a foot and a half at the end of it all.)  That ended last Thursday though.  Since then it’s been cold, but for the last two days, it’s been above 0 degrees (Celsius) and raining off and on.  The snowbanks here are still pretty high, but that’s life.

Here’s what irks me though… we have a bylaw in this city about clearing your sidewalks.  You are responsible for clearing any that front your property.  In our walk tonight, my neighbour and I were the ONLY ones who cleared theirs.  What is up with that?

It’s not like there isn’t traffic on these streets, either.  Kids walk to school down these roads every day.  So much so that the paths are trampled into ice in many spots and are as slippery as anything.  So why?

Four days ago, when it was still below freezing and hard to chip off, I’d understand.  But now that the temperature has been up for a few days, it’s soft enough to break up when you scrape it off.

Again, the city dictates that you are responsible.  In our litigious society, you could be sued if someone slips and hurts themselves on your sidewalk, but that should not be your main motivation.  The thought of a someone getting hurt should be enough.

Modifying the Ribbon – Part 8

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

In this article I’m going to demonstrate a very simple menu created on the Ribbon.  This particular menu will only hold two buttons on it, but we’ll get into some larger examples in future posts.

XML Markup

The XML Markup provided below is very slim.  It still adds our XLG tab and a Testing group, but only adds one menu item on that tab.  The rest of the menu items will be added dynamically at runtime.

  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon startFromScratch="false">
  3. <tabs>
  4. <tab id = "xlgTab" label="XLG" insertAfterMso="TabView">
  5. <group id="TestTab" label="My Testing Tab">
  6. <dynamicMenu id="menu1" label="My Menu" getContent="GetContent" />
  7. </group>
  8. </tab>
  9. </tabs>
  10. </ribbon>
  11. </customUI>

VBA Code Required

Unlike the last couple of examples, no code is required in the ThisWorkbook module at this point.  We will need to add our callback code to populate the menu, and also the onAction code to react when a button is clicked.  That code is all shown below:

  1. Private Sub GetContent(control As IRibbonControl, ByRef returnedVal)
  2. 'Populate a menu item
  3. Dim sXML As String
  4.  
  5. 'Open the XML string
  6. sXML = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">"
  7. 'Add a button
  8. sXML = sXML & "<button id=""button1"" label=""Button 1"" " _
  9. & "onAction=""btnCentral"" imageMso=""FileSaveAsExcel97_2003"" />"
  10. 'Add another button
  11. sXML = sXML & "<button id=""button2"" label=""Button 2"" " _
  12. & "onAction=""btnCentral"" imageMso=""FileSaveAsExcel97_2003"" />"
  13. 'Close the menu string
  14. sXML = sXML & "</menu>"
  15. 'Return the completed XML to the RibbonUI
  16. returnedVal = sXML
  17. End Sub
  18.  
  19. Private Sub btnCentral(control As IRibbonControl)
  20. 'Feed back which button was clicked
  21. MsgBox "You clicked " & control.ID
  22. End Sub

The code above will result in a menu that looks like this:

How it works

Clicking “My Menu” will result in the GetContent routine being fired.  This routine is looking to pass the XML back to the RibbonUI so that it can be “merged” into the RibbonX code and populate the child items.  The code requires no line formatting, so we basically just build a string of XML to create a menu.  The code in the GetContent routine must start with the customui line, and be properly formed XML.

To demonstrate this a little clearer, the dynamic XML we created above to populate the buttons, (with formatting added,) is shown below:

  1. <menu xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <button id="button1" label="Button 1" onAction="btnCentral" imageMso="FileSaveAsExcel97_2003" />
  3. <button id="button2" label="Button 2" onAction="btnCentral" imageMso="FileSaveAsExcel97_2003" />
  4. </menu>

And, of course, because we specified the OnAction parameter as “btnCentral”, it will fire the btnCentral routine when we click either button.  That code is set to tell us what button we clicked.