Since Microsoft Office 2007, Microsoft uses a graphical interface for its menu items called ribbons. The ribbon interface has changed a bit from Office 2007, but the basic concept of a ribbon remains. You can't change the main components of a ribbon using VBA, but you can create your own custom ribbon using VBA.

For instance, suppose you want to create a custom ribbon and button for your new VB application. You could launch code that runs when the user opens Excel. The code would identify if the ribbon already exists, and then it adds the ribbon if it does not already exist in the interface. The user can then use this ribbon to work with your application. You can execute, run, close or review your application from a ribbon.

In this article, we will work with the Excel ribbon and show you how to edit it for your own application customizations.

Creating Custom XML Code

Before you write your subroutine, you need to create custom XML code that is used to specify the ribbon layout, color and format. There are some third-party tools that can work with Excel and other office productivity tools to create custom code for you. Microsoft XML is specific to Office, so it can be difficult to understand. Since there are several options, you might need to perform a lookup on Microsoft's site to understand each element and value.

We'll use an example XML string to show you what an example ribbon looks like. You can build an XML string manually or using a tool. Whatever you choose, you must have valid XML and each tag must have the right value for Excel to parse the information.

The following is an example of a custom ribbon XML string.

Dim xml As String

  xml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"

  xml = xml + " <mso:ribbon>"

  xml = xml + " <mso:qat/>"

  xml = xml + " <mso:tabs>"

  xml = xml + " <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"

  xml = xml + " <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"

  xml = xml + " <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "

  xml = xml + "imageMso=""DiagramTargetInsertClassic"" onAction="" SendMessage ""/>"

  xml = xml + " </mso:group>"

  xml = xml + " </mso:tab>"

  xml = xml + " </mso:tabs>"

  xml = xml + " </mso:ribbon>"

  xml = xml + "</mso:customUI>"

The code above builds an XML string and assigns it to the variable xml. As you can see, the xml string is extensive and has several tags. You will likely need to build your own by looking up the tags and necessary values or using a third-party tool. In this string, you create a tab called "Highlight" that lets you toggle a task's color. This is just an example. You can have your button and ribbon bar perform any task that you need.

Add the Ribbon to the Ribbon Bar

With the ribbon string created, we can now create a subroutine that adds the ribbon customizations to the user's main Excel menu. Let's take a look at what this subroutine looks like.

Sub CreateRibbon()

  Dim xml As String

    xml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"

  xml = xml + " <mso:ribbon>"

  xml = xml + " <mso:qat/>"

  xml = xml + " <mso:tabs>"

  xml = xml + " <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"

  xml = xml + " <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"

  xml = xml + " <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "

  xml = xml + "imageMso=""DiagramTargetInsertClassic"" onAction="" SendMessage ""/>"

  xml = xml + " </mso:group>"

  xml = xml + " </mso:tab>"

  xml = xml + " </mso:tabs>"

  xml = xml + " </mso:ribbon>"

  xml = xml + "</mso:customUI>"

  ActiveProject.SetCustomUI (xml)

End Sub

This subroutine simply creates the ribbon. Notice that we use the ActiveProject class, which is an internal class in the Office framework. This class has the SetCustomUI method that then uses the xml variable input to define the new ribbon. If the XML is poorly constructed, this method will fail, so ensure that you use the right structure when you build your XML string.

Notice that the string has double quotation marks around string values. This is to ensure that the quotation marks are seen as "literals." When you use one quotation mark, Visual Basic sees this as either the start or termination of a string. When you use double quotation marks in a string, the Visual Basic compiler sees this as literal quotation marks. This allows you to add quotation marks to your strings without cause an error in your code.

With this subroutine created, you can now add it to a startup routine. Most ribbon creation subroutines are added to the Project_Open event. This event fires each time you open a new project in Excel. Let's take a look at the code.

Private Sub Project_Open(ByVal pj As Project)

  CreateRibbon

End Sub

IN the code above, we use the Project_Open event. Remember that you can't change an event's code. You must leave the variables in place and you want to leave the subroutine Private. The only item we change is the call to the CreateRibbon subroutine, which essentially creates the ribbon using the XML string that we created earlier.

When you call a subroutine in another subroutine, you essentially run its code within the local function. In other words, the above code could be broken down to look like the following.

Private Sub Project_Open(ByVal pj As Project)

Dim xml As String

  xml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"

  xml = xml + " <mso:ribbon>"

  xml = xml + " <mso:qat/>"

  xml = xml + " <mso:tabs>"

  xml = xml + " <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"

  xml = xml + " <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"

  xml = xml + " <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "

  xml = xml + "imageMso=""DiagramTargetInsertClassic"" onAction=""SendMessage""/>"

  xml = xml + " </mso:group>"

  xml = xml + " </mso:tab>"

  xml = xml + " </mso:tabs>"

  xml = xml + " </mso:ribbon>"

  xml = xml + "</mso:customUI>"

  ActiveProject.SetCustomUI (xml)

