英文:
Add legend to each point with VBA
问题
我想在我的Excel图表中添加图例，如下所示。
我可以控制每个数据点的颜色，但是我找不到如何编辑这个图例的方法。（在图片上，我是通过手动添加标签来创建和编辑它的。）
我想要使用类似于 cht.Legend.LegendEntries(1).Value = "lol" 的方法。
英文:
I would like to add a legend to some points in my Excel chart as you can see below.
I can control each point color, however I can't find how to edit this legend. (On the picture I created and edited it manually by adding a tag.)
I wanted to use something like cht.Legend.LegendEntries(1).Value = "lol".
答案1
得分: 0
Dim chSer As Series, j As Long, i As Long, SerPoints As Points
Dim shCh As Chart
Set shCh = ActiveChart
With shCh
For Each chSer In .SeriesCollection
Set SerPoints = chSer.Points
j = SerPoints.count
For i = 1 To j
SerPoints(i).ApplyDataLabels Type:=xlDataLabelsShowBubbleSizes
SerPoints(i).DataLabel.Text = "无论什么" & i
Next
Next
End With
正确声明 chChar 和 SerPoints 可能会非常有帮助。您可以使用 VBA 智能感知来查看所有 DataLabel 属性，不仅如此...
例如，您可以在图表系列附近的一列中添加一些所谓的标签并将其命名为“DataLabels”，然后使用以下代码自动应用其中包含的字符串：
Dim rng as Range
Set rng = sh.Range(sh.Parent.Names("DataLabels").RefersToRange.Address)
'并以以下方式添加数据：
SerPoints(i).DataLabel.Formula = "=" & sh.Name & "!" & sh.Cells(rng.Row + i - 1, rng.Column).Address
英文:
Dim chSer As Series, j As Long, i As Long, SerPoints As Points
Dim shCh As Chart
Set shCh = ActiveChart
With shCh
For Each chSer In .SeriesCollection
Set SerPoints = chSer.Points
j = SerPoints.count
For i = 1 To j
SerPoints(i).ApplyDataLabels Type:=xlDataLabelsShowBubbleSizes
SerPoints(i).DataLabel.Text = "Whatever" & i
Next
Next
End With
Correctly declaring chChar and SerPoints, can be very helpful. You can use VBA intellisense to see all DataLabel properties and not only that...
For instance, you can add some definitions for so named labels on a column near the chart series and name it "DataLabels", then use the next code to automatically apply the contained strings:
Dim rng as Range
Set rng = sh.Range(sh.Parent.Names("DataLabels").RefersToRange.Address)
'and add the data in this way:
SerPoints(i).DataLabel.Formula = "=" & sh.Name & "!" & sh.Cells(rng.Row + i - 1, rng.Column).Address
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论