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

This blogpost deals with a slightly more dynamic use of RibbonX than we’ve dealt with in the past here. Specifically, this post illustrates how to create a tab on the ribbon, and add items to it from other workbooks. To do this, we’re going to need to create two files, the first of which will be an .xlam Add-in file. Why an Add-in? I hope to demonstrate that by the end of the post.

Creating the Add-in

First, we’re going to head into Excel, create a brand new workbook, and open up the Visual Basic Editor (VBE). Add a new standard module, and place the following code inside it:

Next, save the file under whatever name you want. I chose “UI Host.xlam”. Make a note of where you saved the file, as you’ll need to reference it a couple of times. (For ease of testing, I just saved it to my desktop.)

Close Excel, and open up the XML for the file using the CustomUI editor tool that is list in the Ribbon Example Table of Contents page. Once you have done that, paste the following XML into the file:

Note in the above XML that we’ve added some pieces to the XML. Specifically:

• xmlns:Q=”My Shared Ribbon”
• This line gives us a way to link each XML namespace between add-ins. It must be the same in each file that is going to share the Ribbon items that you have created.
• tab idQ = “Q:rxTabUI”
• We’re now using idQ instead of just id. This refers to our qualified namespace, and gives us a way to refer to our own controls across files, as you’ll see later.

Save the XML, re-open Excel, and activate the Add-in. (Office|Excel Options|Addins|Go|Browse). Make sure it gets checked. Once done, you should now see a new “UI Test” tab on the Ribbon (after the View tab). Clicking on that should show you the following group:

Clicking the button will tell us the name of the file that the button was created in. (“UI Host.xlam” for me.)

Accessing This Tab From Another Workbook

Since we’re already in Excel, let’s create another new workbook, and drop the following code into a standard module:

(Yes, the code is pretty inane, but if you’re following all this, I expect that you can write your own to do something useful. If not, read some more of the posts here, or email me.)

Now that the code is there, save the file as a macro enabled file (xlsm) and close it. (For the demo, I called the file “Leech.xlsm” for want of a better name.)

Open the file up in the CustomUI editor, and add the following XML code:

Some things to note about this XML:

• We’re using the same xmlns:Q=”My Shared Ribbon” as our Host add-in.
• We also point our code to tab idQ = “Q:rxTabUI”, as this is now a shared tab that we want to add controls to.
• We’ve added a new group to our tab, and by way of the insertBeforeQ=”Q:rxGrpUI” argument, we have asked for it to show up before the Host add-in’s native group

Save the XML code, go back to Excel, and open the file. The “UI Test” tab should now look as follows:

And just for reference, clicking the Button1-4 will tell you that they were called from the xlsm file. Clicking the “Click Me” button, however, will still tell you that it was called from the Host Add-in.

Navigational Effects

Interestingly enough, we have just created a workbook contextual tab. If you create a new workbook right now, you’ll see that the “Leech” group disappears, and you are left with only the “UI Host” group showing, as if the “Leech.xlsm” workbook had not been loaded. Navigate back to the “Leech.xlsm” file, and the “Leech” group is re-added.

Making Your Controls Available To All Workbooks

Currently, the only way I’ve had success with this is to create my file as an xlam add-in. I tried a couple of different things, none of which worked well. Just as a matter of record, they are documented below:

Adding idQ to the Group:

Using the following code to specify the Leech group as a qualified id (idQ):

I was left with the following when I went into another book:

Adding idQ To The Buttons:

The button control does have an idQ as well. I’m sure I’m missing something simple here, but I just could not get this to work. Using the XML below seemed to create everything just find, but only the second two buttons would not even fire VBA code at all. I’ll need to figure this out, but for now, I can’t recommend it:

Using Personal.xlsb

While I had no issue creating a RibbonX UI and saving it in a different xlsb file, Personal.xlsb was different. It was creating the Ribbon tab, yet it was just as visible as the workbook. (i.e. It wasn’t.)

Conclusion

