Microsoft Excel

Chart Pattern Fills

Today’s Author: Eric Patterson, a program manager on the Excel team.  Eric is going to discuss applying pattern fills to chart data points and includes a sample add-in for this purpose.

Overview

In Excel 2007, the interface for applying Pattern fills to chart elements has been removed in favor of the interface for applying Picture and Texture fills.  Charts formatted with pattern fills in previous versions of Excel will retain and display the pattern fills when opened in Excel 2007.

Here is an example of a chart with pattern fills applied:

While the interface for applying Pattern fills has been removed, support is still available via Excel’s object model.  The FillFormat.Patterned Method can be used to apply pattern fills to chart elements, such as datapoints in a series.  I will describe how the object model can be used to create the chart shown above.  A sample add-in with an interface for applying pattern fills to chart elements also accompanies this blog entry. 

The sample add-in with RibbonX and VBA code is here: http://officeblogs.net/excel/PatternUI.zip

Applying Patterns using the Visual Basic Immediate Window

To demonstrate how pattern formatting can be applied, let’s start with a simple Pie Chart.  After creating the chart, click on the Q1 Pie slice once to select the series, then a second time to select the Q1 data point.  It should look like this:

While the Q1 datapoint is selected, launch the Visual Basic Editor by pressing ALT+F11.  In the Visual Basic Editor, press CTRL+G to display and move to the Immediate Window.

In the Immediate Window, type the following and press enter:   selection.fill.patterned (4)

This will apply the 4th indexed pattern to the selected chart element.  At this point the chart will be updated to look like this:

Repeat the Process

To finish the chart, the above process can be repeated for the other 3 data points.  Select them in turn, switch to the VBE and type the Selection.Fill.Patterned command for each of them.  The indexes of the patterns that I used are 26, 15 and 12 for Q2, Q3 and Q4 respectively.  At this point, the chart will look like this:

Sample Add-in

Next I will demonstrate how an add-in can be created to quickly apply Pattern Fills using a gallery control in Excel.

THE RIBBONX

We’ll start off with the RibbonX code.  If you aren’t familiar with RibbonX, the short explanation is that it’s the XML that defines what the Ribbon will look like.  Specifically for us, the RibbonX will define where we want to put our gallery, and the items in the gallery (including pictures).  For more detailed information on RibbonX, see these links:

Office Fluent Ribbon Developer Portal
http://msdn2.microsoft.com/en-us/office/aa905530.aspx

Tools and Utilities for the 2007 Microsoft Office System
http://msdn2.microsoft.com/en-us/office/aa905356.aspx

The RibbonX we will create will live in the document and travel with it.  We will have to use a tool to get the RibbonX in the document.  The one I’ll use for this example is called the “Office 2007 Custom UI Editor”.  You can download it freely from here:

http://openxmldeveloper.org/articles/CustomUIeditor.aspx

After you download and install the tool, boot it up and open the “PatternUI.xlam” document that accompanies this blog entry.  You should see:

To create this, I started by using the Inset Icons command in the UI Editor and selected pictures of each of the pattern fills that I had previously created.  I made sure to name the files such that they matched the indexes for the patterns in the object model.

Once the icons were added, I then typed in the Ribbon XML below that specifies which chart tab (ChartToolsFormat) and what the name of the group should be (Patterns).  Within the Patterns group in the ribbon, I specified the items to be included in the gallery that corresponded to the icons that I had previously added.  I chose to size the gallery to 8 columns and 7 rows.

Here is the bulk of the XML.  The Gallery elements were repeated for all 54 elements:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <ribbon>
  <contextualTabs>
   <tabSet idMso="TabSetChartTools" >
    <tab idMso="TabChartToolsFormat" >
     <group id="customGroup" label="Patterns" insertAfterMso="GroupShapeStyles">
      <gallery id="PatternGallery" image="Pattern54" size ="large" columns="8" rows="7" onAction="InsertPattern" >
         <item id="Pattern1" image="Pattern1"/>
         <item id="Pattern2" image="Pattern2"/>
         <item id="Pattern3" image="Pattern3"/>
       .
       .
       .

After entering all of the Ribbon XML, I used the Generate Callbacks command in the UI Editor, which produced this callback:

This callback can then be entered into a module in the VBE.

VBA CODE

In the code module in VBA, just 2 lines of code need to be added to the callback.  The first line is very simple error checking in case you attempt to use the gallery to apply formatting when an invalid object is selected.  The second line is the same command that we typed into the Immediate pane above.  In this instance, the argument for the Patterned Method is “index+1”.  Index is the gallery index that is returned.  This index is zero based so the “+1” is used to ensure that the gallery index matches the pattern index.

'Callback for PatternGallery onAction
Sub InsertPattern(control As IRibbonControl, id As String, index As Integer)
    On Error Resume Next
    Selection.Fill.Patterned (index + 1)
End Sub

Make this an Add-in

The final step is to save this file as an addin using the Save As command.

The Finished Product

After defining the RibbonX and VBA code, opening up the add-in will show a pattern dropdown on the Chart Format tab when a chart is selected.  To use it, you select a chart series or data point and then click on one of the gallery entries.

Pattern fills are primarily used for Black and White printing.  If you are using pattern fills for this or other purposes we would like to hear from you.