Creating a customized ribbon

This recipe is optional, though very useful.

There are several ways that you can invoke a macro. After activating the Developer ribbon, it is relatively easy to click on the Developer tab, and then to choose the Macros button in the Code group. But what if you don't want to do it that way? Shortcuts may help, but only up to a point because macros have a way of proliferating quickly.

This recipe will show you how to create a new ribbon, group, and icons.

Getting ready

Open Excel, with a new sheet active.

How to do it…

1. Before starting with customization, there is an important yet simple way to manipulate the ribbon in Excel. This is not really a customization, but it is still important to know how to control the interface. If you double-click any of the tabs on the ribbon, the ribbon will collapse. By double-clicking the tab again, the ribbon will expand, showing the standard, full view:
   

   Figure 1.26 – The Developer ribbon in the expanded view

   The tabs are collapsed as shown:

   

   Figure 1.27 – Collapsed ribbons

   The standard tabs normally provide all the functionality you need. However, if you have a series of functions you want to use regularly, or even a series of macros, it will help a lot if you could display the shortcuts to those items on a ribbon.

2. To add a new tab to the ribbon, right-click on any existing tab. Choose Customize the Ribbon:
   

   Figure 1.28 – Right-clicking on the Help tab

3. You could also right-click anywhere to the right of an existing ribbon, in the gray area, and choose Customize the Ribbon, or you can click on File | Options | Customize Ribbon.

   The Excel Options dialog box will appear:

   

   Figure 1.29 – The Excel Options dialog box

   There are two columns in the Excel Options dialog box. We will be working on the right side, under the Customize the Ribbon heading.

4. Make sure that all the main tabs are collapsed. If any of these are expanded, click on - to collapse them.
5. Select the Help tab without expanding it, because we want the new tab to appear to the right of the Help tab:
   

   Figure 1.30 – The Help tab selected

6. At the bottom of that column, click on New Tab:
   

   Figure 1.31 – The Help tab selected

7. A new entry will appear on the list – New Tab (Custom) – and under that is New Group (Custom):
   

   Figure 1.32 – The New Tab option inserted

8. Select the New Tab (Custom) listing, then click on Rename at the bottom:
   

   Figure 1.33 – Renaming the new tab

9. In the Rename dialog box, enter an appropriate name for the tab. Click OK:
   

   Figure 1.34 – Typing a new name

10. Select the New Group (Custom) listing:
    

    Figure 1.35 – Selecting the new group

11. Click on Rename at the bottom, then enter a name for the new group. Click OK:
    

    Figure 1.36 – The Rename dialog box for groups

12. In the left column, select Macros from the commands list:
    

    Figure 1.37 – Selecting Macros

    A list of the available macros in this Excel file will appear:

    

    Figure 1.38 – List of recorded macros

13. Select the macro you want to add to the group, then click Add:
    

    Figure 1.39 – Click Add to add a macro to the group

14. Select the newly inserted icon and click on Rename. Type a more descriptive name for the macro, and choose a fitting icon. Click OK, and OK again:
    

    Figure 1.40 – Name and icon selection

    You will have a new tab with a new group, and one icon in the group:

    

    Figure 1.41 – The new ribbon with the new group

15. Click the new icon to run the macro associated with it. You can insert more macros to the same group by repeating the steps from Step 12 on this list.

This recipe should enable you to create an easy access point for all recorder macros in a workbook.