My experiences point to add-ins as the ideal source for Ribbon UI containers if you want to share them between workbooks and make them accessible. If you want to do a lot of work with workbook contextual Ribbon adjustments, I’d suggest creating one container add-in, and having your individual workbooks add to them as they come in scope.

So the final day dawned early, again at 6:20. Staggering somewhat sleepily, I still managed to get ready and drag all Deanna and I’s stuff to Zack’s room at 7:00. (I’d used my four days from Sunday to Wednesday nights, but he booked Monday to Thursday nights.) This at least meant we didn’t have to haul everything to campus and back.It was another day of sessions, of which the last was just awesome. And then, sadly, it was over. Deanna and Shane Devenshire’s wife shared a cab out to the closing lunch on campus, and we met up with them about a half hour late. (The last session ran long.) It was then a good buffet of mexican food and, at long last, time for the bottle…

Bob and I had arranged that he’d bring a special bottle of Scotch with him. For the past three days, Bob had dutifully been carrying it around with him, just waiting for the appropriate place to sample it.

I scooped a half dozen plastic beer cups (necessity begets sacrifice, okay?) and we settled down in the farthest region of the lunch tents. (Actually, that was coincidence, as it was the only table left.) The moment Bob fanned out the cups, though, the busser lady was over, asking if they needed to be cleaned up. LOL! With a big smile, she left when she saw Bob pull out the bottle. I will say, that after a sniff of the scotch, Andrew was the most awake we’d seen him all week.

And here’s our final group photo from that day:

All in all, this was a great event, but way too short. Nick made a great post at Dick’s Blog on what he learned about the MVP’s, and I have some stuff of my own to say on that.

First off, there really is a secret decoder ring for the initiates. Without it, you won’t get into the bathroom at the pub, so it’s pretty important.

Second, I think it’s a natural thing to wonder how the heck you got in to this club, and how well you’ll fit in when you get there. I will say that I was pretty nervous about meeting these guys (and gal), but at the end of it I felt very warmly accepted by them. I cannot describe the feeling of being in the Excel sessions, discussing the issues and potentials of the product that I love, with the people whom I respect. It was very cool.

Catching the bus home sucked, to put it bluntly. And while the next summit is only a year away, I can’t wait to get back there. I had so much fun, and learned a ton.

If you’re looking for more summit info, you can find some at the following links:

• Dick Kusleika posted on his blog
• Damon Longworth posted his entire summit journey in a photo essay
• John Walkenbach’s blog has a couple of posts:
• Summit Update
• His Return
• Andrew Engwirda just blogged his experience as well
• Colo also put up his collection of photos

From the whole event, I can say that I only have one real regret, and that is that I didn’t take enough pictures. (I know that I don’t have a single picture of Ron Coderre, for example, as well as a few others.) But despite that regret, I did leave with one overwhelming feeling…

I feel inspired.

Wednesday morning started, again, at 6:20 AM, and by 7:30 I was on a bus to Microsoft’s Redmond campus.  And from there, any locations are under NDA, so I can’t tell you anything about where I was. 

After a catered breakfast, we were bussed to a top secret location for the day’s Excel “deep dive” sessions.  Of course, those also were under NDA, so I can’t tell you much from there.  Dick Kusleika has already said that he’s not afraid of the future of VBA, and I’d have to agree with him there.  I’ll also say that I’m excited to see some of the stuff that they are looking at for the next version of Excel, which will be Excel 14.

After a full day of sessions, we hiked over to the Microsoft store, and stood in line with about 1000 MVP’s to get in.  After an hour, we were finally able to have a quick shop, although there really wasn’t much that I was interested in.  I bought some pens, and a mouse for a friend, and that was it.  From there, we headed off to our product group dinners.  Unfortunately, we’d been in line so long that by the time we go there, all the food that was left was salad and dessert.  No main courses at all.  This was not good, as we were starving by then.  Fortunately, though, the Excel team was ready to start drinking networking again, so we caught a bus back to the hotel.

