User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

 

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 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)
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 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

 

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

 

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