Invert if negative chart error

[ManvinderKaur]

Hi I am using these steps
http://www.andypope.info/charts/Invertneg.htm
to fill color if negative for my bar graph. But it works only when sheet is active. When when I close that excel workbook and open again. color of bar chart change automatically from red and green to blue and white. I could not figure out why color change automatically whenever I open my sheet again. I was trying to call following sub when sheet become active but that give error 1004 in line 2. I was trying to assign macro to chart. But that works only if I select chart. Is there any solution

'Formating of Financial saving based on current production level
Public Sub ColorBar()
    [COLOR="Red"]ActiveSheet.ChartObjects("Chart 64").Activate[/COLOR] ' error here
    ActiveChart.SeriesCollection(1).Select
    Selection.InvertIfNegative = True
    Selection.Fill.Patterned Pattern:=msoPattern5Percent
    With Selection
        .Fill.ForeColor.SchemeColor = 43
        .Fill.BackColor.SchemeColor = 3
    End With
    With Selection.Interior
        .ColorIndex = 3
        .PatternColorIndex = 43
        .Pattern = xlSolid
    End With
End Sub

http://www.excelforum.com/excel-programming/739390-invert-if-negative-chart-issue.html#post2353397

 

[Andrew Poulsom]

You shouldn't need to activate or select anything. Try the untested:

Public Sub ColorBar()
    Dim Sh As Worksheet
    Dim Cht As Chart
'***Change sheet reference to suit***
    Set Sh = Worksheets("Sheet1")
    Set Cht = Sh.ChartObjects("Chart 64").Chart
    With Cht.SeriesCollection(1)
        .InvertIfNegative = True
        .Fill.Patterned Pattern:=msoPattern5Percent
        .Fill.ForeColor.SchemeColor = 43
        .Fill.BackColor.SchemeColor = 3
        With .Interior
            .ColorIndex = 3
            .PatternColorIndex = 43
            .Pattern = xlSolid
        End With
    End With
End Sub

 

[p45cal]

As far as the colour changes are concerned I can't help, but as far as the error on line two try the following (untested) which should allow changes to take place without it being the activesheet, but you have to adjust the macro to match your sheet's name (red):

Public Sub ColorBar()
With Sheets("[COLOR=Red]TheSheetName[/COLOR]").ChartObjects("Chart 64").Chart.SeriesCollection(1)
  .InvertIfNegative = True
  .Fill.Patterned Pattern:=msoPattern5Percent
  .Fill.ForeColor.SchemeColor = 43
  .Fill.BackColor.SchemeColor = 3
  With .Interior
    .ColorIndex = 3
    .PatternColorIndex = 43
    .Pattern = xlSolid
  End With
End With
End Sub

 

[ManvinderKaur]

that give error Unable to set invertifnegative property of series class.

Public Sub ColorBar()
    Dim Sh As Worksheet
    Dim Cht As Chart
'***Change sheet reference to suit***
    Set Sh = Worksheets("Sheet1")
    Set Cht = Sh.ChartObjects("Chart 64").Chart
    With Cht.SeriesCollection(1)
        .InvertIfNegative = True ' error here
        .Fill.Patterned Pattern:=msoPattern5Percent
        .Fill.ForeColor.SchemeColor = 43
        .Fill.BackColor.SchemeColor = 3
        With .Interior
            .ColorIndex = 3
            .PatternColorIndex = 43
            .Pattern = xlSolid
        End With
    End With
End Sub

 

[Andrew Poulsom]

Do you also get an error with p45cal's code?

 

[ManvinderKaur]

My sheet is protected..when I have removed protection ..your code is working. But I need sheet protected.
I have tried following code for sheet activation . But still no success

Private Sub Worksheet_Activate()
Me.Protect userinterfaceonly:=True
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Me.Protect userinterfaceonly:=True ' for protection
Me.ScrollArea = "A1:T51"
Me.Range("A1:T1").Select
ActiveWindow.Zoom = True
Me.Range("A3").Select
Call ColorBar
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Andrew Poulsom]

If that's not working you will need to unprotect the sheet before calling ColorBar and protect it again afterwards.

 

[ManvinderKaur]

working finally thanks for your awesome help all the time...a big thanks

 

[ManvinderKaur]

I have one problem again. These are red and green colors. When I open in excel 2003 colors are now red and green. these do not change to blue and white default color. But when I open in excel 2007 color change to red and white instead of red and green. red becomes white and green becomes red in 2007

 

[Andrew Poulsom]

I don't have Excel 2007 where I am at the moment. I will try to look later.