how to check the x axis label in vba (Excel 2010)

Question

This piece of code is trying to change the colour of the chart bars according to the Quarter (four quarters of a year and the same colour for every other quarter)

so my x-axis label is by month and I am trying to search for it and then use the Month() function to get the month number.

Sub chartcolour()
    Dim c As Chart
    Dim s As Series
    Dim iPoint As Long
    Dim nPoint As Long

    WSChart1.Activate
    ActiveSheet.ChartObjects("Chart 3").Activate

   Set c = WSChart1.ChartObjects("Chart 3")

    Set s = c.SeriesCollection(1)

    nPoint = ActiveChart.SeriesCollection(1).Points.Count


    For iPoint = 1 To nPoint
        If Month(s.XValues(iPoint)) = 11 Or 12 Or 1 Then
            s.Points(iPoint).Interior.Color = RGB(153, 153, 255)
        ElseIf Month(s.XValues(iPoint)) = 2 Or 3 Or 4 Then
            s.Points(iPoint).Interior.Color = RGB(153, 51, 102)
        ElseIf Month(s.XValues(iPoint)) = 5 Or 6 Or 7 Then
            s.Points(iPoint).Interior.Color = RGB(153, 153, 255)
        Else
            s.Points(iPoint).Interior.Color = RGB(153, 51, 102)

        End If
    Next

End Sub

Could you elaborate 'by month'? Is it a date formatted as months, or is it text, or are they numbers (month-numbers), etc.? — hnk, Jul 07 '14 at 15:12
The format is Mar-13 , Apr-13, etc. (In excel, the real value is 3/1/2013) — user3812787, Jul 07 '14 at 18:05
The Dim c as chartobject and s as series seems not working for excel 2010. Set c = WSChart1.ChartObjects("Chart 3") These two lines always show data type mismatch Set s = c.SeriesCollection(1) — user3812787, Jul 07 '14 at 18:28
WSChart1 is the name of the variable which is set to the worksheet? or is it the name of the worksheet itself? — hnk, Jul 07 '14 at 23:03
Sorry I may misunderstand you. It's the name I gave to the worksheet in the Macro Page not the Worksheet name on the worksheet tab — user3812787, Jul 08 '14 at 13:29
You'll need to post more information on the worksheet names, the rest of the code and show exactly which line is giving you the error, etc. — hnk, Jul 08 '14 at 13:30
This is the full piece of code. The error is data type mismatch for the set c and set s statments — user3812787, Jul 08 '14 at 13:32
also I cannot debug the xvalue(ipoint) part, it will return me 438 error. So I donno what error exactly is that. — user3812787, Jul 08 '14 at 13:34
Set s = c.SeriesCollection(1) this line got error 438. (after replace wschart1 to activesheet — user3812787, Jul 08 '14 at 14:03
I also dim s as chartobject instead of chart. (not sure if it effects anything) — user3812787, Jul 08 '14 at 14:12

score 1 · Answer 1 · edited May 23 '17 at 11:44

1

For some reason you can't loop through the xvalues property because it's not a collection (you get an Error 451). See the code at the link below on how to work around it with an array variable.

how to loop through xvalues

edited May 23 '17 at 11:44

Community

1
1

answered Oct 15 '15 at 02:39

Jess-eye-ca

61
5

hnk · Answer 2 · 2014-07-08T13:34:38.127

0

The error occurs because "WSChart1" is the not clearly declared. Replace it with

ActiveSheet

and the code is likely to work as expected.

edited Jul 08 '14 at 13:34

answered Jul 08 '14 at 12:58

hnk

2,216
1
13
18

how to check the x axis label in vba (Excel 2010)

2 Answers2