We picked up my wife, who according to Bob Phillips is more fun than me anyway (love you Bob!  LOL!)  and headed off to our usual haunt.  In truth, this was probably one of my favourite parts of the summit.  The chance to sit over beer and get to really know some of these guys was something I won’t forget.  I had the chance to sit with Stephen Bullen that night, and got some great advice, as well as things to pursue.  This was after spending lunch discussing best practices for working with text files with Damon, and discussions of OLAP cubes and SQL Analysis Services with Charley Kid during session breaks.  My wife also had a blast during the whole event.

This is a picture of the bar that night.  From left to right here are Damon Longworth (with his back to Colo, who took the photo), Debra Dalgleish, Dick Kusleika and Bob Phillips.  If I recall correctly, Jon Peltier is next, between Bob and myself.  My wife, Deanna, is at the head of the table, next to Stephen Bullen and Zack Barresse (both hidden by John Walkenbach.)  Next is Nick Hodge, and finally Niek Otten.

This next picture is of Nick Hodge trying to keep an eye on everything that is going on.  Honestly, I have no idea how this started, but knowing Nick, it only took a moment to get to this point. 

It was a great night at the bar, that ended when they turned the lights on.  So off we meandered to the hotel bar, and resumed the conversation.  It was sometime during that time that Dick decided that it was a good time to write his next blog post.  So off he ran to his room, returning with his laptop.  The picture below is Dick, Colo and Damon trying to get connected to the wireless hotspot in the lobby:

And finally, this is Dick on the phone, trying to find a Windows Wireless Networking MVP to get the issue resolved.  Sadly, they were all asleep, or something, so he never did get hooked up that night. 

For me, this was the latest night of all.  We had to check out by noon, and we were going to be at the Microsoft campus all day.  That didn’t stop us from closing down the Westin bar, though, and I found myself packing all my stuff until about 2:00AM, dreading the 6:20 start that was looming…

Tuesday was a day full of “platform sessions” at the summit, where they tell you about all kinds of things that you don’t usually work with. Not to be ungrateful, but I found that those sessions were so high level, that they really weren’t useful. While there were some “ooh, ahh” moments in a developer session, I’d really rather have focussed my time on what they call “deep dive” sessions where they get right in to technical content.

At any rate, the day actually started with Bill Gates’s keynote speech… most likely the last one he’ll ever deliver to an MVP summit since he is retiring soon. My Canadian MVP lead, Sasha, had organized an official Team Canada hockey jersey for each Canadian MVP to wear at the keynote. so almost all the Canadians sat together, which looked a little like this:

When the event actually started, the chants of Can-a-da started, and we had a great time. If you’re an MVP and can get your hands on the recording, you might just hear us a little bit.

After the keynote, we went into the afore-mentioned platform sessions, then headed out for the day, to prepare for the big MVP party at the Boeing museum of flight. Before I left, though, I snapped this picture:

This is one of the things that amazed me most about the summit… Microsoft spent huge dollars on the summit. Every street pole on the Redmond campus seemed to have an “MVP Summit 2007″ banner on it, and the size of the super banners like the one above are, as you can see, massive. Microsoft did not skimp on the “Thank you” message at all, which was pretty cool, and really made you feel appreciated. Since most of us would do what we do regardless of the MVP status, this is truly an appreciated thing.

Anyhow… on to the party…

Microsoft organized buses to drive us the 30 minutes from the hotel to the Boeing museum of flight. Since Anne Troy was with us, though, and had a rental car, Bill Jelen offered to drive us there. So five of us piled in to the car. Bill drove, Anne in the front, Zack, Deanna (my wife), and I piled in the back. No sooner do we get in than Bill pulls out a GPS unit and sticks it to the windshield, and off we go.

So here we are cruising along I-whatever, marveling at the cool toy that Bill is using. It’s telling him which turn, how far ahead and everything. Zack asks Bill if it’s ever steered him wrong. “Absolutely not”, he says. So then something weird happens…

