CODEWITHSUNDEEP
excelvbatrendline
D.Vas
D.Vas

Reputation: 47

VBA: Trendline add or remove (if exists)

I believe this one will be very quick one. I've written a code that allows to add trendline to the chart if there're certain slicer item selected. However, I wanto to include both Add and Remove trendline depending on the condition (If selected, remove and opposite). The code worked when it was split into 2 subs, but when I include & modify it doesn't. The code fails if statement: If x.Selected Then. However, the problem I think is in If ActiveChart.SeriesCollection(1).Trendlines(1).Selected. How can it be tested if there's a trendline already? If yes - remove, if no - add. Simple as that.

Sub trend_add_remv()

Dim x As Excel.SlicerItem, slicer_x As Excel.SlicerCache

Set slicer_x = ActiveWorkbook.SlicerCaches("Slicer_x")

Application.ScreenUpdating = False

For Each x In slicer_x.SlicerItems

  If x.Selected Then 'This part always fails despite the condition is true
      ActiveSheet.ChartObjects("Chart 1").Activate
          If ActiveChart.SeriesCollection(1).Trendlines(1).Selected Then
             ActiveChart.SeriesCollection(1).Trendlines(1).Delete
             ActiveSheet.ChartObjects("Chart 1").Selected = False
          Else
             With ActiveChart
            .SeriesCollection(x.Value & " - " & "Actual Sales").Select
            .SeriesCollection(x.Value & " - " & "Actual Sales").Trendlines.Add
             End With
             ActiveSheet.ChartObjects("Chart 1").Selected = False
          End If
  End If

  On Error GoTo Message

  Next x
  Exit Sub

  Message:
  MsgBox "No actual sales or not selected in the slicer!"

  Application.ScreenUpdating = True

  End Sub

Can anyone help me find the solution and give a brief explanation (as part of my learning) why this happened? I would appreciate :)

Upvotes: 0

Views: 4021

Answers (1)

D.Vas
D.Vas

Reputation: 47

Thanks for John Coleman's answer, the code now works, and here's the resolution: Sub trendline_add()

Dim x As Excel.SlicerItem, slicer_x As Excel.SlicerCache

Set slicer_x = ActiveWorkbook.SlicerCaches("Slicer_x")

Application.ScreenUpdating = False

For Each x In slicer_x.SlicerItems

If x.Selected Then
    ActiveSheet.ChartObjects("Chart 1").Activate
        If ActiveChart.SeriesCollection(x.Value & " - " & "Actual _
           Sales").Trendlines.Count > 0 Then
          ActiveChart.SeriesCollection(x.Value & " - " & "Actual _
          Sales").Trendlines(1).Delete
        Else
          ActiveChart.SeriesCollection(x.Value & " - " & "Actual Sales").Select
          ActiveChart.SeriesCollection(x.Value & " - " & "Actual Sales").Trendlines.Add
        End If
End If

On Error GoTo Message

Next x
Exit Sub

Message:
MsgBox "No actual sales or not selected in the slicer"

Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions