Willy's
Freeware

 Windows 95...XP
 Agenda 
 Belgian postal codes 
 Calculator 
 Htmlpad 
 Links2Tray 
 Startup manager 
 Excel 97...2007
 Alphabetical Disorder 
 Customize the Ribbon 
 Perpetual calendar 
 DOS
 Compaction manager 
 GPS
 MTB tracks 
 Fietsroutetracks 
 Fietsknooppunten 
 Other
 Download 
 How this site is setup 
 Index 
 Home 

Top

Simplicity is
the characteristic
of the real thing.

Customizing the Office 2007 Ribbon

 Introduction  Example 1  Example 2  Example 3  A real  life example 

Introduction

In earlier versions of Excel and Word it was relatively easy to add or remove items in the menu or toolbars or even make custom toolbars. Since the menu and toolbars have been replaced by the Ribbon in Excel 2007, those customizations made in an earlier version are grouped in the Add-Ins tab of the new Ribbon. Most probably you will not agree with the way Excel has done this conversion but the problem is that you cannot edit this tab. You can only delete it. But then you have to start from scratch using a totally new technique.

Since the Ribbon is so different from earlier menus and toolbars it is probably even a good idea to rethink your customizations. Some commands you created earlier will even not be necessary anymore since they can perhaps be executed more conveniently from the standard Ribbon. Commands you use very often can be put in the Quick Access Toolbar.

Custom commands can be grouped in an extra tab. I'll explain here three examples you can use as a basis to develop your own solution.

To try these examples, you don't need any special tools. Just plain Notepad will do. It is assumed though you are familiar with HTML/XML and Excel/Word Visual Basic.

Example1: creation of an Extra tab in Excel with a group with two buttons and a menu. The technique is similar in Word.

Example 2: creation of an additional group in the Excel Extra tab which is only visible when a particular sheet is active.

Example 3: creation of an Extra tab in Word with a group that is visible in all documents and another group which is only visible in a document based on a particular template. The technique is similar in Excel.

A real-life example: I developed some universal clever macros and use them for quite a while now finding them to be very useful. They are stored in an add-in (download) that adds an Extra tab to the ribbon so that they are available in all workbooks.

  1. The Formula's to values macro is a macro that does just that but in one operation.
  2. The Change Case macro is a macro that changes the text in a range of cells to UPPER CASE, lower case, Title Case or Sentence case.
  3. The Formula... macro is a macro that lets you manipulate the contents of a range of cells directly.
They have the following characteristics in common:
  • You can use the macros on a single cell, a range off cells or on multiple selections in one operation.
  • When your sheet is filtered, the macros will only act on the visible cells.
  • You can select entire row(s) and/or column(s) or even the entire sheet. The macros will automatically restrict the range to the Lastcell in use to prevent it to run "forever" especially in Excel 2007 with its 1 048 576 rows and 16 384 columns.

The Basics

First of all you probably already know that Excel/Word 2007 now save files in the new Office Open XML File Format which is in fact a ZIP file with another extension (.xlsx, .xlsm respectively .docx, docm etc) containing several XML files.

To add a tab to the Ribbon, you have to pack (add) another XML file to your workbook/document zip containing the necessary XML elements representing the Ribbon controls. How you name this file and where you put it is of no importance as long as you tell Excel where to find it. This is done by adding a reference to it in the .rels file that resides in the _rels folder of the zip file.

Some useful links

 

uptime Statistieken