Office Home And Stude/nt 2010 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,Office Home And Stude/nt, 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:
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,Office Home And Stude/nt 2010, 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 ############## 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 ##############, and the items in the ############## (including pictures). For more detailed information on RibbonX, see these links:
Office Fluent Ribbon Developer Portal
Tools and Utilities for the 2007 Microsoft Office System
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:
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,Windows 7 Discount, 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,Office 2007 Professional Plus Key, I specified the items to be included in the ############## that corresponded to the icons that I had previously added. I chose to size the ############## to 8 columns and 7 rows.
Here is the bulk of the XML. The ############## elements were repeated for all 54 elements:
<customUI xmlns="">
<ribbon>
<contextualTabs>
<tabSet idMso="TabSetChartTools" >
<tab idMso="TabChartToolsFormat" >
<group id="customGroup" label="Patterns" insertAfterMso="GroupShapeStyles">
<############## id="Pattern##############" 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,Office 2010 Professional Key, 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 ############## 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 ############## index that is returned. This index is zero based so the “+1” is used to ensure that the ############## index matches the pattern index.
'Callback for Pattern############## 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 ############## 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. <div