Posts in category: vba

In a previous post, I went over how to modify the Excel ribbon with some basic buttons. This time, I’m going to focus specifically on a toggle button that can change the value of a cell or trigger a calculation or macro. In this example, I’ll show you how you can create a toggle button on the ribbon that will change the color of the sheet, switching between having no fill and a white fill.

The first thing you’ll want to do is download the CustomUI Editor, which you should be able to find online through a search. Once you have that, you can get to work on coding the xml.

Creating the ribbon code

To get started, you can refer to this post on how to create a simple tab and button on the Excel ribbon. Once you have that basic framework set up in the CustomUI Editor, it’s just a matter of modifying the type of button you’ll use. In that post, I just inserted some generic code for a button:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab" label="Custom Tab">
				<group id="customGroup" label="Custom Group">
					<button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

The button in that example is just a basic button. To turn it into a toggle button, I just need to modify the syntax so rather than saying ‘button’ it says ‘toggleButton’. I still need a label, an image, and specify what happens when it is pressed (the ‘onAction’ argument). Here’s what my updated xml code looks like:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab" label="Custom Tab">
				<group id="customGroup" label="Custom Group">
					<toggleButton id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Changing button to toggleButton just allows the button to have a pressed state so that when you click on it, it can look sunken. You can use that variable to also dictate what action should be taken. The key to making the toggle button work is to attach some macros to it, which is what I’ll cover next.

Creating the callback macros

A callback macro tells Excel what should happen when the toggle button is clicked. Here’s what the macro should look like initially:

Sub Callback(control As IRibbonControl, pressed As Boolean)

End Sub

The pressed value is a boolean, so it will only either be true or false. The next part of the code involves setting up an if statement to check on that value.

    If pressed = True Then

        'code goes here if the value is true (pressed)

    Else
    
        'code goes here if the value is false (not pressed)


    End If

In this example, I’m going to make a simple macro where the sheet is either filled white, or there is no fill effect. The code isn’t complicated and I will start will selecting all the cells, selecting the interior property, and setting it to solid (i.e. ‘filled’):

Cells.Interior.Pattern = xlSolid

And to change it back so there is no fill effect:

Cells.Interior.Pattern = xlNone

Within the full callback macro, this is how all the code looks:

Sub Callback(control As IRibbonControl, pressed As Boolean)

    If pressed = True Then
    
        Cells.Interior.Pattern = xlSolid

    Else
    
        Cells.Interior.Pattern = xlNone


    End If


End Sub

Here is the toggle button in action:

If you liked this post on How to Create a Toggle Button on the Excel Ribbon, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

In a previous post, I covered how you can customize the Excel ribbon in what was a pretty manual process. The good news is there is a much easier way to make changes to the ribbon using the Custom UI Editor. Below, I’ll cover how you can add and remove both tabs and groups, how to add buttons, and how to even use your own images. The first thing you’ll want to do is to download the Custom UI Editor. There’s not a definitive place you can always find this tool at so your best bet is to do a Google search.

How the Custom UI Editor works

Once you have downloaded the Custom UI Editor, you can get to work and begin to customize the Excel ribbon. Unlike just a simple customization, when you modify the ribbon using the Custom UI Editor, you are making changes to the Excel file itself. That means when you send the file to someone, they will see the changes you have made. With just a ribbon customization, those changes only apply to your computer. But these changes will be saved within the file. The Custom UI Editor goes through the cumbersome process of attaching the XML code to the Excel file and makes it a lot easier to make changes to the ribbon.

When you first open the file, you’ll see a blank canvas such as this:

Start with clicking on the Folder icon to load up the Excel file that you want to modify. Any Excel file will do. Next, go to the Insert menu and click on Office 2010 Custom UI Part:

This will create the xml file for Office 2010 and newer versions of the ribbon. If you want to ensure these ribbon modifications also work on Office 2007, then you will want to also insert the file for Office 2007 Custom UI Part. If the file isn’t going to be used on an older version of Excel, this isn’t necessary, but it also doesn’t require much additional effort.

The Custom UI Editor includes some sample code within it that you can automatically load so that you don’t have to start from scratch. With one of the xml files selected, let’s go back to the Insert menu, and this time click on Sample XML and Custom Tab.

This will insert some xml to get you started:

What the code has done is created a tab called Custom Tab and within that, created a Custom Group. Lastly, it has also added a button called Custom Button, which is a large size and uses a HappyFace icon that is built-in within Excel. Here’s what that looks like in the actual Excel file:

The one thing that’s left to do is to link the button to some VBA code so that it does something when you click on it. I’ll cover that towards the end of this post.

