Excel Hints

Results 1 to 5 of 5

Thread: Chart Object set Marker type and color

  1. #1

    Chart Object set Marker type and color

    Does Chart object has the option to set the color and image type of the marker

    thanks much!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,093
    Location
    Of course

    [vba]

    With ActiveChart.SeriesCollection(2)
    .MarkerBackgroundColorIndex = xlAutomatic
    .MarkerForegroundColorIndex = 3
    .MarkerStyle = xlX
    .Smooth = False
    .MarkerSize = 11
    .Shadow = False
    End With
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Hi

    I am trying to do a similar thing but specifically I am trying to set the shadow on the marker.

    I am actually doing this from Access but in order to simply and try to isolate the issue I have put it into a simple macro in excel.

    I have a chart on a worksheet on which I want to re-format the default format of the series line & markers. If I right click on the series line interactively (i.e. Using the Excel Interface not VBA) and select "Format Data Series", select "Shadow" then select a "Preset" shadow, it applies the shadow to the Marker 'which is exactly what I want.

    However, when I try to do this from VBA, it applies the shadow formatting to the lines that join the markers rather than the markers themselvs. Funny thing is, I cant actually find a way to do apply shadows to the lines interactively, not that I want to.

    My code is below and I have documented it as much as I can to explain what I am doing and an alternative that I have tried. If anyone can figure out how to get the shadow affects to work on the Markers rather than the lines through VBA it would be much appreciated.

    I have also attached a sample spreadsheet. In the first Chart I have manually applied the shadow as described above, and in the second chart the VBA code applies the shadow.

    Any help is much appreciated.

    Dim excChart As Excel.Chart
    Dim excChartSeries As Excel.Series
    Dim excPoint As Excel.Point
     
    Set excChart = ActiveSheet.ChartObjects("NameOfYourChart").Chart
    Set excChartSeries = excChart.SeriesCollection(2) 'My chart has two data series, the first being a bar graph and the second being a line graph.
     
    With excChartSeries
     
        'The next 2 lines correctly set the colour of the Markers
        .MarkerBackgroundColor = RGB(255, 255, 255) 
        .MarkerForegroundColor = RGB(0, 176, 80) 
     
        'The affect of the next 3 lines is that it applies the shadow formatting to the lines that
        'join the markers rather than to the markers themselves.
     
        .Shadow = True
        .Format.Shadow.Blur = 5
        .Format.Shadow.ForeColor.RGB = RGB(0, 176, 80)
     
        'The next 2 lines correctly set the size and style of the Markers
        .MarkerSize = 12
        .MarkerStyle = xlMarkerStyleCircle
     
        'The above didnt work so I tried iterating through the points collection and setting 
        'these properties on each individual Marker or Point.
        'All that happens is that for each Marker it sets the shadow on the lines that join all the 
        'markers again, rather than on the individual marker itself.
     
        For Each excPoint In .Points
            .Shadow = True
            .MarkerBackgroundColor = RGB(255, 255, 255)
            .MarkerForegroundColor = RGB(0, 176, 80)
            .Format.Shadow.Blur = 5 
            .Format.Shadow.ForeColor.RGB = RGB(0, 176, 80) 
            .MarkerSize = 12
            .MarkerStyle = xlMarkerStyleCircle
        Next
     
    End With

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,093
    Location
    [vba]

    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(2).Format.Shadow.Type = msoShadow38
    [/vba]

    Type can be msoShadow1-43
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Thanks xld, that worked perfectly. Should have looked a bit further but sometimes you just cant see whats directly in front of you. Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •