Child Objects commonly used with Chart Elements: Border Object, ChartFillFormat Object, Interior Object, Font Object
Contents:
You can use the Border object, ChartFillFormat Object, Interior Object & Font Object with several Chart Elements to customize the border, fill, interior and font settings. Each object has multiple properties to customize it. Border object is used to manipulate a chart element’s border, the ChartFillFormat object is used to manipulate fill formatting for chart elements, the Interior object is used to manipulate the chart element’s interior (inside area), and the Font object enables you to manipulate the font attributes viz. font name, font size, font color, … for all text values.
Border Object – objChartElement.Border – use the Border Property to return a Border object, to manipulate a chart element’s border.
| Commonly used Properties of the Border Object, with Charts: | ||
| Property | Syntax | Description |
| Color Property | objBorder.Color | sets or returns the primary color for the border – create a color value by using the RGB function |
|
Sheets(“Sheet1”).ChartObjects(1).Chart.Axes(xlCategory).Border.Color = RGB(0, 255, 0)
|
||
| ColorIndex Property | objBorder.ColorIndex | sets or returns the border color, wherein an index value (in the existing color palette) or one of the XlColorIndex constants (xlColorIndexAutomatic, xlColorIndexNone) are used to specify color |
| ‘set plot area’s border color to green Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Border.ColorIndex = 4 |
||
| ‘consider series 1 line color, for line chart: With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1).Border If .ColorIndex = xlColorIndexAutomatic Then MsgBox “Automatic” Else MsgBox “Non-Automatic” End If End With |
||
| LineStyle Property | objBorder.LineStyle | sets or returns the line style for the border, using the values xlGray25, xlGray50, xlGray75, or xlAutomatic or using the XlLineStyle Enumeration viz. xlContinuous (value 1) – continuous line; xlDashDot (value 4) – alternate dash & dot; xlDashDotDot (value 5) – dash-dot-dot; xlDash (value -4115) – dashed line; xlDot (value -4118) – dotted line; xlLineStyleNone (value -4142) – no line. Note that the XlLineStyle constants xlDouble & xlSlantDashDot are not applicable for charts. |
| ‘turn on minor gridlines for axis Sheets(“Sheet1”).ChartObjects(1).Chart.Axes(xlValue).HasMinorGridlines = True ‘set line style for minor gridlines Sheets(“Sheet1”).ChartObjects(1).Chart.Axes(xlValue).MinorGridlines.Border.LineStyle = xlDash |
||
| Weight Property | objBorder.Weight | sets or returns the weight for the border, using the XlBorderWeight Enumeration viz. xlHairline (value 1) – hairline / thinnest; xlThin (value 2) – thin; xlMedium (value -4138) – medium; xlThick (value 4) – thick / widest. |
| Sheets(“Sheet1”).ChartObjects(1).Chart.ChartArea.Border.Weight = xlThick | ||
ChartFillFormat Object – use the Fill Property – objChartElement.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements.
| Commonly Used Properties of the ChartFillFormat Object, with Charts: | ||
| Property | Syntax | Description |
| BackColor Property | objChartFillFormat.
BackColor |
Use the BackColor property to set or return a ChartColorFormat object that represents the background fill color of a gradient or patterned fill. Use the RGB value (RGB property of ChartColorFormat object) to return the color; use the SchemeColor index value (SchemeColor property of ChartColorFormat object) in the existing color scheme to set or return the color. |
| ForeColor Property | objChartFillFormat.
ForeColor |
Use the ForeColor property to set or return a ChartColorFormat object that represents the fill color of a solid fill or the foreground fill color of a gradient or patterned fill. Use the RGB value (RGB property of ChartColorFormat object) to return the color; use the SchemeColor index value (SchemeColor property of ChartColorFormat object) in the existing color scheme to set or return the color. To set the interior color for the plot area with a solid fill to Yellow:
Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill.ForeColor.SchemeColor = 6 |
| GradientColorType Property | objChartFillFormat.
GradientColorType |
Refer same property of the the FillFormat object (in the section of ChartFormat object) for explanation. |
| GradientDegree Property | objChartFillFormat.
GradientDegree |
— do — |
| GradientStyle Property | objChartFillFormat.
GradientStyle |
— do — |
| GradientVariant Property | objChartFillFormat.
GradientVariant |
— do — |
| Pattern Property | objChartFillFormat.Pattern | — do — |
| PresetGradientType Property | objChartFillFormat.
PresetGradientType |
— do — |
| PresetTexture Property | objChartFillFormat.
PresetTexture |
— do — |
| TextureType Property | objChartFillFormat.
TextureType |
— do — |
| Type Property | objChartFillFormat.Type | — do — |
| Visible Property | objChartFillFormat.Visible | — do — |
| Commonly Used Methods of the ChartFillFormat Object, with Charts:
|
||
| Method | Syntax | Description |
| OneColorGradient Method | objChartFillFormat.
OneColorGradient(Style, Variant, Degree) |
Refer same method of the the FillFormat object (in the section of ChartFormat object) for explanation. |
| Patterned Method | objChartFillFormat.
Patterned(Pattern) |
— do — |
| PresetGradient Method | objChartFillFormat.
PresetGradient(Style, Variant, PresetGradientType) |
— do — |
| PresetTextured Method | objChartFillFormat.
PresetTextured(PresetTexture) |
— do — |
| Solid Method | objChartFillFormat.Solid | — do — |
| TwoColorGradient Method | objChartFillFormat.
TwoColorGradient(Style, Variant) |
— do — |
| UserTextured Method | objChartFillFormat.
UserTextured(TextureFile) |
— do — |
| UserPicture Method | objChartFillFormat.UserPicture
(PictureFile, PictureFormat, PictureStackUnit, PicturePlacement) |
Sets an image to the specified fill. All arguments are optional. The PictureFile argument is a string value specifying the picture file name. The PictureFormat argument specifies picture display format on a column, bar picture chart, or legend key, per constants defined in XlChartPictureType Enumeration – xlStack (value 2, picture is sized to repeat a maximum of 15 times in the longest stacked bar), xlStackScale (value 3, picture is sized to a specified number of units & repeated the length of the bar), xlStretch (value 1, picture is stretched the full length of the stacked bar). The PictureStackUnit argument specifies the stack or scale unit for the picture (ie. n units per picture) – a double value depending on the PictureFormat argument. The PicturePlacement argument specifies the placement of a user-selected picture on a bar in a 3-D bar or column as per values defined in the XlChartPicturePlacement Enumeration – xlSides (value 1, display on sides), xlEnd (value 2, display on end), xlEndSides (value 3, display on end & sides), xlFront (value 4, display on front), xlFrontSides (value 5, display on front & sides), xlFrontEnd (value 6, display on front & end), xlAllFaces (value 7, display on all faces). |
VBA Codes illustrating ChartFillFormat Object’s Properties & Methods:
With Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill
‘remove fill color from Plot Area
.Visible = msoFalse
‘add fill color to Plot Area
.Visible = msoTrue
‘set a solid fill of uniform color – note that this is the Default & excluding this will not matter (use the Solid method to convert a gradient, textured, patterned or background fill back to a solid fill)
.Solid
‘set the interior color for the plot area to Yellow
.ForeColor.SchemeColor = 6
‘returns 65535 (ForeColor.RGB is read only)
MsgBox .ForeColor.RGB
End With
With Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill
‘set a one-color gradient for the Plot Area Fill, gradient fill style of msoGradientDiagonalUp, gradient variant of 2 & gradient degree of 0.9 (1 is lightest)
.OneColorGradient msoGradientDiagonalUp, 2, 0.9
‘set the interior color for the plot area to Green – for gradient stop #1 (ForeColor)
.ForeColor.SchemeColor = 4
‘returns 1 indicating msoGradientOneColor (ie. one-color gradient)
MsgBox .GradientColorType
‘returns 3 indicating msoGradientDiagonalUp (ie. diagonal gradient moving from a bottom corner up to the opposite corner)
MsgBox .GradientStyle
‘returns 2
MsgBox .GradientVariant
‘returns 0.9
MsgBox .GradientDegree
End With
With Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill
‘set a two-color gradient for the Plot Area Fill, gradient fill style of msoGradientHorizontal and gradient variant of 1
.TwoColorGradient msoGradientHorizontal, 1
‘set the interior color for stop #1 (ForeColor) to Yellow
.ForeColor.SchemeColor = 6
‘set the color for stop #2 (BackColor) to Green
.BackColor.SchemeColor = 4
‘returns 2 indicating msoGradientTwoColors (ie. two-color gradient)
MsgBox .GradientColorType
End With
With Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill
‘specify fill pattern for Plot Area to msoPattern90Percent (90% of the foreground color)
.Patterned msoPattern90Percent
‘set fore color for the plot area to Yellow
.ForeColor.SchemeColor = 6
‘set back color for the plot area to Green
.BackColor.SchemeColor = 4
‘returns the fill pattern, value 12, corresponding to msoPattern90Percent
MsgBox .Pattern
‘get the fill type – returns value 2 representing a Patterned fill (msoFillPatterned)
MsgBox .Type
End With
With Sheets(“Sheet1”).ChartObjects(1).Chart.ChartArea.Fill
‘specify fill preset texture for Chart Area to msoTextureWhiteMarble – White marble texture
.PresetTextured msoTextureWhiteMarble
‘returns 4 indicating msoFillTextured (ie. Textured fill)
MsgBox .Type
End With
‘set small tiles of an image to the specified fill
Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill.UserTextured “C:\Users\Amit Tandon\Documents\Photos\IMG_2032.JPG”
‘using the UserPicture Method to set an image to the specified fill ie. to plot area
Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill.UserPicture “C:\Users\Amit Tandon\Documents\Photos\IMG_2032.JPG”
‘using the UserPicture Method to stack (ie. repeat) pictures in a column/bar chart’s Series 1:
Sheets(“Sheet1″).ChartObjects(1).Chart.SeriesCollection(1).Fill.UserPicture PictureFile:=”C:\Users\Amit Tandon\Documents\Photos\IMG_2032.JPG”, PictureFormat:=xlStack
‘scales to 1000 units (Series 1 value) per picture ie. a value, in Series 1, of 5000 units will show 5 pictures – refer Image 1
Sheets(“Sheet1″).ChartObjects(1).Chart.SeriesCollection(1).Fill.UserPicture PictureFile:=”C:\Users\Amit Tandon\Documents\Photos\Dollar.jpg“, PictureFormat:=xlStackScale, PictureStackUnit:=1000
‘using the UserPicture Method wherein picture is stretched to full length
Sheets(“Sheet1″).ChartObjects(1).Chart.PlotArea.Fill.UserPicture PictureFile:=”C:\Users\Amit Tandon\Documents\Photos\IMG_2032.JPG“, PictureFormat:=xlStretch
With Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Fill
‘set plot area fill to a preset gradient – specifies gradient style as msoGradientFromCenter (Gradient running from the center out to the corners), gradient variant of 1 & preset gradient Type as msoGradientOcean
.PresetGradient msoGradientFromCenter, 1, msoGradientOcean
‘returns 7 indicating msoGradientOcean (ie. Ocean gradient)
MsgBox .PresetGradientType
End With
Interior Object – objChartElement.Interior – use the Interior property to return the Interior object, to manipulate the chart element’s interior (inside area).
| Commonly Used Properties of the Interior Object, with Charts:
|
||
| Property | Syntax | Description |
| Color Property | objInterior.Color | sets or returns the primary color of the interior, and create a color value by using the RGB function. |
| ColorIndex Property | objInterior.ColorIndex | sets or returns the interior color, as an index value (in the existing color palette) or one of the XlColorIndex constants (xlColorIndexAutomatic, xlColorIndexNone). |
| Pattern Property | objInterior.Pattern | sets or returns the interior pattern, as per constants / values defined in XlPattern Enumeration – xlPatternSolid (value 1 – solid color); xlPatternChecker (value 9, checkerboard); xlPatternLightHorizontal (value 11, light horizontal lines); xlPatternLightVertical (value 12, light vertical bars); xlPatternLightDown (value 13, Light diagonal lines running from the upper left to the lower right); xlPatternGrid (value 15 – Grid); xlPatternGray16 (value 17, 16% gray); xlPatternAutomatic (value -4105, Excel controls pattern); xlPatternHorizontal (value -4128, Dark horizontal lines); and so on. |
| PatternColor Property | objInterior.PatternColor | sets or returns the color of the interior pattern, as an RGB value. |
| PatternColorIndex Property | objInterior.PatternColorIndex | sets or returns the color of the interior pattern, as an index value (in the existing color palette) or one of the XlColorIndex constants (xlColorIndexAutomatic, xlColorIndexNone) wherein xlColorIndexNone equates with xlPatternNone of Pattern Property which indicates no pattern. |
VBA Codes illustrating Interior Object’s Properties:
With Sheets(“Sheet1”).ChartObjects(1).Chart
‘set interior color of plot area to yellow
.PlotArea.Interior.Color = vbYellow
‘set interior color of chart area to green
.ChartArea.Interior.ColorIndex = 4
End With
With Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Interior
‘set pattern of xlLightDown indicating light diagonal lines running from the upper left to the lower right (value 13)
.Pattern = xlLightDown
‘set the color of the interior pattern as an RGB value, to yellow
.PatternColor = RGB(255, 255, 0)
‘returns 6, indicating color index of the interior pattern as yellow
MsgBox .PatternColorIndex
End With
Font Object – objChartElement.Font – use the Font Property to return a Font object, to manipulate the font attributes viz. font name, font size, font color, …
| Commonly used Properties of the Font Object, with Charts:
|
||
| Property | Syntax | Description |
| Background Property | objFont.Background | sets (or returns) background type for text in a chart, as per constants defined in XlBackground Enumeration: xlBackgroundAutomatic (value -4105) – Excel controls the background, xlBackgroundTransparent (value 2) – Transparent background, xlBackgroundOpaque (value 3) – Opaque background. |
| Bold Property | objFont.Bold | sets (or returns) a bold font – True sets the font to bold |
| Color Property | objFont.Color | sets or returns the primary color for the font, and create a color value by using the RGB function. |
| ColorIndex Property | objFont.ColorIndex | sets or returns the font color, as an index value (in the existing color palette) or one of the XlColorIndex constants (xlColorIndexAutomatic, xlColorIndexNone) |
| FontStyle Property | objFont.FontStyle | sets or returns the font style, using a string value viz. Regular, Bold, Italic, Bold Italic. |
| Italic Property | objFont.Italic | sets (or returns) an italic font, using a Boolean value – True sets the font to italic |
| Name Property | objFont.Name | sets or returns the font name, using a string value viz. Arial, Calibri, Times New Roman, … |
| Size Property | objFont.Size | sets or returns the font size, in points |
| Strikethrough Property | objFont.Strikethrough | sets (or returns) a font struck through with a horizontal line, using a Boolean value – True sets the font to strikethrough |
| Subscript Property | objFont.Subscript | sets (or returns) a font format as subscript – True formats the font to subscript |
| Superscript Property | objFont.Superscript | sets (or returns) a font format as superscript – True formats the font to superscript |
| Underline Property | objFont.Underline | sets or returns the type of underline for the font, using one of the XlUnderlineStyle constants: xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, xlUnderlineStyleDoubleAccounting. |
VBA Codes illustrating Font Object’s Properties:
With Sheets(“Sheet10”).ChartObjects(1).Chart
.ChartTitle.Text = “Qtrly Sales & Profit“
‘set to False, so that the Chart Title font remains the same size whenever the Chart size changes
.ChartTitle.AutoScaleFont = False
With .ChartTitle.Font
.Name = “Arial“
.FontStyle = “Bold“
.Size = 16
.Color = RGB(255, 0, 0)
.Strikethrough = False
.Superscript = False
.Subscript = False
.Shadow = False
.Underline = xlUnderlineStyleDouble
End With
With .Axes(xlCategory, xlPrimary).AxisTitle
.Caption = “Yr-Qtr“
.Font.Name = “Calibri“
.Font.Bold = True
.Font.Italic = False
.Font.Size = 10
.Font.ColorIndex = 5
.Font.Underline = xlUnderlineStyleNone
.Font.Background = xlAutomatic
‘format characters within a text string – starting at 4th character & a length of 3 characters – representing the word “Qtr”
.Characters(4, 3).Font.Italic = True
End With
End With