CODEWITHSUNDEEP
vbaexcel
Schbabako
Schbabako

Reputation: 113

Black Border around the Column Chart in VBA

I try to set a black border around the Column chart from my Vba CODE. This is what i have now. The last row where i set Border.ColorIndex obviously does not work. Currently the column looks like this.

enter image description here

I want it to look like this.

enter image description here

Here is my code.

ActiveSheet.Cells(10000, 10000).Select
ActiveSheet.Shapes.AddChart.Select
With ActiveChart ' clear SeriesCollection
Do Until .SeriesCollection.Count = 0
         .SeriesCollection(1).Delete
Loop
End With
ActiveChart.ChartType = xlColumnClustered
ActiveChart.PlotVisibleOnly = True
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Plottt"     ' rename chart sheets

' create SeriesCollection for each line
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "Hallo"
    ActiveChart.SeriesCollection(1).XValues = breaks
    ActiveChart.SeriesCollection(1).Values = freq
    ActiveChart.ChartGroups(1).GapWidth = 0
    ActiveChart.ChartGroups(1).Border.ColorIndex = 3

Also i would like to reduce the step size from my code. Help with this would also be appreciated.

Upvotes: 0

Views: 2198

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

To set the border color for a SeriesCollection(1) use the line below:

ActiveChart.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)

Note: it's better to not use ActiveChart, and it's "relatives". Instead use referenced objects. In this case use a ChartObject.

Simple reference code:

Dim Chtobj                     As ChartObject

' modify "Chart_Data" Name to your Sheet, and "Chart 1" to your chart's name
Set Chtobj = Sheets("Chart_Data").ChartObjects("Chart 1")
With Chtobj
    ' modify the chartobject properties here...

    ' modify the major unit of X-axis
    .Axes(xlCategory).MajorUnit = 5 '<-- modify to whatever value you want
    ' modify the minor unit of X-axis
    .Axes(xlCategory).MinorUnit = 1
End With

Upvotes: 3

Brandon Barney
Brandon Barney

Reputation: 2392

I am not sure why the border isn't showing, though I suspect that there must be a property that determines its visibility or thickness.

For cleaning up your code though, try this:

Dim oChart as Object
ActiveSheet.Cells(10000, 10000).Select
Set oChart = ActiveSheet.Shapes.AddChart

With oChart' clear SeriesCollection
    Do Until .SeriesCollection.Count = 0
        .SeriesCollection(1).Delete
    Loop

    .ChartType = xlColumnClustered
    .PlotVisibleOnly = True
    .Location Where:=xlLocationAsNewSheet, Name:="Plottt"     ' rename chart sheets

' create SeriesCollection for each line
    .SeriesCollection.NewSeries
    With .SeriesCollection(1)
        .Name = "Hallo"
        .XValues = breaks
        .Values = freq
    End With

    With .ChartGroups(1)
        .GapWidth = 0
        With .Border
            .ColorIndex = 3
            ' You may need the LineStyle property of the border
            ' https://msdn.microsoft.com/en-us/library/office/ff821622.aspx
            .Linestyle = xlContinuous
        End With
    End With
End With ' Ends the with block for the entire chart

This should not only clean up your code quite a bit and make it easier to debug, but I have had cases where an object I am working with doesn't update properly if I try to update it's properties outside of a with block. I am not sure if there is a reason for this or not, but I err on the side of using the With block just in case.

Upvotes: 1

Related Questions