Trouble setting marker properties in chart through VBA

I am trying to control the marker properties in an excel chart, with each point in a single series taking on different properties.  However, it is choosing to ignore most of the settings that I tell it to use.  For example,  I have a scatter chart with a single point plotted on it.  Using the following code snippet, I can change color and size of the marker, but the style will not change.  When I query the proerties of the individual point, either through vba or the excel format window, it shows all values at automatic (as opposed to what I set, and what I see on the chart).  Why will the style not change to circle, and why do the property queries not match what I see or set?
very frustrated and confused....

    For Each bubble In Charts(1).SeriesCollection(1).Points
            With bubble
                .MarkerStyle = 8
                .MarkerForegroundColor = RGB(0, 0, 0)
                .MarkerBackgroundColorIndex = 8
                .MarkerSize = 52
            End With
    Next bubble



print charts(1).seriesCollection(1).points(1).markerstyle
-4105
ASEuserAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello ASEuser,

why not use a bubble chart? You can control the individual colors of the bubbles. Either add each bubble as its own data series, or all data as one series and format the fill to vary colors by point.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello, ASEuser,

is bubble a variable? If so, what is its data type? Are you manipulating a XY Chart or a Bubble chart?

Please post the workbook with the chart and the complete code. De-sensitize and replace confidential data with dummy data before posting.

cheers, teylyn
0
 
ASEuserAuthor Commented:
Hello,

My high level goal is to use a bubble chart, but color each bubble based on a given value.  Since I cannot control the individual colors of a bubble chart, I resorted to an XY scatter chart and manually manipulate the marker size and color.

The attached file has 1 data series with 2 data points.  I am trying to set both to circles with a green background and black foreground, and a marker size of 50.  The size and color work, but I cannot get circles for the marker.  If a add a second series to the chart, everything suddenly works.  When I remove the second series, it fails again.  Very mysterious...

Thank you
test.xls
0
 
ASEuserAuthor Commented:
well, that is much easier!  I had tried some things earlier along those lines, and it would never let me set properties on the bubble chart series.  I guess I just hadn't tried the proper key words.

This doesn't really explain why the current method is not working, but I don't really care any more!

Thank you.
0
All Courses

From novice to tech pro — start learning today.