Line Weight

campster · #1 (**permalink**) 07-29-09, 11:18

The code below works in Excel 2007 but not in 2003.
Does anyone know the equivalent code in 2003 to chnage the weight of a line?

Code:

 
For Each oXLChart In oXLBookCurrent.Charts
    For Each oXLSer In oXLChart.SeriesCollection
      
      If Not sWeight = "" Then
        oXLSer.Format.Line.Weight = sWeight
      End If
      
    Next
  Next

weejas · #2 (**permalink**) 07-29-09, 12:13

The easiest way to find the code that you need is to record a macro doing that action. Then you just look at the code that the macro recorder generates, and ignore the bits that you don't need.

campster · #3 (**permalink**) 07-29-09, 12:52

The problem is that the line weight method has to belong to the seriesCollection object. I'm not sure that you can duplicate that from a macro.

campster · #4 (**permalink**) 07-29-09, 13:24

I tried weejas suggestion and built the following macro by selecting a series and setting it's width to a thick line.

Code:

 
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThick
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With

It took me awhile to realize that the line properties are contained inthe border object. After realizing that I change the code to the following and it appears to work.

Code:

 
      If Not sWeight = "" Then
        If Application.Version > 11 Then
           oXLSer.Format.Line.Weight = sWeight
        Else
          If sWeight > 4 Then
            sWeight = 4
            oXLSheetControl.Cells(20, 4).Value = 4
            MsgBox "Line weight cannot exceed 4." & vbCrLf & _
                   "Setting weight to max value.", _
                    vbOKOnly, _
                    "Line weight limit exceeded"
          End If
          oXLSer.Border.Weight = sWeight
        End If
      End If

Thanks for the suggestion weejas

weejas · #5 (**permalink**) 07-30-09, 06:16

You're welcome!

That piece of advice was the single most useful thing that I learned on a VBA course in 2005.

Internet Services

Web Development

Digital Marketing

Consumer Tech