+ Reply to Thread
Results 1 to 2 of 2

Bar of Pie chart - Need Bar creation

  1. #1
    Registered User
    Join Date
    06-16-2005
    Posts
    62

    Arrow Bar of Pie chart - Need Bar creation

    I have the code running to create the pie chart.

    I am would like to breakout all categories with 4% or less into the bar in a Bar of Pie Chart.

    I am completly stumped? Craigm
    ----------------------------------------------

    Sub chrtTire()

    Dim chrtTires As Chart

    Sheets("Data").Select
    Range("C3").Select
    'DELETE ALL CHARTS ON THE DATA WORKSHEET
    ActiveSheet.ChartObjects.Delete

    Set chrtTires = Charts.Add
    Set chrtTires = chrtTires.Location(Where:=xlLocationAsObject, Name:="Data")

    With chrtTires
    .ChartType = xlPie
    .SetSourceData Source:=Sheets("TiresSum").Range("C1:D21"), PlotBy:=xlCol
    .HasTitle = True
    .ChartTitle.Text = "Tire Usage by Department"
    With .ChartTitle.Font
    .Size = 16
    .Background = xlBackgroundTransparent
    End With

    .ChartArea.Shadow = True 'Outer edge of entire chart
    .HasLegend = False
    .HasDataTable = True
    .ApplyDataLabels xlDataLabelsShowLabel
    .ApplyDataLabels xlDataLabelsShowValue
    .ApplyDataLabels xlDataLabelsShowPercent

    With .Parent
    .Top = Range("B2").Top 'With Charts("chrtTires").ChartArea
    .Left = Range("A1").Left
    .Height = ("350")
    .Width = ("500")
    .Name = "TiresSum"
    .RoundedCorners = True
    End With
    End With

    Worksheets("Data").ChartObjects(1).Chart.ChartArea.Interior.Pattern = xlLightDown

    With chrtTires.PlotArea.Fill 'Just the pie area
    .Visible = msoFalse 'Makes are transparent msoFalse
    End With

    With chrtTires.PlotArea 'Just the pie area
    .Border.LineStyle = xlLineStyleNone
    End With

    With chrtTires.ChartArea.Fill 'The entire chart area
    .Visible = True
    .ForeColor.SchemeColor = 15
    .BackColor.SchemeColor = 17
    .TwoColorGradient Style:=msoGradientHorizontal, Variant:=1

    End With

    End Sub

  2. #2
    Jon Peltier
    Guest

    Re: Bar of Pie chart - Need Bar creation

    Craig -

    It doesn't take many lines to convert the pie to a bar-of-pie, with all
    items less than 4% of the total placed in the bar. Apparently your
    procedure is based on recorded code. Here's what the macro recorder
    showed me when I converted the pie chart:

    Sub Macro1()
    '
    ' Macro2 Macro
    ' Macro recorded 7/5/2005 by Jon Peltier
    '
    ActiveChart.ChartType = xlBarOfPie
    With ActiveChart.ChartGroups(1)
    .HasSeriesLines = True
    .VaryByCategories = True
    .SplitType = xlSplitByPercentValue
    .SplitValue = 4
    .GapWidth = 100
    .SecondPlotSize = 75
    End With
    End Sub

    Making the bar-of-pie from scratch is also pretty straightforward:

    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 7/5/2005 by Jon Peltier
    '
    Charts.Add
    ActiveChart.ChartType = xlBarOfPie
    ActiveChart.SetSourceData _
    Source:=Sheets("TiresSum").Range("C1:D21"), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Date"
    With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Tire Usage by Department"
    End With
    With ActiveChart.ChartGroups(1)
    .HasSeriesLines = True
    .VaryByCategories = True
    .SplitType = xlSplitByPercentValue
    .SplitValue = 4
    .GapWidth = 100
    .SecondPlotSize = 75
    End With
    End Sub

    You seem to have a duplicate "End With", and lots of your lines should
    begin with ".", although your newsreader may be overly helpful in
    deleting these.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    Craigm wrote:

    > I have the code running to create the pie chart.
    >
    > I am would like to breakout all categories with 4% or less into the bar
    > in a Bar of Pie Chart.
    >
    > I am completly stumped? Craigm
    > ----------------------------------------------
    >
    > Sub chrtTire()
    >
    > Dim chrtTires As Chart
    >
    > Sheets("Data").Select
    > Range("C3").Select
    > 'DELETE ALL CHARTS ON THE DATA
    > WORKSHEET
    > ActiveSheet.ChartObjects.Delete
    >
    > Set chrtTires = Charts.Add
    > Set chrtTires = chrtTires.Location(Where:=xlLocationAsObject,
    > Name:="Data")
    >
    > With chrtTires
    > ChartType = xlPie
    > SetSourceData Source:=Sheets("TiresSum").Range("C1:D21"),
    > PlotBy:=xlCol
    > HasTitle = True
    > ChartTitle.Text = "Tire Usage by Department"
    > With .ChartTitle.Font
    > Size = 16
    > Background = xlBackgroundTransparent
    > End With
    >
    > ChartArea.Shadow = True 'Outer edge of entire chart
    > HasLegend = False
    > HasDataTable = True
    > ApplyDataLabels xlDataLabelsShowLabel
    > ApplyDataLabels xlDataLabelsShowValue
    > ApplyDataLabels xlDataLabelsShowPercent
    >
    > With .Parent
    > Top = Range("B2").Top 'With Charts("chrtTires").ChartArea
    > Left = Range("A1").Left
    > Height = ("350")
    > Width = ("500")
    > Name = "TiresSum"
    > RoundedCorners = True
    > End With
    > End With
    >
    > Worksheets("Data").ChartObjects(1).Chart.ChartArea.Interior.Pattern =
    > xlLightDown
    >
    > With chrtTires.PlotArea.Fill 'Just the pie area
    > Visible = msoFalse 'Makes are transparent
    > msoFalse
    > End With
    >
    > With chrtTires.PlotArea 'Just the pie area
    > Border.LineStyle = xlLineStyleNone
    > End With
    >
    > With chrtTires.ChartArea.Fill 'The entire chart area
    > Visible = True
    > ForeColor.SchemeColor = 15
    > BackColor.SchemeColor = 17
    > TwoColorGradient Style:=msoGradientHorizontal, Variant:=1
    >
    > End With
    >
    > End Sub
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1