Here we are, traveling along at 60 mph when the GPS suddenly indicates a hard right, and Bill slams on the brakes and veers off… Zack’s face is pressed against the glass, and I’m trying hard not to end up in his lap, despite the seatbelt. Once things right themselves, and the centrifugal force dies down, we can see that we’re now drivign down a sleepy country road. There’s no buses anywhere, nor any sign of the museum of flight. I’m looking around, thinking to myself “this is a very scenic route…” Bill tells us that he thinks that he might have missed a turn, and that the GPS is forcing him into a U-turn. At that point he turned on the sound.

A few more turns, and we’re back on main roads, with the GPS telling us… “your next turn is coming up in .5 miles”. We had to ask… “Bill, if you miss the turn, does it call you a moron? No? Does it have a mean voice at all? It doesn’t? Can you download an add-in that gets nasty, cause we really think you need one.” The ribbing would continue all the way to the party.

If you’ve never been to the museum of flight, it’s pretty big. Microsoft had rented the whole thing for us. One of the rooms held an entire XBOX lounge, there was a Jam session room, but the most impressive thing was the museum floor itself. I had to lighten these, so they’re not the best quality, but check this out. yes, that is the bar underneath the jet:

Here’s another of the floor:

And another:

And what you see here wasn’t the whole thing, either. The main floor was sunken, and the surrounding floor was the display area for many war planes. (Just to give you an idea of how big it was.) There was a karaoke area upstairs (which was frightening), and food tables everywhere. My wife and I, amongst others, had a blast wandering around, and drinking networking with all the other members of the Excel group. We eventually said goodbye to Anne (she had to fly home), then ended up downstairs with the rest of the Excel crew. Here’s a picture of how the round table started, although we were joined by others as well.

Above are Zack Barresse, Bob Phillips, Dick Kusleika and Damon Longworth. Clockwise from bottom left below are: Damon Longworth (in green), myself, my wife Deanna (although you can’t see her here), Debra Dalgleish, Stephen Bullen, Nick Hodge, Zack Barresse, Bob Phillips and Dick Kusleika in the hat.

Last shot from another angle shows Dick Kusleika (still in the hat), Damon Longworth, myself and Deanna. With that many pictures, hopefully you can get an idea of how the table set up.

The conversation at that table was great. We talked about stuff from Excel, to the potential Excel Down Under tour, to Canadian Tire money until some polite security guard came over and informed us that the last bus to Seattle would be leaving in about 5 minutes. So off we went, and headed back to our hotel. I actually can’t remember if we stuck around and closed down the Westin bar after that or not. Some of the nights started to blend together.

Again, still more to come…

Well, I’ve been missing in action for a few days on the blog, but due to the fact that I was having a great time at the summit. I will say that there were some pretty long days involved in this, but they flew by. I think the earliest I crawled into bed was 1:00AM, and I was up at 6:20AM every morning to get ready and head off to the various events.

At any rate, in my last post, I left off just before dinner on the Monday night, so here’s what happened from there…

We went off to the “Regional Dinner”. There were actually 4 of these, I think. One for Japan, EMEA (Europe, Middle East & Africa), APAC (Asia Pacific), and the America’s dinner, covering both North and South Americas. I joined the table of US Excel MVP’s, and got the chance to meet a couple of the guys who I had not met the night before. Bob Umlas, Charlie Kyd, Curtis Frye and Damon Longworth. (I think… there may have been others…)

Unfortunately, I don’t have any pictures of that part, but Damon does, starting right about here. (Keep clicking next, as he was very photo happy!)

While dinner was good, and the conversation was great, the entertainment was awful. I don’t think I’ve ever seen a more obnoxious, less funny comedian. Bob Umlas and Curtis Frye started doing magic tricks at our table, to at least keep us entertained. After a while, though, none of use could take it any longer, and escaped for a group photo that I’ll post when I get it. We hung out until about 8:30 or so, then a few of us took off to go and check out the SupportSpace party.

