Tag Archives | programming

Customising the Office Ribbon

Those of you who use Office 2010 and 2013 are probably quite happy with the Ribbon customisation options offered in those versions. Office 2007 had the Ribbon but no customisation at all.

What if you want to add sub-menus? This is not possible in the 2010 and 2013 user-interfaces and according to Microsoft you really need to program in Visual Studio .NET to get these extra customisations; however, I found an article written way back in 2009 that I want to bring to your attention that still works and gives some really cool customisations using VBA and a little XML. Now don’t panic at the thought of the XML as the article explains it all.

Take a look at the step-by-step guide to customising the Office Ribbon

Excel Data Validation and Case-Sensitivity

I was working with a client recently on some VBA programming and the client was trying to limit data entries in a column of cells to one of four specific values. Now those of you who are familiar with Excel will immediately shout out “set up data validation”, and you would be correct. However, not entirely, as most people would create the set of entries in a range of four cells somewhere and set the Excel data validation to that range of cells.

While using data validation this way would work after a fashion it would not enforce the case of the entries. Those of you staying in Excel would probably not worry. I was working using VBA (Visual Basic for Applications) and the VBA code was case sensitive. Not only that but the client wanted to force the text entered by users to have the first letter of each entered word to be upper case.

So how do you force data validation to enforce the case of entered values. The answer is to enter the values literally in the Source box of the Data Validation dialog, separating each value with a comma, and not set the source to be a range reference. So if I was restricting my entries to the three major cities of England then I would type in the Source box:

London, Birmingham, Manchester

In this way London would be acceptable but london or lonDON would be rejected.

Of course, if you are working with a dynamic list then you have to stick with range references and use some other method (as I would in my VBA code) to enforce the case of entries, but the above is a simple solution for short, static lists.