Procedure to add border around all data points, on all charts, on all sheets of a workbook?

ajjava

New Member
Joined
Dec 11, 2018
Messages
47
I keep getting it to work in little chunks. As soon as I try to make it loop, I get errors.

Assumptions:
1 workbook
Varying # of sheets
Varying # of charts on some (but not all) sheets
Some, but not all, charts are column bar charts

Goal:
Procedure that goes through all the charts, on all the sheets, and adds a black border around each column/bar

THANK YOU!!!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
OK, so what code do you have now ?

Also, perhaps consider, do you really want to add a black border around each column / bar ?
Personally when I'm producing bar charts I try to avoid borders round the bars.
I'm a fan of Edward R Tufte who considers this kind of thing to be chart junk.
 

ajjava

New Member
Joined
Dec 11, 2018
Messages
47
Oh, believe me... I'm right with you on the no borders. But a client is requesting it and so rework it, I must. I'll post what I have tomorrow, when I'm in the office.
 

ajjava

New Member
Joined
Dec 11, 2018
Messages
47
Here is what I have. If it looks like a crazy person put it together, do forgive me. I've been cobbling bits of code together and it's really easy to lose track.

Code:
Sub OutlineBars()

' OutlineBars Macro




   
Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject
Dim ct As Chart
Dim mySeries As Series
Dim seriesCol As SeriesCollection
Dim I As Integer
            
I = 1






Set CurrentSheet = ActiveSheet




'Go through all the sheets
For Each sht In ActiveWorkbook.Worksheets
    
    'Go through all the charts
    For Each cht In sht.ChartObjects
        
        cht.Activate
        
        'Do something with the chart...
            Set seriesCol = ActiveSheet.ChartObject(1).Chart.SeriesCollection
            For Each mySeries In seriesCol
                Set mySeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(I)
                'Add a border around each data series (not individual point)
                
                'First data series
                With mySeries.Format.Line
                    .Visible = msoTrue
                    .ForeColor.ObjectThemeColor = msoThemeColorText1
                End With
             I = I + 1
            Next mySeries






    Next cht


Next sht




        
End Sub
 

ajjava

New Member
Joined
Dec 11, 2018
Messages
47
Stop the presses - I was able to find the exact code I need, without having to cobble things together. Feel free to continue analyzing, if the challenge intrigues you. I love to see the different methods that can be used to accomplish the same goal :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,939
Members
409,848
Latest member
Blomsten
Top