I’m not going deep into SupportSpace here, but suffice it to say that they are a commercial upstart that wanted to get the MVP’s and other experts in a room to pitch business at them. They hosted a party, and flew Anne Troy out to attract some of the MVP’s, which was a good enough hook to get a few of us to go. So off we went, and listened to their pitch. The cool thing about this event was that we got to have a VBAX photo!

The characters from left to right are: Tony Jollans, Zack Barresse (firefytr), Gerry Knight (fumei), myself, Colo, Andrew Engwirda and Anne Troy. You might notice that Andrew is holding up an XBOX… he won it as a door prize. Now how cool is that?

For the record, while Gerry may not be an MVP in title, he was invited as an expert to the party. (As anyone who has ever dealt with him knows he is.) He went through an absolutely horrible Greyhound trip to get down to Seattle to meet us all, but I’ll let him tell that story if he likes.

More to come as I catch up on the days…

Hi everyone,

We’ve got a bit of a break before dinner tonight, so I thought I’d give a bit of an update. Sorry that all the pictures are linked, but they’re huge, and wouldn’t scale to thumbnails. As of March 17, I scaled down all the pics and put them in the post. Sorry if you’re on dialup, and I hope the download isn’t too bad.

My wife and I came down to Seattle by Greyhound yesterday, which was an interesting trip. We got delayed coming through customs because one of the passengers failed to declare himself as an US citizen. He told us later that he had dual citizenship (US/Canada), but had nothing to prove his US, so he decided to travel under Canadian only. I wasn’t in line right behind him, but the guards gave it to him pretty good. Overall, it delayed us by about 20 minutes.

Then we got to Mt Vernon, (I think,) where the bus driver announced… “We will be here long enough for people to get off the bus, and for people to get on the bus. That means that we will be here long enough for people to get off the bus, and for people to get on the bus.” We all kind of laughed, but some crazy woman got off the bus and went in to get a coffee. When the friend told the driver, he said “too bad”, and started to drive away. He actually did stop, but it was rather amusing to see the look of panic on her face as she came screaming out of the coffee bar, waving frantically with a coffee in one hand. Needless to say, no one tried to get a coffee at the next three stops.

The wind was just howling on the drive down, and the bus just about got blown off the road several times. In the end, though, we got down to Seattle and managed to walk the three blocks to the hotel between rain showers. We checked in to a nice hotel room, with the view of the Space Needle.

So upon checking in, we headed downstairs and met up with the Excel group. That was pretty cool to be able to finally meet these guys face to face. The people that were there last night included (in no particular order):

• Zack Barresse
• Bob Phillips
• Colo
• Andrew Engwirda
• John Walkenbach
• Jon Peltier
• Dick Kuslieka
• Shane Devenshire
• Tushar Mehta
• Stephen Bullen
• Nick Hodge

After chatting with the guys in the lobby, Zack, Colo, Bob, Andrew and myself headed out for dinner. In the picture below, we are, from left to right: Me, Colo, Zack, Andrew and Bob. (My wife took the photo for us.)

After dinner, we headed down to the pub to rendezvous with the rest of the team. Left to right below are Bob Phillips, Nick Hodge, John Walkenbach, Jon Peltier and Tushar Mehta. (And that would be Stephen Bullen’s arm holding a beer to the left of the picture.)

This morning, I had a couple of meetings, and then headed off to Registration for the Summit. As kind of a cool bonus, Anne Troy was flown in, (not an MS MVP though, but for another business venture,) so we got to meet her face to face as well. So here’s some shots:

Anne with Zack

Anne with Me

Myself and Zack (who is trying to look 4 inches taller)

And now I’m off to the MVP regional dinner.

I got an email the other day from a friend wondering if I had anything to explain how to use Form controls. I don’t use them a lot, but they can be quite handy, and ironically I’d just finished using them in a project of mine. That project was actually a Net Present Value calculation to evaluate whether it would be better to purchase a software update plan, or replace my software at full retail every 3 years. I’m not going to post that project right now, but I may in future.

At any rate, the question sort of stuck with me, so I thought I’d write something up on it.  Eventually, I’d like to write up a formal article on this for my site, but for start, I thought I’d blog it to get some of my thoughts on virtual paper. 
ActiveX vs Form controls?