End Sub

Defining a Subroutine for the Ribbon Button

Now that we have the ribbon XML, we need to provide a subroutine for the action taken when the user clicks a button. This action could be anything. You can change cells, show a message, or even open a Word document. Whatever action you take is up to you.

The name you give to your action subroutine is defined by the onAction property set in the xml string we created in the previous section.

Let's look at the XML again.

Private Sub Project_Open(ByVal pj As Project)

  Dim xml As String

    xml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"

  xml = xml + " <mso:ribbon>"

  xml = xml + " <mso:qat/>"

  xml = xml + " <mso:tabs>"

  xml = xml + " <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"

  xml = xml + " <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"

  xml = xml + " <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "

  xml = xml + "imageMso=""DiagramTargetInsertClassic"" onAction=""SendMessage""/>"

  xml = xml + " </mso:group>"

  xml = xml + " </mso:tab>"

  xml = xml + " </mso:tabs>"

  xml = xml + " </mso:ribbon>"

  xml = xml + "</mso:customUI>"

  ActiveProject.SetCustomUI (xml)

End Sub

The important part of the above string is this line:

xml = xml + "imageMso=""DiagramTargetInsertClassic"" onAction=""SendMessage""/>"

Notice the onAction property in this part of the string. This sets what happens when the user clicks your custom button. We defined a subroutine for this button called SendMessage. When we click this button, the compiler will specifically look for a subroutine named SendMessage. Whatever code is included in this subroutine will be executed.

Let's take a look at the code we can use for our SendMessage subroutine.

Sub SendMessage()

  Dim tsks As Tasks

  Dim't As Task

  Set tsks = ActiveProject.Tasks

End Sub

Notice that we created two variables. These variables capture the tasks we need to do for our button. Normally, you would perform a different process for each task. Since we will just send a message, we can view what happens as Visual Basic loops through each one. We assign the tasks to a variable named tsks. Let's take a look at the code.

Sub SendMessage()

  Dim tsks As Tasks

  Dim't As Task

  Set tsks = ActiveProject.Tasks

  For Each't In tsks

  MsgBox t

  Next t

End Sub

In the above code, we loop through each task and display it to the user. This method is a great way to follow through all of the code and see what happens when you click a custom button.

Notice that we don't use this code in the startup project event. We only want this function to run each time you click a button. The button itself is only created when you add it to your custom ribbon.

Creating Add-Ins

Excel and VBA offer you the ability to customize a user's interface using add-ins. Add-ins are workbooks you create and save using the XLA file extension. Users are then able to install your add-in, which usually provides them with a custom ribbon and modules for extended controls. These controls are often useful when you want to create a module specific to a department. When you create an XLA file, the workbook included is invisible to the user except when editing from the Visual Basic Editor.

Creating an Add-In File

As we mentioned, add-ins are workbooks that can't be edited except in the VBE. These workbooks are invisible to the user, so they only see the add-in functionality and not the workbook itself. If you look at the top of your Excel application, you'll see an "Add-ins" section. This section contains the custom buttons and controls you can offer your users to work more easily with your application code. Instead of creating a button on a spreadsheet that could get in the way of data, you can create a button in the add-ins section that the user can use when the application macro is needed.

When you create an add-in, you save it as an XLA file and then users can add the file to their own Excel projects. Before we show you how to include an add-in with an Excel project, we'll show you how to create a small add-in.

Add-ins have their own event handlers that aren't available with standard spreadsheets and workbooks. You first must create a new Excel workbook and save it as an XLA file. When you save it, you have the option to place a password on the file to protect it from being edited by your users. It's not necessary for practice projects, but keep this option in mind when you create complex add-ins. If you want to control the source code, you should password protect the workbook.

After you create the XLA file, open the VB editor. Create a new module and then choose "Workbook" from the first dropdown in the editor. Click the second dropdown and notice that you have several other options that weren't there when you were working with a standard workbook.

You will see the AddinInstall event and the AddinUninstall event. These two events are used when the user installs your addin or removes it from their project. We'll work with the AddinInstall event to include a button for our users to click. When users click this button, our module will perform an action. We created buttons and ribbons in the last chapter. In this chapter, we'll use internal Visual Basic functionality to add a button instead of creating long XML strings.

Click "AddinInstall" in the module dropdown. You'll see the following code automatically created in the module.

Private Sub Workbook_AddinInstall()

End Sub

This event runs with the user installs your add-in. We'll show you how the user installs the add-in at the end of the section.

