Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2003 vba ChartWizard is not working in Excel 2010

    We have upgrade office 2003 to office 2010. We developed one application in open system (Visual Basic 6.0) with MS Office Access. Charts were prepared in MS Office excel.

    Created one Excel addin, which is placed it in C:\Program Files\Microsoft Office\Office14\Library folder.

    Few charts were used chart wizard, now when we are trying to run the same code, the chart is not showing correctly.

    Attached Excel 2003 and Excel 2010 charts.

    Code :
    ----------------------
    '\ Redraw Pareto button code
    Sub RefreshPareto()

    On Error GoTo Err_RefreshPareto
    Dim pvt As PivotTable
    Dim rngChart As Range
    Dim rngPercent As Range
    Dim cht As Chart
    Dim rngCell As Range
    Dim i As Integer
    Dim varTopN As Variant

    Set pvt = ActiveSheet.PivotTables(1)
    Set cht = ActiveSheet.ChartObjects(1).Chart

    'set TopN if there is a TopN range on worksheet
    On Error Resume Next
    varTopN = ActiveSheet.Range("TopN")
    If Err.Number <> 0 Then
    varTopN = ""
    End If
    On Error GoTo Err_RefreshPareto

    If varTopN <> "" Then
    If varTopN = "All" Then
    pvt.RowFields(1).AutoShow xlManual, xlTop, 15, pvt.DataFields(1).Name
    Else
    pvt.RowFields(1).AutoShow xlAutomatic, xlTop, varTopN, pvt.DataFields(1).Name
    End If
    End If

    '\ sort the PivotTable
    On Error Resume Next ' if no data found to sort
    pvt.DataBodyRange.Columns(pvt.DataBodyRange.Column s.Count).Rows(1).Select
    Selection.Sort Key1:=Selection, Order1:=xlDescending, Type:=xlSortValues, _
    OrderCustom:=1, Orientation:=xlTopToBottom
    On Error GoTo Err_RefreshPareto


    '\clear cumulative percent column next to pivot table
    pvt.TableRange2.Offset(0, pvt.TableRange2.Columns.Count).Resize(, 1).Clear


    '\ finds the range that will be used for the chart
    If pvt.ColumnGrand = True Then 'has grand total row
    Set rngChart = pvt.TableRange1.Offset(1).Resize(pvt.TableRange1.R ows.Count - 2, pvt.TableRange1.Columns.Count + 1)
    Else
    Set rngChart = pvt.TableRange1.Offset(1).Resize(pvt.TableRange1.R ows.Count - 1, pvt.TableRange1.Columns.Count + 1)
    End If

    '\ sets the column where percentages will be calculated
    Set rngPercent = Intersect(rngChart.Columns(rngChart.Columns.Count) , pvt.DataBodyRange.EntireRow)
    rngPercent.Select
    '\ put formula in each cell of the percent column
    i = 1
    For Each rngCell In rngPercent
    rngCell.Formula = "=sum(" & rngPercent.Offset(0, -1).Resize(i, 1).Address & ")/Sum(" & rngPercent.Offset(0, -1).Address & ")"
    i = i + 1
    Next rngCell
    '\ adds title to percent column
    rngPercent.Offset(-1, 0).Resize(1, 1).Value = "Dist %"
    '\ set the number format of percent column
    rngChart.Columns(rngChart.Columns.Count).NumberFor mat = "0.0%"
    '\ resets the chart range

    cht.ChartWizard Source:=rngChart
    On Error GoTo Err_RefreshPareto
    rngChart.Interior.ColorIndex = 15


    Application.Goto Reference:=ActiveSheet.Range("A1"), Scroll:=True

    Set pvt = Nothing
    Set rngChart = Nothing
    Set rngPercent = Nothing
    Set cht = Nothing
    Set rngCell = Nothing
    Set varTopN = Nothing

    Exit Sub
    Err_RefreshPareto:
    MsgBox "Chart on sheet " & ActiveSheet.Name & " may not have been updated correctly", vbExclamation
    Call ErrorMessage("RefreshPareto", Err)

    End Sub
    ----------------------

    Please help me how to change the code and where to change the code...

    Thanks
    Sagar
    Attached Images Attached Images

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    VERY hard to tell from those pictures but perhaps you simply need to specify the plotby argument.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory for reply...

    Code :
    ---------------
    '\ finds the range that will be used for the chart
    If pvt.ColumnGrand = True Then 'has grand total row
    Set rngChart = pvt.TableRange1.Offset(1).Resize(pvt.TableRange1.R ows.Count - 2, pvt.TableRange1.Columns.Count + 1)
    Else
    Set rngChart = pvt.TableRange1.Offset(1).Resize(pvt.TableRange1.R ows.Count - 1, pvt.TableRange1.Columns.Count + 1)
    End If

    '\ sets the column where percentages will be calculated
    Set rngPercent = Intersect(rngChart.Columns(rngChart.Columns.Count) , pvt.DataBodyRange.EntireRow)
    rngPercent.Select
    '\ put formula in each cell of the percent column
    i = 1
    For Each rngCell In rngPercent
    rngCell.Formula = "=sum(" & rngPercent.Offset(0, -1).Resize(i, 1).Address & ")/Sum(" & rngPercent.Offset(0, -1).Address & ")"
    i = i + 1
    Next rngCell
    '\ adds title to percent column
    rngPercent.Offset(-1, 0).Resize(1, 1).Value = "Dist %"
    '\ set the number format of percent column
    rngChart.Columns(rngChart.Columns.Count).NumberFor mat = "0.0%"
    '\ resets the chart range

    cht.ChartWizard Source:=rngChart
    ---------------

    Last line, Cht.ChartWizard, when we debug I haven't received any error message, it simply removed the arc data i.e., Dist % and shows the data as per previous attached file.

    Please guide me...

    Thanks
    Sagar

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Perhaps you missed my points:
    1. I can't tell very much from some garbled pictures
    2. The chartwizard method has many arguments, one of which is whether to plot by rows or by columns. My guess (because I cant' tell anything from your pictures) is that the default is not what you want so you should specify whether to plot by rows or columns.

    To be honest I can't see why you don't just use the SetSourceData method
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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