Excel has two sets of controls available to put on a worksheet. ActiveX controls, available from the Control Toolbox, and Forms controls.

ActiveX controls are pretty robust, allowing you to change many of their properties, such as their backgrounds. They also have a variety of built in macro events that are linked specifically to the objects. They do have their drawbacks, however. If you don’t know VBA, you may have a much harder time working with them, as you need to write code to do things with them.

By contrast, Forms controls are much more simple. They have built in methods to easily place values in worksheet cells with no programming needed. Unfortunately, you can’t manipulate the display of them much at all. Having said that, they can be quite simple to use, and can help in forcing your users to choose amongst the options you give them.

The remainder of this blog post will focus on how to set up and use the Form controls that I use most often; Group Box, Option Buttons, ListBoxes, CheckBoxes, ComboBoxes, Spin Buttons and Buttons.

Where do you find the Forms controls?

If you are using Office 2007, both the ActiveX and Forms controls are located in the Controls group on the Developer tab. Click “Insert” there, and you’ll get the list. For earlier versions of Excel, you’ll find the Forms Toolbar by going to View|Toolbars|Forms. I’ve included an annotated view of the Forms toolbar here:

Forcing user to choose from a list

One of the great things you can do with these controls is to force a user to choose a value from a list. We’ll illustrate this by creating one to begin with. Go to a blank sheet in your workbook and enter the following list of colours. (Or colors if you happen to be one of my US friends. )

• Red
• Orange
• Yellow
• Green
• Blue
• Purple

Now, highlight the list, and name it rngColours.

If you don’t know how to do this easily, go to Insert|Name|Define (or Formulas|Defined Names|Define Name if you’re using Excel 2007.) Type “rngColours” in the Name box, and make sure that the cells that contain your list are in the “RefersTo” box.

We’re now ready to use one of the Form controls on our sheet.

ComboBox and Listbox

Comboboxes and listboxes are virtually identical in use, but vary in their presentation. While the listbox shows all items in the list, combox boxes keep only one line until you click the arrow to expand them. For this example, we’ll use a combobox. Personally, I find these to be one of the most appealing of the controls, as they have a small footprint but still contain the list of data. So move to a new sheet (let’s keep it separate from our data), and draw a Combo Box on your worksheet:

• Click the Combo Box on the Forms toolbar
• Starting at the upper left of cell A3, left click and drag your mouse to cover to the bottom right of B3

Notice that the Combo Box always drops lower than the bottom of the current row. I don’t know why this is, but the minimum height of a combobox is 15.75, where the default row height is 12.75.

At any rate, let’s right click the control, and choose “Format Control”. The true power of this control is all on the “Control” tab, so let’s navigate there.

In the “Input Range” field, fill in “rngColours” (or whatever you called the range). You could also refer to the range using Excel’s standard A1 format if you like.

The “Cell Link” is the big key here. This is the cell that the selected value will drop in to when we select something. Let’s make this C3 for now, and then click OK.

Click somewhere on the sheet to get out of design mode (the little circles surrounding the control will go away), and then left click it. You should notice that it gives you a drop-down list with all the colours. Select one.

Now, in cell C3, you should see a number. This is the number of the item from the list. Great, you say, but that’s not what I chose… how do I get the actual colour?

This is actually part of the power of the forms controls. You get the index number of what was chosen, which allows you to look up things in tables. Let’s enter the following in cell D3: =Index(rngColours,C3)

Go and change the combobox values a few times. You should see that your colours keep updating.

So why is this Index return so great? Assume that our data table had many more columns to it. We can use that value to look up the appropriate values in other columns as well, simply by adjusting our formula. Assume we added another column to our data table that held something important. We could return that value in the next column over by adjusting our formula to =Index(rngColours,C3,2). Please note that if you have a multi-column range, you’d return the values in the first column by way of =Index(rngColours,C3,1)

