Child Objects common for many chart elements: Border Object, ChartFillFormat Object, Interior Object, Font Object

Child Objects commonly used with Chart Elements: Border Object, ChartFillFormat Object, Interior Object, Font Object

Contents:

Border Object

ChartFillFormat Object

Interior Object

Font Object

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

Border ObjectobjChartElement.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)
Sheets(“Sheet1”).ChartObjects(1).Chart.Axes(xlValue).AxisTitle.Border.Color = RGB(0, 255, 0)
Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Border.Color = vbBlue
Sheets(“Sheet1”).ChartObjects(1).Chart.ChartArea.Border.Color = vbRed

 

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”
‘if set to automatic color, set black color for series 1 line
.ColorIndex = 1

Else

MsgBox “Non-Automatic”
‘if NOT set to automatic color, set red color for series 1 line
.ColorIndex = 3

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

ChartFillFormat Object – use the Fill PropertyobjChartElement.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 EnumerationxlStack (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 EnumerationxlSides (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”

Image 1

‘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

Interior ObjectobjChartElement.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 EnumerationxlPatternSolid (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

Font ObjectobjChartElement.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

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top