How to modify the font format of some series value in a pivot chart?

[Harry Covair]

Hi the forum,
I try to write a VBA application to analyze the results of the participants in several events.

As you can see on the picture in attachment, some participants do not complete all events.

Based on the pivot table, a pivot chart is build.
The name of the participants who do not complete all the events should be written in italic.

Is it possible?

If yes, does some member of the forum could help me with an example of the needed code?

Are more explanations needed, please ask.

Thanks in advance

Harry

 

[p45cal]

Yes you can with a statement such as:

ActiveChart.Legend.LegendEntries(1).Format.TextFrame2.TextRange.Font.Italic = msoTrue

but of course, this would need adjusting to apply only to the right names.
An example workbook will be needed.

 

[Harry Covair]

Hi p45cal,
Thanks for your answer.
I guess, if the number of events (or test) for a participant is less than the number of events than should his name be written in italic.
But technically, i'm not able to translate it in VBA.
You will find an example workbook in attachment.
Are more information needed, please ask.

Harry

 

[p45cal]

Your macro with multiple changes and a bit tacked onto the end:

Sub Make_PT()
Dim Ch As Object, pt As PivotTable, yyy As LegendEntries, xxx As SeriesCollection, i As Long, v
Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
          "Data!R1C1:R25C5", Version:=xlPivotTableVersion14).CreatePivotTable _
          (TableDestination:="Data!R2C8", DefaultVersion:= _
          xlPivotTableVersion14)
Sheets("Data").Select
'Cells(2, 8).Select
With pt
  .AddDataField pt.PivotFields("Rank"), "Sum of Rank", xlSum
  With .PivotFields("Participants")
    .Orientation = xlColumnField
    .Position = 1
  End With
  With .PivotFields("Tests")
    .Orientation = xlRowField
    .Position = 1
  End With
  .ColumnGrand = False
  .RowGrand = False
End With
Range("A1").Select
Set Ch = ActiveSheet.Shapes.AddChart
Set Ch = Ch.Chart
Ch.ChartType = xlLineMarkers
Ch.SetSourceData Source:=Range("Data!$H$2:$Q$6")
Set yyy = Ch.Legend.LegendEntries
Set xxx = Ch.SeriesCollection
For i = 1 To xxx.Count
  For Each v In xxx(i).Values
    If IsEmpty(v) Then
      yyy(i).Format.TextFrame2.TextRange.Font.Italic = msoTrue
      Exit For
    End If
  Next v
Next i
End Sub

 

[Harry Covair]

Hi p45cal,

Thanks a lot. That was exactly what I was looking for.

The improved code is also significant.

Have a nice day
Harry