In This Chapter
Beginning with Microsoft Office 2007, the primary user interface was changed from menus and toolbars to the Ribbon. While there are similarities between toolbars and the Ribbon, the Ribbon is radically different, particularly when it comes to VBA.
The Ribbon is made up of a hierarchy of tabs, groups, and controls. The tabs appear across the top. Each tab consists of one or more groups and each group consists of one or more controls.
The Ribbon supports many types of controls. While I don’t discuss every type of control in this chapter, I do discuss the ones you are likely to use. If you’re used to the older menus and toolbars, you’ll appreciate the flexibility that the Ribbon controls offer. Figure 17.1 shows the default Page Layout tab with a good selection of control types. A brief description of some of the controls is listed below:
There are several other controls offered by the Ribbon, including the ToggleButton, Gallery, EditBox, dynamicMenu, and Label. Some of these controls are used in this chapter. To learn more about these and the other controls, visit Microsoft’s website at https://msdn.microsoft.com/en-us/library/bb386089.aspx.
Excel provides a couple of ways for you to add your macros to the Ribbon. These methods don’t give you the flexibility that creating a custom Ribbon does, but what they lack in customization, they make up for in simplicity.
The simplest way to use the Ribbon to execute your code is to add your macro to a custom group using Excel’s Customize Ribbon interface. In a new workbook, insert a Module and add the following simple procedure:
Public Sub HelloWorld() MsgBox"Hello World!" End Sub
Return to Excel, right click anywhere on the Ribbon, and choose Customize the Ribbon to display the Customize Ribbon tab in the Excel Options dialog box. The Customize Ribbon tab primarily consists of two lists. The list on the left contains all the possible commands, and the list on the right shows what the Ribbon currently looks like.
At the top of these lists are drop-down boxes that allow you to filter them, making the command you’re looking for easier to find. From the drop-down above the commands list, choose Macros as shown in Figure 17.2. Now the left list shows all the macros that are available to add to the Ribbon, including the HelloWorld procedure you just created.
You can’t add your macro to just anywhere on the Ribbon. Excel prevents you from changing its built-in groups. To add your macro, you must create a custom group. Follow these steps to add the HelloWorld procedure to a custom group on the Home tab.
The Home tab now contains a custom group called MyGroup, and that group contains one control labelled Hello World. Figure 17.4 shows the new control and the message box that’s displayed when it’s clicked.
Another method for accessing your macros is to add them to the Quick Access Toolbar (QAT). The QAT is a list of buttons that’s always visible regardless of which tab is showing on the Ribbon. By default, the QAT is above the tabs on the Ribbon, but it can also be shown below the Ribbon. If you prefer to show the QAT below the Ribbon, click the small down arrow on the right of the QAT and choose Show Below the Ribbon from the menu.
By default, the QAT shows the Save, Undo, and Redo commands. In this example, we’ll add the HelloWorld procedure from the preceding section to the QAT. The steps are similar to adding a button to the Ribbon.
Click the QAT down arrow and choose More Commands from the menu to display the Quick Access Toolbar tab of the Excel Options dialog. Note how similar this tab is to the Customize Ribbon tab from the preceding section. It has a list of commands on the left and the current state of the QAT on the right.
Next, select Macros from the drop-down box above the left list. The HelloWorld procedure now appears in the list. Select HelloWorld from the left list and click the Add>> button to add it to the QAT (see Figure 17.5). Unlike customizing the Ribbon, there is no Rename button. To customize a QAT button, click the Modify button to choose an icon and change the name. The QAT doesn’t actually display names. Changing Display Name in the Modify Button dialog changes what’s shown in the tooltip when you hover over the button.
When you return to Excel’s main window, the QAT will include a fourth button that executes your HelloWorld procedure. Figure 17.6 shows the QAT below the Ribbon and the results of clicking the new button.
Now that you have a custom button on both the Ribbon and the QAT, you can easily execute the HelloWorld procedure. When you save and close the workbook that contains HelloWorld, the buttons on the Ribbon and QAT are still there. If you click either of those buttons when the workbook is closed, Excel will attempt to open the workbook. If Excel can’t find it because you moved or renamed the workbook, you get a message that Excel can’t find your macro (see Figure 17.7).
One way to prevent this message is include your macro in an add-in that’s always loaded. See Chapter 16 for how to create an add-in. If you only want the buttons to appear when the workbook is opened or you want to use Ribbon controls other than the Button control, you have to create a custom Ribbon in your workbook.
You can’t perform any Ribbon modifications using VBA. Rather, you must write RibbonX code and insert the code into the workbook file — outside of Excel. You can, however, create VBA macros that are executed when a custom Ribbon control is activated.
RibbonX code is Extensible Markup Language (XML) that describes the controls, where on the Ribbon they’re displayed, what they look like, and what happens when they’re activated. This book only covers a small portion of RibbonX — the topic is complex enough to be the subject of an entire book.
This section contains a step-by-step walkthrough that will create two controls in a custom group on the Data tab of the Ribbon. You’ll use the Custom UI Editor for Microsoft Office, an application created by Microsoft, to insert the XML for the new Ribbon into a workbook.
Follow these steps to create a workbook that contains RibbonX code that modifies the Ribbon:
It’s important to understand that the Ribbon modification is document-specific. In other words, the new Ribbon group is displayed only when the workbook that contains the RibbonX code is the active workbook. This is a major departure from how UI modifications worked in versions before Excel 2007.
The RibbonX code used in this example is XML. Excel can read this XML and convert it into UI elements, like tabs, groups, and buttons. XML consists of data between opening and closing tags (or, in some cases, within self-closing tags). The first line defines the schema in a customUI tag — this tells Excel how to read the XML. The last line is the closing tag for the customUI tag.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> </customUI>
Everything between these two tags is interpreted as RibbonX code by Excel. The next line, the ribbon tag, defines that you want to work with the Ribbon. Its closing tag is the second to last line. The XML is hierarchical, just like the Ribbon. You can see in Figure 17.8 that the button tags are contained in the group tag, the group tag is contained in a tab tag, the tab tag is contained in the tabs tag, and the tabs tag is contained in the ribbon tag.
Tags also contain attributes. The tab tag contains an idMso attribute that tells Excel which tab to use.
<tab idMso="TabData">
Each built-in tab and group has a unqiue idMso. In this example, TabData tells Excel that you want to work in the built-in Data tab.
Custom elements, like the group and button tags, use the id attribute rather than idMso. You can use any value for the id attribute, such as Group1 and Button1 in this example, as long as it’s unique. The following lists the attributes used in the example and a brief description of what they do:
A complete list of attributes for all the UI elements would be too long to list. You can find many examples of RibbonX on the web and change them to suit your needs.
VBA responds to user actions using Events (see Chapter 6). The Ribbon uses a different technique: callback procedures. The buttons in this example are tied to the VBA code via the OnAction attribute. Most controls have an OnAction attribute, and the action is different for different controls. A button’s action is a click, but a check box’s action is a check or uncheck.
Most attributes have a corresponding callback attribute, generally with a get prefix. For example, the label attribute sets the text that displays for the control. There is also a getLabel attribute. You set the getLabel attribute to the name of a VBA procedure that determines what text is displayed. I’ll discuss dynamic controls later in this chapter, but for now understand that callback procedures are not limited to OnAction.
Both VBA procedures in this example contain an argument named control, which is an IRibbonControl object. This object has three properties, which you can access in your VBA code:
Context: A handle to the active window containing the Ribbon that triggered the callback. For example, use the following expression to get the name of the workbook that contains the RibbonX code:
control.Context.Caption
The VBA callback procedures can be as simple or as complex as necessary.
In Step 6 of the preceding instructions, you inserted a customUI part for Office 2007. This choice makes the workbook compatible with Excel 2007 and later. The other option on the Insert menu is Office 2010 Custom UI Part. If you put the RibbonX code in an Office 2010 Custom UI part, the workbook won’t be compatible with Excel 2007.
Microsoft makes new Custom UI Parts available when it changes the Ribbon in a way that requires one. Don’t look for a 2016 or 2013 Custom UI Part. Those versions of Office continue to use the Office 2010 Custom UI Part.
This section contains another example of using RibbonX to modify the UI. This workbook creates a new group on the Page Layout tab and adds a check box control that toggles the display of page breaks.
This example is a bit tricky because it requires that the new Ribbon control be in synch with the active sheet. For example, if you activate a worksheet that doesn’t display page breaks, the check box control should be in its deselected state. If you activate a worksheet that displays page breaks, the control should be selected. Furthermore, page breaks aren’t relevant for a chart sheet, so the control should be disabled if you activate a chart sheet.
The RibbonX code that adds a new group (with a CheckBox control) to the Page Layout tab follows:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Initialize"> <ribbon> <tabs> <tab idMso="TabPageLayoutExcel"> <group id="FileName_Group1" label="Custom"> <checkBox id="FileName_Checkbox1" label="Page Breaks" onAction="TogglePageBreakDisplay" getPressed="GetPressed" getEnabled="GetEnabled"/> </group> </tab> </tabs> </ribbon> </customUI>
This RibbonX code references four VBA callback procedures (each of which is described later):
Figure 17.12 shows the new control, placed in a group named Custom.
The CustomUI tag includes an onLoad parameter, which specifies the Initialize VBA callback procedure, as follows (this code is in a standard VBA module):
Public MyRibbon As IRibbonUI Sub Initialize(Ribbon As IRibbonUI) ' Executed when the workbook loads Set MyRibbon = Ribbon End Sub
The Initialize procedure creates an IRibbonUI object named MyRibbon. Note that MyRibbon is a Public variable, so it’s accessible from other procedures in the module.
I created a simple event procedure that is executed whenever a worksheet is activated. This procedure, which is located in the ThisWorkbook code module, calls the CheckPageBreakDisplay procedure:
Private Sub Workbook_SheetActivate(ByVal Sh As Object) CheckPageBreakDisplay End Sub
The CheckPageBreakDisplay procedure invalidates the check box control. In other words, it destroys any data associated with that control.
Sub CheckPageBreakDisplay() ' Executed when a sheet is activated MyRibbon.InvalidateControl ("Checkbox1") End Sub
When a control is invalidated, the GetPressed and GetEnabled procedures are called:
Sub GetPressed(control As IRibbonControl, ByRef returnedVal) ' Executed when the control is invalidated On Error Resume Next returnedVal = ActiveSheet.DisplayPageBreaks End Sub Sub GetEnabled(control As IRibbonControl, ByRef returnedVal) ' Executed when the control is invalidated returnedVal = TypeName(ActiveSheet) ="Worksheet" End Sub
Note that the returnedVal argument is passed ByRef. This means your code is able to change the value — and that’s exactly what happens. In the GetPressed procedure, the returnedVal variable is set to the status of the DisplayPageBreaks property of the active sheet. The result is that the control’s Pressed parameter is True if page breaks are displayed (and the control is selected). Otherwise, the control isn’t selected.
In the GetEnabled procedure, the returnedVal variable is set to True if the active sheet is a worksheet (as opposed to a chart sheet). Therefore, the control is enabled only when the active sheet is a worksheet.
The only other VBA procedure is the onAction procedure, TogglePageBreakDisplay, which is executed when the user selects or deselects the check box:
Sub TogglePageBreakDisplay(control As IRibbonControl, pressed As Boolean) ' Executed when check box is clicked On Error Resume Next ActiveSheet.DisplayPageBreaks = pressed End Sub
This pressed argument is True if the user selects the check box and False if the user deselects the check box. The code sets the DisplayPageBreaks property accordingly.
Figure 17.13 shows a custom Ribbon tab (My Stuff) with five groups of controls. In this section, I briefly describe the RibbonX code and the VBA callback procedures.
The RibbonX code that creates the new tab is:
<ribbon> <tabs> <tab id="FileName_CustomTab" label="My Stuff"> </tab> </tabs> </ribbon>
The code in the ribbon controls demo.xlsm example creates five groups on the My Stuff tab. Here’s the code that creates the five groups:
<group id="FileName_grpInfo" label="Information"> </group> <group id="FileName_grpMath" label="Math"> </group> <group id="FileName_grpFeedback" label="Feedback"> </group> <group id="FileName_grpBuiltIn" label="Built In Stuff"> </group> <group id="FileName_grpGalleries" label="Galleries"> </group>
These pairs of <group> and </group> tags are located between the <tab> and </tab> tags that create the new tab.
Following is the RibbonX code that creates the controls in the first group (Information). Figure 17.14 shows these controls on the Ribbon.
<group id="FileName_grpInfo" label="Information"> <labelControl id="FileName_lblUser" getLabel="getlblUser"/> <labelControl id="FileName_lblDate" getLabel="getlblDate"/> </group>
Two label controls each have an associated VBA callback procedure (named getlblUser and getlblDate). These procedures are:
Sub getlblUser(control As IRibbonControl, ByRef returnedVal) returnedVal ="Hello" & Application.UserName End Sub Sub getlblDate(control As IRibbonControl, ByRef returnedVal) returnedVal ="Today is" & Date End Sub
When the RibbonX code is loaded, these two procedures are executed, and the captions of the label controls are dynamically updated with the user’s name and the date.
Figure 17.15 shows the controls in the second group, labeled Math.
The RibbonX for the Math group follows:
<group id="FileName_grpMath" label="Math"> <editBox id="FileName_ebxSquare" showLabel="true" label="The square of" onChange="ebxSquare_Change"/> <labelControl id="FileName_lblSquare" getLabel="getlblSquare"/> <separator id="FileName_sepMath"/> <button id="FileName_btnCalc" label="Calculator" size="large" onAction="ShowCalculator" imageMso="Calculator"/> </group>
The editBox control has an onChange callback procedure named ebxSquare_Change, which updates a label to display the square of the number entered. The ebxSquare_Change procedure is:
Private sq As Double Sub ebxSquare_Change(control As IRibbonControl, text As String) sq = Val(text) ^ 2 MyRibbon.Invalidate End Sub
The label control showing the result is updated when MyRibbon is invalidated. Invalidating the Ribbon causes all the controls to reinitialize. This procedure sets the sq variable to the square of the number entered, which is used by the label in the next procedure.
The label control has a getLabel callback procedure named getlblSquare. When the Ribbon is invalidated, this procedure is run. For an example of how to invalidate the Ribbon, see the “Adding a check box to an existing tab” section earlier in this chapter.
Sub getlblSquare(control As IRibbonControl, ByRef returnedVal) returnedVal ="is" & sq End Sub
The separator control, sepMath, adds a vertical line to separate the squaring controls with the last control. The last control in this group is a simple button. Its onAction parameter executes a VBA procedure named ShowCalculator — which uses the VBA Shell function to display the Windows calculator:
Sub ShowCalculator(control As IRibbonControl) On Error Resume Next Shell"calc.exe", vbNormalFocus If Err.Number <> 0 Then MsgBox"Can't start calc.exe" End Sub
Figure 17.16 shows the controls in the third group, labeled Feedback.
The RibbonX code for the second group is as follows:
<group id="FileName_grpFeedback" label="Feedback"> <toggleButton id="FileName_ToggleButton1" size="large" imageMso="FileManageMenu" label="Toggle Me" onAction="ToggleButton1_Click" /> <checkBox id="FileName_Checkbox1" label="Checkbox" onAction="Checkbox1_Change"/> <comboBox id="FileName_Combo1" label="Month" onChange="Combo1_Change"> <item id="FileName_Month1" label="January" /> <item id="FileName_Month2" label="February"/> <item id="FileName_Month3" label="March"/> <item id="FileName_Month4" label="April"/> <item id="FileName_Month5" label="May"/> <item id="FileName_Month6" label="June"/> <item id="FileName_Month7" label="July"/> <item id="FileName_Month8" label="August"/> <item id="FileName_Month9" label="September"/> <item id="FileName_Month10" label="October"/> <item id="FileName_Month11" label="November"/> <item id="FileName_Month12" label="December"/> </comboBox> </group>
The group contains a toggleButton, a checkBox, and a comboBox control. These controls are straightforward. Each has an associated callback procedure that simply displays the status of the control:
Sub ToggleButton1_Click(control As IRibbonControl, pressed As Boolean) MsgBox"Toggle value:" & pressed End Sub Sub Checkbox1_Change(control As IRibbonControl, pressed As Boolean) MsgBox"Checkbox value:" & pressed End Sub Sub Combo1_Change(control As IRibbonControl, text As String) MsgBox text End Sub
The controls in the fourth group consist of built-in controls, as shown in Figure 17.17. To include a built-in control in a custom group, you just need to know its name (the idMso parameter).
The RibbonX code is:
<group id="FileName_grpBuiltIn" label="Built In Stuff"> <control idMso="Copy" label="Copy" /> <control idMso="Paste" label="Paste" enabled="true" /> <control idMso="WindowSwitchWindowsMenuExcel" label="Switch Window" /> <control idMso="Italic" /> <control idMso="Bold" /> <control idMso="FileOpen" /> </group>
These controls don’t have callback procedures because they perform the standard action.
Figure 17.18 shows the final group of controls, which consists of two galleries.
The RibbonX code for these two gallery controls is:
<group id="FileName_grpGalleries" label="Galleries"> <gallery id="FileName_galAppointments" imageMso="ViewAppointmentInCalendar" label="Pick a Month:" columns="2" rows="6" onAction="MonthSelected"> <item id="FileName_January" label="January" imageMso="QuerySelectQueryType"/> <item id="FileName_February" label="February" imageMso="QuerySelectQueryType"/> <item id="FileName_March" label="March" imageMso="QuerySelectQueryType"/> <item id="FileName_April" label="April" imageMso="QuerySelectQueryType"/> <item id="FileName_May" label="May" imageMso="QuerySelectQueryType"/> <item id="FileName_June" label="June" imageMso="QuerySelectQueryType"/> <item id="FileName_July" label="July" imageMso="QuerySelectQueryType"/> <item id="FileName_August" label="August" imageMso="QuerySelectQueryType"/> <item id="FileName_September" label="September" imageMso="QuerySelectQueryType"/> <item id="FileName_October" label="October" imageMso="QuerySelectQueryType"/> <item id="FileName_November" label="November" imageMso="QuerySelectQueryType"/> <item id="FileName_December" label="December" imageMso="QuerySelectQueryType"/> <button id="FileName_Today" label="Today..." imageMso="ViewAppointmentInCalendar" onAction="ShowToday"/> </gallery> <gallery id="FileName_galPictures" label="Sample Pictures" columns="4" itemWidth="100" itemHeight="125" imageMso="Camera" onAction="galPictures_Click" getItemCount="galPictures_ItemCount" getItemImage="galPictures_ItemImage" size="large"/> </group>
Figure 17.19 shows the first gallery, a list of month names in two columns.
The onAction parameter executes the MonthSelected callback procedure, which displays the selected month (which is stored as the id parameter):
Sub MonthSelected(control As IRibbonControl, _ id As String, index As Integer) MsgBox"You selected" & id End Sub
The Pick a Month gallery also contains a button control with its own callback procedure (labeled Today) at the bottom:
Sub ShowToday(control As IRibbonControl) MsgBox"Today is" & Date End Sub
The second gallery, shown in Figure 17.20, displays eight images, saved as JPG images.
These images are stored in a folder named demopics in the same folder as the workbook. The gallery uses the getItemImage callback procedure to fill the images. When the Ribbon is first loaded, the onLoad callback procedure, shown below, creates an array of image files in the directory, counts them, and stores the information in module-level variables, aFiles() and ImgCnt, so the other callback procedures can read them.
Private ImgCnt As Long Private aFiles() As String Private sPath As String Sub ribbonLoaded(ribbon As IRibbonUI) Set MyRibbon = ribbon Dim sFile As String sPath = ThisWorkbook.Path &"demopics" sFile = Dir(sPath &"*.jpg") Do While Len(sFile) > 0 ImgCnt = ImgCnt + 1 ReDim Preserve aFiles(1 To ImgCnt) aFiles(ImgCnt) = sFile sFile = Dir Loop End Sub
When the gallery is clicked, the getItemCount callback procedure, named galPictures_ItemCount, reads the ImgCnt variable, and galPictures_ItemImage is called that many times. Each time it’s called, the index argument is increased by one. VBA’s LoadPicture function is used to insert the images into the gallery.
Sub galPictures_ItemCount(control As IRibbonControl, _ ByRef returnedVal) returnedVal = ImgCnt End Sub Sub galPictures_ItemImage(control As IRibbonControl, _ index As Integer, ByRef returnedVal) Set returnedVal = LoadPicture(sPath & aFiles(index + 1)) End Sub
Note that dynamic controls, like galleries, start their index at zero.
One of the most interesting Ribbon controls is the dynamicMenu control. This control lets your VBA code feed XML data into the control — which provides the basis for menus that change based on context.
Setting up a dynamicMenu control isn’t a simple task, but this control probably offers the most flexibility in terms of using VBA to modify the Ribbon dynamically.
I created a simple dynamicMenu control demo that displays a different menu for each of the three worksheets in a workbook. Figure 17.21 shows the menu that appears when Sheet1 is active. When a sheet is activated, a VBA procedure sends XML code specific to the sheet. For this demo, I stored the XML code directly in the worksheets to make it easier to read. Alternatively, the XML markup can be stored as a string variable in your code.
The RibbonX code that creates the new tab, the new group, and the dynamicMenu control follows:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="ribbonLoaded"> <ribbon> <tabs> <tab id="FileName_CustomTab" label="Dynamic"> <group id="FileName_group1" label="Dynamic Menu Demo"> <dynamicMenu id="FileName_DynamicMenu" getContent="dynamicMenuContent" imageMso="RegionLayoutMenu" size ="large" label="Sheet-Specific Menu"/> </group> </tab> </tabs> </ribbon> </customUI>
This example needs a way to invalidate the Ribbon whenever the user activates a new sheet. I use the same method I used for the page break display example earlier in this chapter (see “Adding a check box to an existing tab”): I declared a Public variable, MyRibbon, of type IRibbonUI. I used a Workbook_SheetActivate procedure that called the UpdateDynamicRibbon procedure whenever a new sheet is activated:
Sub UpdateDynamicRibbon() ' Invalidate the Ribbon to force a call to dynamicMenuContent On Error Resume Next MyRibbon.Invalidate If Err.Number <> 0 Then MsgBox"Lost the Ribbon object. Save and reload." End If End Sub
The UpdateDynamicRibbon procedure invalidates the MyRibbon object, which forces a call to the VBA callback procedure named dynamicMenuContent (a procedure referenced by the getContent parameter in the RibbonX code). Note the error-handling code. Some edits to your VBA code destroy the MyRibbon object, which is created when the workbook is opened. Attempting to invalidate an object that doesn’t exist causes an error, and the message box informs the user that the workbook must be saved and reopened.
The dynamicMenuContent procedure follows. This procedure loops through the cells in column A of the active sheet, reads the XML code, and stores it in a variable named XMLcode. When all the XML has been appended, it’s passed to the returnedVal argument. The net effect is that the dynamicMenu control has new code, so it displays a different set of menu options.
Sub dynamicMenuContent(control As IRibbonControl, _ ByRef returnedVal) Dim r As Long Dim XMLcode As String ' Read the XML markup from the active sheet For r = 1 To Application.CountA(Range("A:A")) XMLcode = XMLcode & ActiveSheet.Cells(r, 1).Value &"" Next r returnedVal = XMLcode End Sub
I conclude this section with some additional points to keep in mind as you explore the wonderful world of Excel Ribbon customization:
You can, however, hide tabs. The RibbonX code that follows hides three tabs:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab idMso="TabPageLayoutExcel" visible="false" /> <tab idMso="TabData" visible="false" /> <tab idMso="TabReview" visible="false" /> </tabs> </ribbon> </customUI>
You can also hide groups within a tab. Here’s RibbonX code that hides four groups on the Insert tab:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab idMso="TabInsert"> <group idMso="GroupInsertTablesExcel" visible="false" /> <group idMso="GroupInsertIllustrations" visible="false" /> <group idMso="GroupInsertLinks" visible="false" /> <group idMso="GroupInsertText" visible="false" /> </tab> </tabs> </ribbon> </customUI>
You can assign your own macro to a built-in control. This is known as repurposing the control. The RibbonX code that follows intercepts three built-in commands:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <commands> <command idMso="FileSave" onAction="mySave"/> <command idMso="FilePrint" onAction="myPrint"/> <command idMso="FilePrintQuick" onAction="myPrint"/> </commands> </customUI>
You can also write RibbonX code to disable one or more built-in controls. The code that follows disables the Insert ClipArt command:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <commands> <command idMso="ClipArtInsert" enabled="false"/> </commands> </customUI>
As you’ve seen in this chapter, the typical workflow when working with the Ribbon is to create the RibbonX code and use callback procedures to respond to user actions. There are other ways to interact with the Ribbon with VBA, but they are limited.
Following is a list of what you can do with the Ribbon using VBA:
All told, Excel has more than 1,700 Ribbon controls. Every Ribbon control has a name, and you use that name when you work with the control using VBA.
For example, the statement that follows displays a message box that shows the Enabled status of the ViewCustomViews control. (This control is located in the View ➜ Workbook Views group.)
MsgBox Application.CommandBars.GetEnabledMso("ViewCustomViews")
Normally, this control is enabled. But if the workbook contains a table (created by choosing Insert ➜ Tables ➜ Table), the ViewCustomViews control is disabled. In other words, a workbook can use either the Custom Views feature or the Tables feature — but not both.
Determining the name of a particular control is a manual task. First, display the Customize Ribbon tab of the Excel Options dialog box. Locate the control in the list box on the left and then hover the mouse pointer over the item. The control’s name appears in a pop-up screen tip, in parentheses (see Figure 17.22).
Unfortunately, it’s not possible to write VBA code to loop through all the controls on the Ribbon and display a list of their names.
The preceding section provided an example of using the GetEnabledMso method of the CommandBars object. Following is a list of all methods relevant to working with the Ribbon via the CommandBars object. All these methods take one argument: idMso, which is a String data type and represents the name of the command. You must know the name — using index numbers is not possible.
The VBA statement that follows toggles the Selection task pane (a feature introduced in Excel 2007 that facilitates selecting objects on a worksheet):
Application.CommandBars.ExecuteMso"SelectionPane"
The following statement displays the Paste Special dialog box (and will display an error message if the Windows Clipboard is empty):
Application.CommandBars.ExecuteMso"PasteSpecialDialog"
Here’s a command that tells you whether the formula bar is visible (it corresponds to the state of the Formula Bar control in the View ➜ Show group):
MsgBox Application.CommandBars.GetPressedMso"ViewFormulaBar"
To toggle the formula bar, use this statement:
Application.CommandBars.ExecuteMso"ViewFormulaBar"
To make sure the formula bar is visible, use this code:
With Application.CommandBars If Not .GetPressedMso("ViewFormulaBar") Then .ExecuteMso"ViewFormulaBar" End With
To make sure the formula bar is not visible, use this code:
With Application.CommandBars If .GetPressedMso("ViewFormulaBar") Then .ExecuteMso"ViewFormulaBar" End With
Or don’t bother with the Ribbon and set the DisplayFormulaBar property of the Application object to either True or False. This statement displays the formula bar (or has no effect if the formula bar is already visible):
Application.DisplayFormulaBar = True
The statement that follows displays True if the Merge & Center control is enabled. (This control is disabled if the sheet is protected or if the active cell is in a table.)
MsgBox Application.CommandBars.GetEnabledMso("MergeCenter")
The following VBA code adds an ActiveX Image control to the active worksheet and uses the GetImageMso method to display the binoculars icon from the Find & Select control in the Home ➜ Editing group:
Sub ImageOnSheet() Dim MyImage As OLEObject Set MyImage = ActiveSheet.OLEObjects.Add _ (ClassType:="Forms.Image.1", _ Left:=50, _ Top:=50) With MyImage.Object .AutoSize = True .BorderStyle = 0 .Picture = Application.CommandBars. _ GetImageMso("FindDialog", 32, 32) End With End Sub
To display the Ribbon icon in an Image control (named Image1) on a UserForm, use this procedure:
Private Sub UserForm_Initialize() With Image1 .Picture = Application.CommandBars.GetImageMso _ ("FindDialog", 32, 32) .AutoSize = True End With End Sub
Microsoft provides no direct way to activate a Ribbon tab from VBA. But if you really need to do so, using SendKeys is your only option. The SendKeys method simulates keystrokes. The keystrokes required to activate the Home tab are Alt+H. These keystrokes display the keytips in the Ribbon. To hide the keytips, press F6. Using this information, the following statement sends the keystrokes required to activate the Home tab:
Application.SendKeys"%h{F6}"
To avoid the display of keytips, turn off screen updating:
Application.ScreenUpdating = False Application.SendKeys"%h{F6}" Application.ScreenUpdateing=True
If you find that customizing the Ribbon is just too much work, you may be content to create a simple custom toolbar using the pre–Excel 2007 CommandBar object. This technique is perfectly suitable for any workbook that only you will be using and is an easy way to provide quick access to a number of macros.
In this section, I provide boilerplate code that you can adapt as needed. I don’t offer much in the way of explanation. For more information about CommandBar objects, search the web or consult the Excel 2003 edition of this book. CommandBar objects can be much more powerful than the example presented here.
If you decide to create a toolbar for use in Excel 2007 or later, be aware of the following limitations:
The code in this section assumes that you have a workbook with two macros (named Macro1 and Macro2). It also assumes that you want the toolbar to be created when the workbook is opened and deleted when the workbook is closed.
In the ThisWorkbook code module, enter the following procedures. The first one calls the procedure that creates the toolbar when the workbook is opened. The second calls the procedure to delete the toolbar when the workbook is closed:
Private Sub Workbook_Open() Call CreateToolbar End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteToolbar End Sub
The CreateToolbar procedure follows:
Const TOOLBARNAME As String ="MyToolbar" Sub CreateToolbar() Dim TBar As CommandBar Dim Btn As CommandBarButton ' Delete existing toolbar (if it exists) On Error Resume Next CommandBars(TOOLBARNAME).Delete On Error GoTo 0 ' Create toolbar Set TBar = CommandBars.Add With TBar .Name = TOOLBARNAME .Visible = True End With ' Add a button Set Btn = TBar.Controls.Add(Type:=msoControlButton) With Btn .FaceId = 300 .OnAction ="Macro1" .Caption ="Macro1 Tooltip goes here" End With ' Add another button Set Btn = TBar.Controls.Add(Type:=msoControlButton) With Btn .FaceId = 25 .OnAction ="Macro2" .Caption ="Macro2 Tooltip goes here" End With End Sub
Figure 17.23 shows the two-button toolbar.
I use a module-level constant, TOOLBAR, which stores the toolbar’s name. This name is used also in the DeleteToolbar procedure, so using a constant ensures that both procedures work with the same name.
The procedure starts by deleting the existing toolbar that has the same name (if such a toolbar exists). Including this statement is useful during development and also eliminates the error you get if you attempt to create a toolbar using a duplicate name.
The toolbar is created by using the Add method of the CommandBars object. The two buttons are added by using the Add method of the Controls object. Each button has three properties:
When the workbook is closed, the Workbook_BeforeClose event procedure fires, which calls DeleteToolbar:
Sub DeleteToolbar() On Error Resume Next CommandBars(TOOLBARNAME).Delete On Error GoTo 0 End Sub
Note that the toolbar is not deleted from workbook windows that were opened after the toolbar was created.
3.136.234.127