Next, we need to create a control. We'll add a button. The following code creates the variables we need to create a button.

Private Sub Workbook_AddinInstall()

  Dim button As CommandBarControl

End Sub

As you can see from the code above, we only need one variable. Now let's add the code that adds the button to the command bars.

Private Sub Workbook_AddinInstall()

  Dim button As CommandBarControl

  Set button = Application.CommandBars("Tools") _

.Controls.Add(Type:=msoControlButton)

End Sub

In the next line of code, we assign the button variable a control. The msoControlButton is an internal variable that automatically has the right integer to assign a button to the variable. Just know that any variable with the mso prefix is an internal "Microsoft Office" variable.

With the button created, we now need to set its properties. We need to tell the application what happens when the user clicks a button and the text that should display for the user. Let's take a look at the code.

Private Sub Workbook_AddinInstall()

  Dim button As CommandBarControl

  Set button = Application.CommandBars("Tools") _

  .Controls.Add(Type:=msoControlButton)

With button.BeginGroup = True

  .Caption = "Click Me!"

  .FaceId = 0

.OnAction = "ClickMe"

End Sub

In the code above, we added some properties to our button. We first added a caption. This caption is what shows to the user in the add-in toolbar. We set the caption to "Add Me!" FaceId is another integer value that controls the look of the button. We set this to 0, but there are other FaceId values. You must look up the right integer value to find out what interface look you want for a button. There are several listed in Microsoft's VBA documentation.

Finally, we set the OnAction property. This is probably the most important property, because it defines the action or subroutine that runs when the user clicks the button. If we excluded this property, your button would do nothing.

Creating an Action Module

With the add-in defined, we now need to write some code that performs an activity when the user clicks a button. This subroutine is not unlike the others we have written. The subroutine must be named the same name as what is defined in the OnAction property we set up in the previous section.

Let's take a look at some simple code that we can use to test our new button.

Sub ClickMe()

  MsgBox "You clicked me!"

End Sub

Of course, you can get much more complex than a simple message box, but we just want to test that our add-in is working. We haven't installed the add-in yet, but after we do the button will display in the toolbar and the message "You clicked me!" will show to the user.

Uninstalling an Add-in

Your users sometimes need to uninstall your add-in. When they uninstall your add-in, you must remove the custom button in the add-in ribbon or your users will have a broken interface.

Let's take a look at the uninstall module that will remove the button.

Private Sub Workbook_AddinUninstall()

  Dim button As CommandBarControl

  Dim cbt As Integer, i As Integer

  i = 1 cbt = Application.CommandBars("Tools").Controls.Count

  Do Until i > cbt

  Set button = Application.CommandBars("Tools") _

  .Controls.Item(i)

  If button.Caption = "Click Me!" Then

    button.Delete

    Exit Do

   End If

  Loop

End Sub

In the code above, we created a loop. We only created one button during the installation method, but this code assumes that you have more than one button installed. In most add-in code, you have more than one button that functions within your application. To properly uninstall the code, you need to loop through each button and remove it from the toolbar. In this example, we only delete a button with the caption "Click Me!"

Remember that Visual Basic string values are case sensitive. This means that if we used "click me!" in the If statement, it would always return false and your button would never be disabled. This is one reason that you want to password protect your add-in code. If you allow users to edit the code, they might edit the text displayed in the button. When they uninstall the code, it would then never delete the button.

The button.Delete method is what removes the button. Once all buttons are deleted, the subroutine ends and the added workbook is removed from the user's project.

Install the Add-In In an Excel Project

After you create the modules and installation process for your add-in, your users need to install it to integrate it into a current project. You can test your own add-in by including it in a test project.

To install an add-in click the Office button and click Excel Options. If you have a newer version of Excel, the button is named just "Options." The Add-in dialog window opens where you choose the add-in you want to include in your project. You can click the Browse button to choose an add-in, or you can check the box if it's included in this dialog window.

Once you select an add-in, the XLA file's workbook is included in the project. If you recall, we suggested that you password protect it. Users can open the XLA file in the Visual Basic Editor and change any of the module code. You set a password on the file when you save it originally during the development process.

Developing add-ins is one of the most powerful tools for an Excel developer. It helps you customize your applications and give your users the ability to work with macros and VB code designed specifically for their own functionality. Your users will appreciate that your add-ins are available in a toolbar rather than cluttering their workbooks and spreadsheets.

Add-ins are also much more convenient when you need to deploy your code to several different workstations or users. You can save the XLA file to a network drive and let the user retrieve it. This means that you only need to deploy it to one location and let users install any add-in functionality as they need it. Just remember to password protect your files to avoid any unwanted changes in your code.