Results 1 to 5 of 5

Thread: charts (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    charts (2000)

    Hi all,

    i have a Sheet with 15 charts on it, and I would like to change each chart from a column chart to a 3D pie chart. The code I have doesn't work I keep getting a invalid use of property error. Can someone tell me what's wrong with my code. I'm thought I would loop through each chart, but this isn't working. What I would like to know is why?


    Public Sub changechart()
    Dim i As Integer
    Sheets("charts").Activate
    Do Until i > 15
    For i = 1 To 15
    With ActiveChart
    .HasTitle = True
    ActiveChart.Pie3DGroup
    End With
    Next i
    Loop

    End Sub


    Thanks
    Darryl.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    You can do this pretty quicky without code if you change the first chart type to 3-D Pie and then click on each of the other charts and hit F4. Would probably take as long to write and run a macro as to do it that way....at least with the number of charts you're talking about.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: charts (2000)

    BAN's reply is spot on, it will be much easier than writing code, but here are some comments on your code.

    - You have two loops within each other, a Do Until i > 15 ... Loop and a For i = 1 To 15 ... Next i loop. You need only one, having two makes no sense.
    - You refer to ActiveChart but you don't change ActiveChart within the loop, so it will be the same chart all the time.
    - Pie3DGroup refers to the pie in a chart that already is a 3D pie chart; it doesn't change a chart to this type.

    Here is a sample macro. It doesn't select anything, which is generally faster than selecting each item in turn. Also, it loops through all charts on the sheet, whether there are 15, 3 or 37.

    Public Sub ChangeChart()
    Dim cht As ChartObject
    For Each cht In Sheets("Charts").ChartObjects
    cht.Chart.ChartType = xl3DPie
    Next cht
    Set cht = Nothing
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    Thank you Ban for your reply, great tip.

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: charts (2000)

    Thanks Hans,
    I am getting closer to understanding setting an object. I had tried a For Each next loop, but I didn't include the chartobjects at the end of sheets("charts")......anyway, you da man

    Darryl.

Posting Permissions

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