assign different chart line type under different condition with vba

Hi all,

As above, there are only two type of line and condition to consider here.

When condition 1 is met, the line is solid and condition 2, the line is dash.

Want to create a vba which link to excel cell when the condition are met, the line change itself.

There is Private Sub Worksheet_Change(ByVal Target As Range) in my file. No sure if this is the place to put the macro. Just let you know.

Here is the two macro I get from the record macro:

'
' Macro1 Macro
'

'
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.SeriesCollection(12).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineSysDash < link to excel cell. probably need  to change to something like Range("A100").Value.
    End With
    ActiveCell.Offset(3, 0).Range("A1").Select
End Sub

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.SeriesCollection(12).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineSolid
    End With
    ActiveCell.Offset(-2, 0).Range("A1").Select
End Sub

Appreciated any help given.

Hello DavidRoger

Can you tell what values that cell can have (ie. 1 or 2, Solid or Dash etc.)

Private Sub Worksheet_Change(ByVal Target As Range)

  With Target                                              'the cell you change
    If .Address = "$D$2" Then                              'address of that cell
      With Me.ChartObjects("Chart 8").Chart.SeriesCollection(12).Format.Line  'the serie of your chart
        .Visible = msoTrue
        .DashStyle = IIf(Target.Value > 0, msoLineSolid, msoLineSysDash)  '< link to excel cell. probably need  to change to something like Range("A100").Value.
      End With
      Application.Goto .Offset(IIf(.Value > 0, 3, -2)), False  'depend on value 3 columns goto to the right or 2 to the left
    End If
  End With

End Sub

Hi all,

I think I need to rephrase my message. Found something interesting after going through my vba.

Below is the code I use for .values, xvalues, line weight, name, and other:

Private Sub Worksheet_Change(ByVal Target As Range)

    With .ChartObjects("Chart 5").Chart

       With .SeriesCollection(8)
        .Values = Range("A100").Value
        .XValues = Range("A101").Value <<<The excel cell is doing all the conditioning (formula with 
       End With                           IF function, etc.)
         
    End With

End Sub

Below code is the amended code I get from the above code. However this will not work because Range ("A100").value is for number not for text. Tried replacing with [A100] but not working too. What else can be use in the ??? below?

Private Sub Worksheet_Change(ByVal Target As Range)

    With .ChartObjects("Chart 5").Chart
        
       With .SeriesCollection(13)
        .Format.Line.Visible = msoTrue <<< do I need this line?
        .Format.Line.DashStyle = ??? <<<link to excel cell and is conditioned to show msoLineSolid 
       End With                         or msoLineSysDash)   
                              
    End With
         
End Sub

All help is appreciated and Happy New Year .

Originally Posted by Andy Pope

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count = 1 Then
        If Not Intersect(Target, Range("A100")) Is Nothing Then
            With Me.ChartObjects("Chart 1").Chart
                
               With .SeriesCollection(1)
                .Format.Line.Visible = msoTrue '<<< do I need this line?
                .Format.Line.DashStyle = IIf(StrComp([a100], "msoLineSysDash", vbTextCompare) = 0, msoLineSysDash, msoLineSolid)
               End With
                                      
            End With
        End If
    End If
End Sub

msoLineSysDash and msoLineSolid are VBA constants that have numeric values. The above checks for text match and then uses the constant.

Hi Andy,

This is what I do. I copy and paste the code into my vba. Enter cell A100 with msoLineSysDash or msoLineSolid. It's work.

I am prefer sure that not what you mean.

Other that than I tried

1) to change the line type manual, but nothing happen to A100.

2) And tried to insert A100 with number 1 - 100, also nothing happen.

Please confirm "The above checks for text match and then uses the constant".

Originally Posted by Andy Pope

Yes, just make sure the references to range and chart object are correct for your file.

Thanks.

Wish you good blessing in 2014.

When there are more than two conditions, in what way can the code be amended?

use select case or a 2nd Iif (but here it's twice the same condition and change the x in a valid expression)

          .Format.Line.DashStyle = IIf(StrComp([a100], "msoLineSysDash", vbTextCompare) = 0, msoLineSysDash, IIf(StrComp([a100], "msoLineSysDash", vbTextCompare) = 0, x, msoLineSolid))

Thanks both.

IIF and SELECT case is good.