• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Formatting of Chart Lines throwing the error in Line Chart.

M

Manan

New Member
Hi All,

I have a line chart , where I have line A and Line B . I am writing a macro for auto-formatting of the lines like color of line A should be Black and Width is 1.25. For Line B I am setting few more properties like Color = RED , width =1.25 , Under Shadow property -- Transperency = 58% , Blur =0 , Distance = 21 .

But I am getting the error in the VBA code below . Error always coming on line Selection.Format.Line saying Object does not support this method or Property.

Can someone help please.

Code: 
Sub ChartFormat()
'
' ChartFormat Macro
'

'
  With Selection.Format.Line
  .Visible = msoTrue
  .ForeColor.ObjectThemeColor = msoThemeColorAccent1
  .ForeColor.TintAndShade = 0
  .ForeColor.Brightness = 0
  End With
  With Selection.Format.Line
  .Visible = msoTrue
  .ForeColor.ObjectThemeColor = msoThemeColorText1
  .ForeColor.TintAndShade = 0
  .ForeColor.Brightness = 0
  .Transparency = 0
  End With
  Application.CommandBars("Format Object").Visible = False
  ActiveChart.Legend.LegendEntries(2).Select
  With Selection.Format.Line
  .Visible = msoTrue
  .Weight = 1.25
  End With
  With Selection.Format.Line
  .Visible = msoTrue
  .ForeColor.ObjectThemeColor = msoThemeColorText1
  .ForeColor.TintAndShade = 0
  .ForeColor.Brightness = 0
  .Transparency = 0.0400000215
  End With
  With Selection.Format.Line
  .Visible = msoTrue
  .ForeColor.RGB = RGB(192, 0, 0)
  .Transparency = 0.0400000215
  End With
  With Selection.Format.Line
  .Visible = msoTrue
  .Weight = 1.25
  End With
  With Selection.Format.Shadow
  .Type = msoShadow25
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 4
  .OffsetX = 3.9993907806
  .OffsetY = -0.0698096257
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0.5686300099
  .Size = 100
  End With
  With Selection.Format.Shadow
  .Type = msoShadow25
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 4
  .OffsetX = 3.9993907806
  .OffsetY = -0.0698096257
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0.5686300099
  .Size = 110
  End With
  Selection.Format.Shadow.Type = msoShadow32
  Selection.Format.Shadow.Type = msoShadow31
  Selection.Format.Shadow.Type = msoShadow30
  Selection.Format.Shadow.Type = msoShadow23
  Selection.Format.Shadow.Type = msoShadow26
  With Selection.Format.Shadow
  .Type = msoShadow26
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 4
  .OffsetX = -3
  .OffsetY = 3.6739403974E-16
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0.3199999928
  .Size = 100
  End With
  With Selection.Format.Shadow
  .Type = msoShadow26
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 4
  .OffsetX = -3
  .OffsetY = 3.6739403974E-16
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0
  .Size = 100
  End With
  With Selection.Format.Shadow
  .Type = msoShadow26
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 0
  .OffsetX = -3
  .OffsetY = 3.6739403974E-16
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0
  .Size = 100
  End With
  With Selection.Format.Shadow
  .Type = msoShadow26
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 0
  .OffsetX = -21
  .OffsetY = 2.5717582782E-15
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0
  .Size = 100
  End With
  With Selection.Format.Shadow
  .Type = msoShadow26
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 0
  .OffsetX = 20.9968015983
  .OffsetY = -0.3665005352
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0
  .Size = 100
  End With
  With Selection.Format.Shadow
  .Type = msoShadow26
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 0
  .OffsetX = 20.9968015983
  .OffsetY = -0.3665005352
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0.5699999928
  .Size = 100
  End With
  With Selection.Format.Shadow
  .Type = msoShadow26
  .Visible = msoTrue
  .Style = msoShadowStyleOuterShadow
  .Blur = 0
  .OffsetX = 20.9968015983
  .OffsetY = -0.3665005352
  .RotateWithShape = msoFalse
  .ForeColor.RGB = RGB(0, 0, 0)
  .Transparency = 0.5699999928
  .Size = 110
  End With
End Sub
 
Hi ,

Since all of the code is written to operate on Selection , what is being selected before the code is run ? Is it one of the series ?

Narayan
 
NARAYANK991 said:
Hi ,

Since all of the code is written to operate on Selection , what is being selected before the code is run ? Is it one of the series ?

Narayan
Click to expand...
Hi Narayan ,

Yes you are correct , the selection is Column A and Column B , by which I create a Line Chart. The Issue is the Data in Column A and Column B is changing every day and I require to do all the settings again and again , hence I am thinking of writing the macro which can format it once I run the macro and the task will be easy .
 
Hi Manan ,

The actual line chart series is to be selected before you run the macro ; click on any one of series , either the one for line A or the one for line B , and then run the macro.

Narayan
 
You must log in or register to reply here.
Back
Top