Formatting Graph using VBA

[eoinymc]

Hi there,

I was wondering if anyone can help me.

I want to format a chart when a particular cell is chosen. This cell may be in another sheet, but I don't want the sheet to change to where the chart is. I just want the chart to update in the background. Is this possible?

Here is my code that works, but changes the sheet because I am activating the chart:

Function FormatGraph(myWorksheet As String)

Set Dashb = Sheets("Dashboard")
Set Ind = Sheets("Index")


Application.ScreenUpdating = False


Dashb.ChartObjects("RevWfall").Activate


    Dim myPoint As Integer, valArray


    With Excel.ActiveChart.SeriesCollection(2)
        valArray = .Values
        j = 36
        For myPoint = 2 To 6 '.Points.Count
            If (Ind.Cells(j, 39) - Ind.Cells(j - 1, 39)) < 0 Then
                    With .Points(myPoint)
                        .Interior.Color = RGB(255, 204, 0)
                    End With
                    j = j + 1
            Else
                    With .Points(myPoint)
                        .Interior.Color = RGB(150, 150, 150)
                    End With
                    j = j + 1
            End If
        Next
    End With
        
ActiveChart.Axes(xlValue).MinimumScale = Ind.Range("AM43")


End Function

I was trying to do the following, but it doesn't work. I'm getting an 'Object doesn't support this property or method' Run-time error.

Function FormatGraph(myWorksheet As String)

Set Dashb = Sheets("Dashboard")
Set Ind = Sheets("Index")


Application.ScreenUpdating = False

    Dim myPoint As Integer, valArray


    With Dashb.ChartObjects("RevWfall").SeriesCollection(2)
        valArray = .Values
        j = 36
        For myPoint = 2 To 6 '.Points.Count
            If (Ind.Cells(j, 39) - Ind.Cells(j - 1, 39)) < 0 Then
                    With .Points(myPoint)
                        .Interior.Color = RGB(255, 204, 0)
                    End With
                    j = j + 1
            Else
                    With .Points(myPoint)
                        .Interior.Color = RGB(150, 150, 150)
                    End With
                    j = j + 1
            End If
        Next
    End With


Dashb.ChartObjects("RevWfall").Axes(xlValue).MinimumScale = Ind.Range("AM43")



End Function

Any help would be greatly appreciated. This is a rather large piece of work that I'm doing and this would be the final touch to the model I'm building!

Thanks,

Eoin

 

[Cool blue]

yep, the Chart object is wrapped in the ChartObject object. See changes in red...

Function FormatGraph(myWorksheet As String)

Set Dashb = Sheets("Dashboard")
Set Ind = Sheets("Index")


Application.ScreenUpdating = False

    Dim myPoint As Integer, valArray


    With Dashb.ChartObjects("RevWfall").[COLOR=#ff0000]Chart[/COLOR].SeriesCollection(2)
        valArray = .Values
        j = 36
        For myPoint = 2 To [COLOR=#ff0000].Points.Count[/COLOR]
            If (Ind.Cells(j, 39) - Ind.Cells(j - 1, 39)) < 0 Then
                    With .Points(myPoint)
                        .Interior.Color = RGB(255, 204, 0)
                    End With
                    j = j + 1
            Else
                    With .Points(myPoint)
                        .Interior.Color = RGB(150, 150, 150)
                    End With
                    j = j + 1
            End If
        Next
    End With


Dashb.ChartObjects("RevWfall").[COLOR=#ff0000]Chart[/COLOR].Axes(xlValue).MinimumScale = Ind.Range("AM43")



End Function

 

[eoinymc]

Perfect Mr/Mrs Cool Blue!

I appreciate the help...

FYI: I have .Points.Count commented out for a reason..I don't want to include the first or last point...

thanks again,

Eoin

 

[Cool blue]

FYI: I have .Points.Count commented out for a reason..I don't want to include the first or last point...

Ah, ok. I thought it was just because you were having the problem with the error.
Points.Count -1
would be safer then. In case something changes down the track and you want to avoid future head scratching.