Hiding data series in a bubble chart using VBA

[kmurshed]

Hello,

I'm having some problems when trying to hide and unhide a data series, in a bubble chart, using VBA.

The code I'm using is this, it changes the color of the bubble from white to green, and when its white, it gets hidden:

Sheets("overview").ChartObjects("Chart 6").Activate
With ActiveChart.SeriesCollection("high high")
    If .Interior.ColorIndex = -4142 Then
    .Interior.ColorIndex = 2
    .Border.ColorIndex = 2 
    
    Else
    .Interior.ColorIndex = -4142
    .Border.ColorIndex = -4142

    End If
End With

The color does change, and I do get the "hiding" effect, but still there is a thin border line that remains green even after I change the color, and I've tried all sorts of things in order to change its color also, but I can't seem to get it to work.

Could someone help me?

Thanks in advance

 

[p45cal]

Which version of Excel?
Have a play with:

With ActiveChart.SeriesCollection("high high")
    'hide:
    .Format.Line.Visible = msoFalse
    .Format.Fill.Visible = msoFalse
    'show:
    .Format.Line.Visible = msoTrue
    .Format.Fill.Visible = msoTrue
End With

and while testing you may need a sprinkling of
activechart.Refresh
to see the changes taking place.

 

[kmurshed]

Thanks for your reply.

That's a nice alternative, I thought it would work, but its behaving strangely. When both line and fill are set to msoFalse, I get the green color I have picked for the series, and when they are set to msoTrue, it returns a random color and a thick line. I'm trying now to add some command lines to format the line and the color, see if it works.

BTW, im using Excel 2007

Thanks

 

[kmurshed]

I've managed to get the desired effect combining

.Format.Fill.Visible = msoTrue
.Format.Line.Visible = msoFalse

with

.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)

but i'm still getting that anoying border line that does not seems to be affected by .format.line statements..

 

[kmurshed]

I figure I could make it disappear if there was a way of getting VBA to do the same as if I right clicked on the data series, selected format data series, and set the fill property to "No fill".

Is there a way?

 

[kmurshed]

I figured it out using the following code:

With ActiveChart.SeriesCollection("high high")

    If .Interior.ColorIndex = xlNone Then
        .Interior.ColorIndex = xlSolid
        .Format.Fill.ForeColor.RGB = RGB(84, 234, 64)
    Else
        .Interior.ColorIndex = xlNone
    End If
ActiveChart.Refresh
End With

Thanks for your help, it lead me in the right direction

 

[p45cal]

Whats the betting there's one or more of: 3d effect, shadow, glow, soft edge?

 

[kmurshed]

Thats the weird thing, everything but fill color was set to null, so nothing else was supposed to be there, but thats probably it, maybe I messed with one of those elements configuration while testing..