Macro doesn't record action on a chart

[mark]

I am recording a macro to remove the fill from a chart (excel 2008).
There are 12 rwos of bars in the chart and so I want to remove the
fills from each bar one as below (series 1 is left alone); problem is
that the macro does not record it when I remove the fill (set it to no
fill) using the format command, does anyone know why the macro is
ignoring the action? Thanks and regards, Mark

Sub RemoveFills()
'
Dim i As Integer

'
For i = 2 To 12
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(i).Select
CodeToRemoveTheFillFromThe Selection

Next i

End Sub

 

[Peter T]

Try the following -

Sub test()
Dim cht As Chart
Dim bSeriesVis As Boolean

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

bSeriesVis = False ' True ' toggle

SeriesVisible cht, bSeriesVis

End Sub

Sub SeriesVisible(cht As Chart, bVis As Boolean)
Dim nFill As Long, nBdrStyle As Long
Dim i As Long
Dim sr As Series

If bVis Then
nFill = xlAutomatic
nBdrStyle = xlContinuous
Else
nFill = xlNone
nBdrStyle = xlNone
End If

For i = 2 To cht.SeriesCollection.Count
With cht.SeriesCollection(i)
.Interior.ColorIndex = nFill
.Border.LineStyle = nBdrStyle ' ?
End With
Next

End Sub

Regards,
Peter T

 

[mark]

Try the following -

Sub test()
Dim cht As Chart
Dim bSeriesVis As Boolean

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

bSeriesVis = False ' True ' toggle

SeriesVisible cht, bSeriesVis

End Sub

Sub SeriesVisible(cht As Chart, bVis As Boolean)
Dim nFill As Long, nBdrStyle As Long
Dim i As Long
Dim sr As Series

    If bVis Then
        nFill = xlAutomatic
        nBdrStyle = xlContinuous
    Else
        nFill = xlNone
        nBdrStyle = xlNone
    End If

    For i = 2 To cht.SeriesCollection.Count
        With cht.SeriesCollection(i)
            .Interior.ColorIndex = nFill
            .Border.LineStyle = nBdrStyle ' ?
        End With
    Next

End Sub

Regards,
Peter T










- Show quoted text -

Thanks for that Peter, unfortunately I 'fell at the first fence' it
wouldn't select the chart! I didn't mention thatt the chart is not
embedded but in a chart sheet but that shouldn't matter should it? I
recorded a macro and selected the chart and it wrote the code:

ActiveSheet.ChartObjects("Chart 1").Activate

So I was kind of surpirsed when the code came up with the message that
the item was not found. There seems to be a difference between the
code when it is recorded from a macro and when it is written which
seems wrong to me, if the object oroducres the code in the macro when
selected why on earth will it not select when it is run back, I am
somewhat nonplussed! I was pretty proficient at programming excel 2003
but the latest version seems to be a lot less user friendly am I
right?

Thanks and regards, Mark

 

[Peter T]

Try the following -

Sub test()
Dim cht As Chart
Dim bSeriesVis As Boolean

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

bSeriesVis = False ' True ' toggle

SeriesVisible cht, bSeriesVis

End Sub

Sub SeriesVisible(cht As Chart, bVis As Boolean)
Dim nFill As Long, nBdrStyle As Long
Dim i As Long
Dim sr As Series

If bVis Then
nFill = xlAutomatic
nBdrStyle = xlContinuous
Else
nFill = xlNone
nBdrStyle = xlNone
End If

For i = 2 To cht.SeriesCollection.Count
With cht.SeriesCollection(i)
.Interior.ColorIndex = nFill
.Border.LineStyle = nBdrStyle ' ?
End With
Next

End Sub

Regards,
Peter T










- Show quoted text -

Thanks for that Peter, unfortunately I 'fell at the first fence' it
wouldn't select the chart! I didn't mention thatt the chart is not
embedded but in a chart sheet but that shouldn't matter should it? I
recorded a macro and selected the chart and it wrote the code:

ActiveSheet.ChartObjects("Chart 1").Activate

So I was kind of surpirsed when the code came up with the message that
the item was not found. There seems to be a difference between the
code when it is recorded from a macro and when it is written which
seems wrong to me, if the object oroducres the code in the macro when
selected why on earth will it not select when it is run back, I am
somewhat nonplussed! I was pretty proficient at programming excel 2003
but the latest version seems to be a lot less user friendly am I
right?

Thanks and regards, Mark

=======================================
Hi Mark,

It would matter very much indeed whether the chart is embedded on a sheet or
a is chart sheet. If it's a chart sheet, as you now say, this cannot
possibly work (unless you have an embedded chart on a chart sheet)-

ActiveSheet.ChartObjects("Chart 1").Activate

Yet you say the above is what you got when you recorded a macro, doesn't
make sense. Afraid the information you are giving is highly contradictory.

In the first of the two procedures I posted change

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
to
Set cht = ActiveChart

Select the chart you want to process and run the code (ie run Test so that
it it turn calls SeriesVisible cht, bSeriesVis).

Regards,
Peter T