How to: Repurpose a button in Excel 2007 or 2010

  • Article

Repurposing a button in Excel is incredibly easy these days. To repurpose a button means to redefine what happens when you click the button. Want it to perform some action from a custom macro? Follow the steps below to find out how this is done.

1. Download the Custom UI Editor for Microsoft Office from https://openxmldeveloper.org/archive/2006/05/26/CustomUIeditor.aspx. The download on that page will be near the bottom, labeled "OfficeCustomUIEditorSetup.zip". This ZIP package contains an MSI file, which is an installation file.

2. Double click the MSI file included in the ZIP you just downloaded. This will install the Custom UI Editor.

3. After installation, run the Custom UI Editor. There are only three menu options - File, Edit and Insert. You will need an Excel file saved to your machine, so if you haven't got one already, launch Excel and create a new spreadsheet, then save it somewhere on your machine. For our purposes, we will call this fictional file buttontest.xlsx

4. In the Custom UI Editor, click File > Open. Navigate to where you saved the Excel file and click Open. You now see the name of your workbook in the left hand pane in the UI editor. Now click Insert > Sample XML > Repurpose.

Code is inserted into the main window. You will see the following:

 <customUI xmlns="https://schemas.microsoft.com/office/2009/07/customui">
      <commands>
           <command idMso="Bold" enabled="false"/>
           <command idMso="Save" onAction="MySave"/>
      </commands>
</customUI>

5. You can delete the line which reads:

<command idMso="Bold" enabled="false"/>

So all you are left with is

<customUI xmlns="https://schemas.microsoft.com/office/2009/07/customui">
      <commands>

           <command idMso="Save" onAction="MySave"/>
      </commands>
</customUI>

6. Now you need the idMso name of the button you want to repurpose. Head on over to this page (https://msdn.microsoft.com/en-us/library/dd945681(v=office.12).aspx)to get the idMso of the button you need. For an example, let's say we want to repurpose the Copy menu item under Home. Your code would look like this:

<customUI xmlns="https://schemas.microsoft.com/office/2009/07/customui">
      <commands>

           <command idMso="Copy" onAction="MyCopy"/>
      </commands>
</customUI>

Notice the onAction item is pointing to something called MyCopy. What is that? Glad you asked. That's the subroutine name of the macro we're about to create.

Click File > Save in the Custom UI Editor and close it.

7. Open Excel and your spreadsheet. In my example it was named ButtonTest.xlsx. Inside the spreadsheet, hit ALT+F11 to open the VBA editor.

8. Click Insert > Module. A code module is inserted in the main window. Copy and paste the following code in your window:

Sub MyCopy(IRibbonContol, ByRef cancelDefault)

     msgbox "This is a test"

End Sub

9. Click File > Save filename.xlsx. You will be prompted that you now have a macro in the workbook. Click NO on the dialog and under the "Save as type" dialog select Excel macro Enabled Workbook (*.xlsm) and click Save.

10. Go back to the main Excel sheet. Click the Home tab and then click the Copy menu item. What you should see is this:

And that's all there is to it! In place of the messagebox you can add whatever code you want to add when clicking a button.