Tip: Put the value of the combobox in cell A3, to hide it behind the control, or place it in a hidden range to keep it out of the users’ view.

Group Box and Option Buttons

The Group Box is a control that is specifically used to keep Option Buttons together and have them work as a group. To illustrate this, let’s go to a new sheet, and add a nice big Group Box:

• Click the Group Box on the Forms toolbar
• Starting at the upper left of cell A3, left click and drag your mouse to cover to the bottom right of D16

Now, add six Option Buttons to the group:

• Click Option Button and draw it within the Group Box, starting upper left of B4 to lower right of C4
• Do this 5 more times, on rows 6, 8, 10, 12 and 14
• Change the names, from top to bottom, to Red, Orange, Yellow, Green, Blue, Purple

Yes, this was a little painful and tedious, but it gives us a way to present our users with choices. What we want to do now is figure out what choice they made, so right click one of the Option Buttons and choose “Format Control”. If you are not already on the Control tab, select it and notice that you can “Uncheck” the option button should you need to. In addition, we have the option to set a Cell Link here, so let’s set that to E3 and say OK.

Before we move on, let’s set up the formula to return the chosen colour. In F3, enter the formula =Index(rngColours,E3).

Now, try selecting on of the option buttons. You should see the colour you’ve chosen show up in F3. I should point out that this has nothing to do with the names you assigned to the buttons, and everything to do with the value you chose. This is why we made sure we named the buttons from top to bottom in the order our list was set.

A couple of additional points of interest here:

• Only one Option Button within the frame can be selected at a time. If you add a button outside the frame, however, it could be selected as well as one from within the frame. Likewise if you have multiple frames… one in each.
• Only one cell link needed to be set up. This setting affects all option buttons within the frame.

Spin Button

The spin button is a bit different from the afore-mentioned controls. Create a new sheet again, and drop a spin button control on your worksheet. (You can choose where.)

Right click it and have a look at the following screen (captured from Excel 2007):

So what is all this?

• Current Value tells you what the value of the spin box is currently set to. Since we’ve just created it, it’s zero
• Minimum value is as low as the spin button can go. You cannot set a value below zero, but you can set it higher. Let’s set it to 1.
• Maximum value is whatever you choose. In this case, we’ll set it to 6.
• Incremental change is how much you want the spin button to change by each time you click the button. We’re going to leave this at 1.
• Cell link, as you might expect, is where you want the value outputted to. Choose B3.

Note: All numbers here must be whole numbers. No decimals.
If you’ve been following along with this post, you’ve probably guessed that the formula in C3 is =Index(rngColours,B3). Click up or down on the spin button, and you should see the values change:

You may recall that at the beginning of this post I mentioned that I used some form controls in my Net Present Value calculation. It was this control that I used so that I could spin the IRR and Interest rates. (One for each.) I also mentioned that you couldn’t put decimals in the spin button box, so here’s how I did it:

The values I had in the spin button configuration were:

• Minimum: 0
• Maximum: 1000
• Incremental: 1

I then dumped the value into a cell, (B1 pictured below), and put the following formula in cell C1: =ROUND(B1/1000,3). Finally, I formatted it with a % format, and allowed an additional decimal place to be displayed. I ended up with:

Checkbox

The checkbox is similar to an Option Button, except that you do not need to place it inside a Group Box. In addition, each checkbox can be selected individually from all others, meaning that you can have one, some, all or no checkboxes in your worksheet checked. The cell link, accessed the same was as the controls above, places only a TRUE/FALSE value in the cell.

Button

The button control is the most simple of all.  I pretty much just acts as a very simple macro launcher.  As soon as you add it to a worksheet, it will ask you which macro you wish to run.

Conclusion

While I haven’t covered them all, hopefully this has given you enough information to use the most common of Excel’s built in Form controls.  They can be a great addition to help you control your users actions in your worksheets and, combined with worksheet level security, are one way to encourage your users to make valid data selections.  Properly set up, they can also add a certain flair or “bling” to your worksheets as well.

As always, I encourage comments and thoughts on this material.