If you wanted to run this same xml code for the older version of Excel (2007), then everything would work the same except for the very first line. Instead of this:

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

You would use this:

<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui”>

All you need to do is change 2009/07 to 2006/01.

To check that your code is correct, you can click on the red checkmark icon at the top:

And if you get this message:

Then your code is good to go and doesn’t contain any (obvious) errors.

Adding a custom button to the Home tab

Using the above example, you can customize the Excel ribbon to create a group and custom buttons inside of a new tab. However, I prefer simply adding any custom buttons on the Home tab to make them easy to find. Unless you have many buttons and macros, you probably don’t need to put them on an entirely separate tab of their own.

If you don’t want to create a new tab and just want to put your buttons in an existing tab, then you can use the following code to put them on the Home tab:

<tab idMso=”TabHome”>

When you use a reference of ‘Mso’ that means it is an existing Microsoft tab/group/image. You need to refer to the correct name (see further down for a list of groups and tabs) and then you can put your custom group or button in that tab rather than creating a new one. Here’s what the full code would look like by changing this one reference from the above example:

In Excel, I still have my custom group, but now it isn’t on its own tab. Instead, it goes to the end of the tab:

If it’s too far to the end what you can do is insert it before a certain group. Let’s say I want to put it just before the Alignment group. Then I just need to adjust the code slightly to add the insertBeforeMso (this is case-sensitive) attribute for the group tag:

<group id=”customGroup” insertBeforeMso=”GroupAlignmentExcel” label=”Custom Group”>

This is how the full code looks:

And now my custom tab shows up a lot earlier in the home tab:

Another thing I can do is also remove some groups. If I don’t want the font group, I can add the following line of code in the Custom UI Editor:

<group idMso=”GroupFont” visible=”false”/>

Here is the updated code:

And here’s what the ribbon looks like:

You could make all the Microsoft tabs and groups invisible if you wanted to and can control where you custom group goes. The key is knowing the correct names.

Names of the Microsoft tabs and groups

Here is a list of the Microsoft tabs and the reference you will want to use when modifying the ribbon:

Here are the main groups from the Home tab:

There are more groups (from other tabs) but for this purpose, I just included the most common ones.

Adding an image to a button

If you want to use an existing Microsoft image for your button, then you can view the imageMso gallery here. Once you find the image you want to use, just put that in place of the HappyFace image in the earlier code.

However, suppose you want to make a custom image. I’m going to create one using the Amazon logo to create a button that will open my browser to the Amazon.com website.

For starters, I need to get an image. For large ribbon buttons, you want to aim for a size of 32 x 32 and for smaller images, 16 x 16. As long as it’s a square image, however, you should be okay. Wide images will stretch and won’t look as good. This is the image I’m going to use:

I’m going to use just a simple code for creating the button, which looks as follows:

I haven’t associated an image to this button yet. To do that, I’m going to click on the xml file and go back to the Insert menu. This time, I’m going to select Icons. This will open up launch a dialog box where I can now select the image I want to use from my computer. Once I’ve selected it, it now shows up underneath my xml file:

I can right-click on the name ‘amazon’ to change the id to something else. Whatever if it is, that’s what I need to reference in my xml code. Since it’s not a Microsoft image, I just add the following attribute:

image=”amazon”

And here is my full code:

If I open up Excel, this is what my custom button looks like:

But right now, my button doesn’t do anything. That leads us to the last section on how to customize the Excel ribbon: callbacks.

Setting up callback macros

A callback tells the button which code to run. So that means you need some VBA code to begin with, otherwise, the button isn’t going to do anything. I’m going to create a simple macro that will just open the Amazon.com website:

Sub Amazon()

ActiveWorkbook.FollowHyperlink (“https://www.amazon.com”)

End Sub

The callback function itself can be generated from the Custom UI Editor. If I click on the icon next to the checkmark that looks like a block of code:

The application will produce the VBA code I need to put into my Excel file:

I’m going to copy and paste that back into VBA. However, I need to add a line in between as that code only sets up the macro, it doesn’t do anything yet. I need to reference the macro I created earlier. The full callback macro looks as follows:

‘Callback for btnAmazon onAction
Sub Callback(control As IRibbonControl)

Amazon

End Sub

Now when the button is pressed, the ‘Amazon’ macro will run, which opens the Amazon.com website. You can create a custom button for each macro you want to run and assign an image to each one. All you need to do is to use the callback macro to link the button to the code you want to run.

If you liked this post on How to Customize the Excel Ribbon Using the Custom UI Editor, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.