Invert if negative chart error

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
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
Code:
'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
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You shouldn't need to activate or select anything. Try the untested:

Code:
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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):
Code:
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

Board Regular
Joined
Jun 16, 2010
Messages
149
that give error Unable to set invertifnegative property of series class.
Rich (BB code):
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
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
Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,425
Messages
5,831,559
Members
430,075
Latest member
Tuturino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top