1

I am trying to re-write some code created with the macro recorder whilst formatting a line on a chart. I have tried numerous ways of re-writing the syntax so i can use a With statement to avoid any .Activate or .Select.

My code is failing with error 438 Object doesn't support this property of method on the line:

ser.FormatLine.ForeColor.RGB = RGB(0, 176, 80)

Clearly .Format.Line does not belong to mychart.SeriesCollection(10) but after having looked across this site and various others trying to get the syntax right to enable me to use .Format.Line i have failed. In particular, i haven't been able to find a matching example using a With statement.

I believe i may need to access the Series not the SeriesCollection?

If it helps the particular series name is "Target", which is associated with SeriesCollection(10).

Hopefully, it is a simple fix and i would welcome any pointers on how to achieve my goal.

My latest code re-write was:

Public Sub FormatBenchTargetLine()

        Dim wb As Workbook
        Dim ws As Worksheet
        Dim ser As Series

        Set wb = ThisWorkbook
        Set ws = wb.Sheets("Benchmarking")

        Dim mychart As Chart
        Set mychart = ws.ChartObjects("Chart 7").Chart

        With ws

          ' On Error Resume Next

           Set ser = mychart.SeriesCollection(10)

           ser.Format.Line.Visible = msoTrue 
           ser.FormatLine.ForeColor.RGB = RGB(0, 176, 80) '***Errors here*******
           ser.FormatLine.Transparency = 0
           ser.FormatLine.DashStyle = msoLineSysDash

         '  On Error GoTo 0
        End With
End Sub

Original macro code:

Sub FormatBenchTarg()

    ActiveSheet.ChartObjects("Chart 7").Activate
    ActiveChart.FullSeriesCollection(10).Select

    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
        .Transparency = 0
    End With

    ActiveChart.ChartArea.Select

    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineDash
    End With
End Sub
Community
  • 1
  • 1
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Can you try ser.chartarea.Format.Line.Visible = msoTrue – PankajKushwaha Nov 07 '17 at 15:37
  • Same error message i'm afraid. Intellisense doesn't provide my any options following .SeriesCollection(10) which makes me think i have to lose this and access the series in a different way. I don't want to loop all the series though. I want just the series which has the title Target and is SeriesCollection(10) – QHarr Nov 07 '17 at 15:40
  • Okay I get it. The SeriesCollection is culprit. Set ser = mychart.SeriesCollection(1) So for first chart the series collection is 1, for second it is 2, for third it is 3 and so on. So if you made one chart and deleted it and again made a chart series collection should be 2 for second chart. So series collection show the what number chart it is. – PankajKushwaha Nov 07 '17 at 15:49
  • 1
    Are you sure the error isn't on the following lines since `FormatLine` is *not* a valid property. – Rory Nov 07 '17 at 15:49
  • 1
    @Rory: It definitely is not a valid property but stepping through with F8 is highlighting the line i posted. – QHarr Nov 07 '17 at 15:50
  • @Rory, Apologies. I despair. It is now showing the following line in yellow: ser.FormatLine.ForeColor.RGB = RGB(0, 176, 80) – QHarr Nov 07 '17 at 15:52
  • I have updated my question accordingly indicating an edit. – QHarr Nov 07 '17 at 15:57
  • When you type `ser.`, you should be getting intellisense. Do both `Format` and `FormatLine` members exist? Error 438 means you're calling members that don't exist, which means you're either ignoring intellisense, or writing late-bound code against `Object` - in which case you need to declare a local variable of the appropriate type, like you did here with `ser As Series`, and then intellisense should show you the way. – Mathieu Guindon Nov 07 '17 at 16:08
  • Yes the members appear and it is declared as a Series which is why i thought i was close to getting it right. I do note however that i have used SeriesCollection(10) whereas the macro code was FullSeriesCollection(10). I didn't think this would make a difference though. – QHarr Nov 07 '17 at 16:11
  • 1
    Use `Format.Line` not `FormatLine` (note the period between `Format` and `Line`. – xidgel Nov 07 '17 at 16:16
  • @xidgel: Agghhhhhhhhhh! How could i not see that typo. And i think Rory was also trying to point this out and i completely blanked on it! – QHarr Nov 07 '17 at 16:17
  • If post as answer i will accept. – QHarr Nov 07 '17 at 16:18

1 Answers1

0

As mentioned in the comments the valid syntax is:

ser.Format.Line.ForeColor.RGB = RGB(0, 176, 80)

Not 

ser.FormatLine.ForeColor.RGB = RGB(0, 176, 80)
QHarr
  • 83,427
  • 12
  • 54
  • 101