Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing PivotChart Data Labels Appearance (2003)

    I'm trying to programmatically modify the visibility and format of selected data labels for the VapPivot form PivotChart of the attached mdb so, on the startup form, I set the "begins" date to April 1, 2003 and the "ends" date to April 30, 2003, select the "Como lake level" option button but when I click on the Chart button I get the message:
    Run-time error '438':
    Property or method not supported by the object

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

    Re: Changing PivotChart Data Labels Appearance (2003)

    Lot of problems.
    <UL><LI>By putting parentheses around ch in the call to FormatSeriesLabel, you are calling the argument by value, but that is not possible if you want to modify the chart.
    <LI>If you remove the parentheses, the error message changes, it says that you can't modify the object in an event handler.
    <LI>So you must call FormatSeriesLabel elsewhere, for example in the On Click procedure of the Chart button on Grafici, after opening VapPivot:

    FormatSeriesLabel Forms!VapPivot.ChartSpace.Charts(0)

    <LI>However, the code then errors at the line

    dlSeries1Labels.Item(2) = True

    You can't just set an item to True. What did you mean here? Did you omit a property?[/list]

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing PivotChart Data Labels Appearance (2003)

    Thank you Hans, yes, I did omit a property <img src=/S/blush.gif border=0 alt=blush width=15 height=15>, the correct line is:
    dlSeries1Labels.Item(2).Font.Bold = True
    and now everything works fine except for the fact that the line:
    <font color=448800>dlSeries1Labels.Item(3).Visible = False</font color=448800>
    in Sub FormatSeriesLabel(chartspace1)
    doesn't seem to work because I can still see the data label for that item in the pivot chart.
    Why is it so?

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

    Re: Changing PivotChart Data Labels Appearance (2003)

    The pivot chart has several sets of data labels overlaid on each other. Each time you open the form, a new set is added. So if you really want to run the code each time, you must first delete all the old sets before adding a new one.

    Sub FormatSeriesLabel(chartspace1)
    Dim serSeries1 As ChSeries
    Dim dlSeries1Labels As ChDataLabels
    Dim i As Integer

    ' Set a variable to the first series of the first chart in Chartspace.
    Set serSeries1 = chartspace1.SeriesCollection(0)

    ' Delete existing data labels
    For i = serSeries1.DataLabelsCollection.Count - 1 To 0 Step -1
    serSeries1.DataLabelsCollection.Delete i
    Next i

    ' Add a set of data labels to the first series and return a DataLabels object.
    Set dlSeries1Labels = serSeries1.DataLabelsCollection.Add

    dlSeries1Labels.Item(2).Font.Bold = True
    dlSeries1Labels.Item(2).Font.Color = "Red"
    dlSeries1Labels.Item(3).Visible = False
    End Sub

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing PivotChart Data Labels Appearance (2003)

    Thank you Hans,
    How come now I get a "Type mismatch" error when I open the pivot chart?

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

    Re: Changing PivotChart Data Labels Appearance (2003)

    Please provide enough information for us to help you. I don't get the error, so you'll have to indicate on which line the error occurs.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing PivotChart Data Labels Appearance (2003)

    Huge screenshot cropped by HansV - please don't post images over 640x480 pixels.

    When I click on the Chart button the code stops at the line you see in the picture below and if I then click on the Continue button in the VBA editor toolbar, the Type Mismatch error message box appears.

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

    Re: Changing PivotChart Data Labels Appearance (2003)

    Does the error go away if you revert to omitting the data types for SerSeries1 and dlSeries1Labels?

    Dim SerSeries1
    Dim dlSeries1Labels

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing PivotChart Data Labels Appearance (2003)

    Yes, it does!!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Thank you Hans.
    I've added a few lines of code to the FormatSeriesLabel sub because the objective is for the chart to only display data labels for the maximum and minimum values of the first(and only) series but I still get the "Type mismatch" error message. What's the cause this time?

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

    Re: Changing PivotChart Data Labels Appearance (2003)

    You declare

    Dim p As ChPoint

    and use

    For Each p In serSeries1.Points

    and

    For Each p In serSeries1

    The latter is not valid. Why not use

    For Each p In serSeries1.Points
    If p.GetValue(chDimValues) > a Then
    a = p.GetValue(chDimValues)
    b = p.Index
    ElseIf p.GetValue(chDimValues) < a Then
    a = p.GetValue(chDimValues)
    c = p.Index
    End If
    Next p

    (What happens if p.GetValue(chDimValues) = a, by the way?)

    The For Each In dlSeries1Labels loop is invalid too. It should be

    For i = 0 To dlSeries1Labels.Count - 1
    If i <> b Or i <> c Then
    dlSeries1Labels.Item(i).Visible = False
    Else
    dlSeries1Labels.Item(i).Font.Bold = True
    dlSeries1Labels.Item(i).Font.Color = "Red"
    dlSeries1Labels.Item(i).Visible = True
    End If
    Next i

    I don't think it will do what you want, but I don't have the slightest idea what your code does or should do, so I cannot offer suggestions.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing PivotChart Data Labels Appearance (2003)

    Thank you so much Hans,
    I've followed through on your suggestions and when I open the pivot chart form the "Type mismatch" error message still appears. The objective of the code is to make it so that the pivot chart shows data labels only for the points that are at a maximum or minimum value in the series.

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

    Re: Changing PivotChart Data Labels Appearance (2003)

    The code to calculate min and max didn't make sense. See if the attached version works for you.

    BTW: I applied consistent indentation. Code with haphazard indentation is hard to follow.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing PivotChart Data Labels Appearance (2003)

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>Perfect! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Thank you Hans.
    I see you used GetValue(2). Is 2 the numeric equivalent of chDimValues?
    I ask you this because help on line for the GetValue method only specifies literal enumeration constants, not their numeric counterparts.

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

    Re: Changing PivotChart Data Labels Appearance (2003)

    Giorgio,

    To avoid problems with references, I removed the reference to the Microsoft Office Web Components library (OWC11 for Office 2003)
    As a consequence, I had to replace chDimValues with its numeric value 2. I found this value by looking it up in the Object Browser before removing the reference to OWC11.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing PivotChart Data Labels Appearance (2003)

    Amazing how you can drive pivot charts even without any reference to the Microsoft Office Web Components library.

    I'd like to use the same Grafici form to open different pivot charts dependent on which value is selected in the option group so I've modified the the On Click procedure of the Chart button by moving the argument value for the FormatSeriesLabel sub inside each case block in the Select Case statement but now when I click the Chart button I get a message saying, "Can't find the VapPivot form referred to in a macro or VBA code."

Page 1 of 2 12 LastLast

Posting Permissions

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