在Excel VBA中编辑和命名新添加的图表

问题描述：

我想在Excel VBA中编写一个宏，它添加了一个图表，然后想重新命名并编辑列的颜色，但不知何故它会引发调试错误。在Excel VBA中编辑和命名新添加的图表

这是我的代码。是否有人可以帮助我：

Sub Charts() 

ActiveSheet.Shapes.AddCha rt.Select 
ActiveChart.ChartType = xlColumnStacked100 
ActiveChart.SetSourceData Source:=Sheets("Calculations").Range("A1:D11") 
ActiveChart.Name = "MyChart" 
ActiveChart.SeriesCollection(1).XValues = "=Data!$N$5:$N$14" 
ActiveChart.SeriesCollection(3).Select 
    ActiveChart.Legend.Select 
    ActiveChart.Legend.LegendEntries(1).Select 
    With Selection.Format.Fill 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(255, 0, 0) 
     .Transparency = 0 
     .Solid 
    End With 
    With Selection.Format.Line 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(0, 0, 0) 
     .Transparency = 0 
    End With 
    ActiveChart.Legend.LegendEntries(2).Select 
    With Selection.Format.Fill 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(255, 192, 0) 
     .Transparency = 0 
     .Solid 
    End With 
    With Selection.Format.Line 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(0, 0, 0) 
     .Transparency = 0 
    End With 
    ActiveChart.Legend.LegendEntries(3).Select 
    With Selection.Format.Fill 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(0, 176, 80) 
     .Transparency = 0 
     .Solid 
    End With 
    With Selection.Format.Line 
     .Visible = msoTrue 
     .ForeColor.ObjectThemeColor = msoThemeColorText1 
     .ForeColor.TintAndShade = 0 
     .ForeColor.Brightness = 0 
     .Transparency = 0 
    End With 
    ActiveChart.SeriesCollection(3).Select 
    ActiveChart.Axes(xlValue).MajorGridlines.Select 
    Selection.Delete 
    End Sub

感谢

哪一行产生错误，错误到底是什么？ – vacip

对于此：ActiveChart.Name =“MyChart”，错误是：指定的维度对当前图表类型无效。 – user1778266

对于此：使用Selection.Format.Fill，错误是对象'ChartFormat'的方法'填充'失败 – user1778266

答

所以，请将.Name属性只能用于图表工作表设置。对于嵌入式图表（图表对象）它是只读的，因此您无法为其分配值。您可以为其容器的名称指定一个值：

ActiveChart.Parent.Name = "MyChart"

而不是尝试格式化图例条目，自己格式化系列。我也重写了您的.with语句，在格式化之前不需要选择每个项目：

Sub ChartThingy() 

ActiveSheet.Shapes.AddChart.Select 
ActiveChart.ChartType = xlColumnStacked100 
ActiveChart.SetSourceData Source:=Sheets("Calculations").Range("A1:D11") 
ActiveChart.Parent.Name = "MyChart" 
ActiveChart.SeriesCollection(1).XValues = "=Data!$N$5:$N$14" 
    With ActiveChart.SeriesCollection(3).Format 
    With .Fill 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(255, 0, 0) 
     .Transparency = 0 
     .Solid 
    End With 
    With .Line 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(0, 0, 0) 
     .Transparency = 0 
    End With 
    End With 
    With ActiveChart.SeriesCollection(2).Format 
    With .Fill 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(255, 192, 0) 
     .Transparency = 0 
     .Solid 
    End With 
    With .Line 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(0, 0, 0) 
     .Transparency = 0 
    End With 
    End With 
    With ActiveChart.SeriesCollection(1).Format 
    With .Fill 
     .Visible = msoTrue 
     .ForeColor.RGB = RGB(0, 176, 80) 
     .Transparency = 0 
     .Solid 
    End With 
    With .Line 
     .Visible = msoTrue 
     .ForeColor.ObjectThemeColor = msoThemeColorText1 
     .ForeColor.TintAndShade = 0 
     .ForeColor.Brightness = 0 
     .Transparency = 0 
    End With 
    End With 
    ActiveChart.Axes(xlValue).MajorGridlines.Select 
    Selection.Delete 
End Sub

工作！非常感谢：） – user1778266

在Excel VBA中编辑和命名新添加的图表

相关推荐