Graphing macro works in 2003 but doesn't have some features in 2007

[mgphill]

I am having difficulty with a graphing macro. The macro was written for Excel 2003; however, I am now using the program on Excel 2007 and some of the features do not function in 2007. The macro was written to make trendlines of a certain set of data, there used to be an option that would allow you to choose the color of the trendline as well as the type of trendline you wanted (ie linear, polyfit). Can anyone help me figure out what may have happened and a possible solution?
Thank You

[Richard Buttrey]

Hi,

You'll need to post the macro that you say isn't working.

Trendlines can be changed to a different type and of course the colour can be varied.

[mgphill]

Here are the subroutines and functions. I didn't write the macro, so I'm not 100% sure what's going on. It seems as if chosing what type of trendline is working, but I'm not sure how to handle the part where it asks what color you want it to be.
Thank You

Sub TrendALine()
    Dim Switch As Integer
    Dim seriesauto As Integer
    Switch = 0
    seriesauto = 1
    Call Check_Empty_ChartObjects
    Call TrendALineAuto(Switch, seriesauto)
End Sub

Sub Check_Empty_ChartObjects()

    Dim iSheetNum As Integer
    Dim iChNumber As Integer
    Dim x As Variant
    
    For iSheetNum = 3 To Worksheets.Count
       For iChNumber = 1 To Worksheets(iSheetNum). _
          ChartObjects.Count
          On Error GoTo Error_Handler
          Worksheets(iSheetNum).ChartObjects(iChNumber).Chart.SeriesCollection (1)
        Next
    Next
    Exit Sub
Error_Handler:
    MsgBox ("Error, There must be a series graphed on every chart in order to use this button.")
    End
End Sub

Sub TrendALineAuto(Switch, seriesauto)
    Dim Message As String
    Dim title As String
    Dim default As Variant
    Dim iSheetNum As Integer
    Dim iChNumber As Integer
    Dim series As Integer
    Dim Direct As Variant
    Dim PolyOrder As Integer
    Dim trendNum As Integer
    Dim x As Integer
    Dim sSearch As Integer
    Dim NameRow As Integer
    Dim trendcolor As Variant
    Dim trendweight As Variant
    Dim trendstyle As Variant
    Dim trendtype As Variant
    Dim trendorder As Variant
    Dim trendforward As Variant
    Dim trendbackward As Variant
    Dim trendintercept As Variant
    Dim trenddisplayform As Variant
    Dim trendname As Variant
    Dim trenddisplayR2 As Variant
    Dim path As Variant
    
    If Switch = 0 Then
    
    
    Message = "Enter Series Number for Trending"
    title = "Trend Line"
    series = Val(InputBox(Message, title, default))
    If series = Empty Then End
    
    Do
    x = Worksheets(3).ChartObjects(1).Chart.SeriesCollection.Count
    If series <= x Then
    Exit Do
    Else
    Message = "The series entered doesn't exist, please try again."
    title = "Series Number"
    series = Val(InputBox(Message, title, default))
    If series = Empty Then End
    End If
    Loop
    Worksheets(3).ChartObjects(1).Activate
    On Error Resume Next
    ActiveChart.SeriesCollection(series).Trendlines(1).Delete
    ActiveChart.SeriesCollection(series).Select
    Application.Dialogs(xlDialogChartTrend).Show
    ActiveChart.SeriesCollection(series).Trendlines(1).Select
    Application.Dialogs(xlDialogPatterns).Show
    Application.ScreenUpdating = False
    
    With Selection.Border
        trendcolor = .ColorIndex
        trendweight = .weight
        trendstyle = .LineStyle
    End With
    With Selection
        trendtype = .Type
        trendorder = .Order
        trendforward = .Forward
        trendbackward = .Backward
        trendintercept = .Intercept
        trenddisplayR2 = .DisplayRSquared
        trenddisplayform = .DisplayEquation
        trendname = .Name
    End With
    
    For iSheetNum = 3 To Worksheets.Count
       For iChNumber = 1 To Worksheets(iSheetNum). _
          ChartObjects.Count
          If iSheetNum = 3 And iChNumber = 1 Then
          ActiveChart.SeriesCollection(series).Trendlines(1).Select
          With Selection.Border
          .ColorIndex = trendcolor
          .weight = trendweight
          .LineStyle = trendstyle
          End With

          Else
          On Error Resume Next
          Worksheets(iSheetNum).ChartObjects(iChNumber).Activate
          ActiveChart.SeriesCollection(series).Trendlines(1).Delete
          Set Direct = Worksheets(iSheetNum).ChartObjects(iChNumber).Chart.SeriesCollection(series)
          Direct.Trendlines.Add Type:=trendtype, Order:=trendorder _
        , Forward:=trendforward, Backward:=trendbackward, DisplayEquation:=trenddisplayform, DisplayRSquared:=trenddisplayR2 _
        , Name:=trendname
        Set path = Worksheets(iSheetNum).ChartObjects(iChNumber).Chart.SeriesCollection(series).Trendlines(1)
        With path.Border
          .ColorIndex = trendcolor
          .weight = trendweight
          .LineStyle = trendstyle
        End With
        End If
       Next
    Next
    Application.ScreenUpdating = False
    End
    
    Else
        series = seriesauto
        PolyOrder = 3
    End If
    
    
    Call CountTrendlines(trendNum)
    
    For iSheetNum = 3 To Worksheets.Count
       For iChNumber = 1 To Worksheets(iSheetNum). _
          ChartObjects.Count
          Set Direct = Worksheets(iSheetNum).ChartObjects(iChNumber).Chart.SeriesCollection(series)
        Direct.Trendlines.Add Type:=xlPolynomial, Order:=PolyOrder _
        , Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False
        
        With Direct.Trendlines(1)
        .Border.ColorIndex = xlAutomatic
        .Border.weight = xlMedium
        If trendNum = 0 Then
        .Border.LineStyle = xlDash
        ElseIf trendNum = 1 Then
        .Border.LineStyle = xlDashDot
        Else
        .Border.LineStyle = xlDot
        End If
        sSearch = series
        NameRow = FindRow(sSearch)
        If Worksheets("Main").Cells(NameRow + 1, 1) <> Empty Then
        .Name = Worksheets("Main").Cells(NameRow + 1, 1).Value
        Else
        .Name = "Trendline Series " & series
        End If
        End With
        
        With Direct
        .Border.ColorIndex = xlNone
        .Border.LineStyle = xlNone
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        End With
        
        Next
    Next
    Call UpdateLegend
    
End Sub

Sub CountTrendlines(trendNum)
    
    Dim I As Integer
    Dim trendNumsave As Integer
    
    For I = 1 To Worksheets(3).ChartObjects(1).Chart.SeriesCollection.Count
        trendNum = Worksheets(3).ChartObjects(1).Chart.SeriesCollection(I). _
        Trendlines.Count
        trendNum = trendNumsave + trendNum
        trendNumsave = trendNum
    Next
End Sub

[Richard Buttrey]

Hi,

In your TrendALineAuto procedure include code like the following. I've also included the trendline weight and style. Just delete if not necessary.

ActiveSheet.ChartObjects(1).Activate 

With ActiveChart.SeriesCollection(1).Trendlines(1)
    .Type = xlExponential ' or 'xllinear  etc...
    .Border.ColorIndex = 5 ' change colour value  as  necessary
    .Border.Weight = xlThick 
    .Border.LineStyle = xlContinuous
     
End With

[mgphill]

Is there anyway to get the macro to pause at the format trendline screen so that I can manually choose which line fit I want and then choose which color I want manually? The biggest thing I am doing is I am updating about 30 plots and I do not want to have to manually go in and add a trendline and then format it by hand. I want to be able to just have one "Format Trendline" box, where I can select the line fit and the line color/style, that will update all the plots with the trendline I have selected.
Thank You

[Richard Buttrey]

Hi,

Perhaps the easiest way would be to have a couple of Data Validation drop down cells, one containing a list of trendline types and the other a list of colours. Then read those cell values in the macro rather than have them hard coded.

Regards

[mgphill]

Is there a way to pause the macro so I can select the features I want from the "format trendline" screen? Like could it pause until I have selected the stuff I would like and then I could press enter to have the macro continue? Thank You

[Richard Buttrey]

Is there a way to pause the macro so I can select the features I want from the "format trendline" screen? Like could it pause until I have selected the stuff I would like and then I could press enter to have the macro continue? Thank You

...have you tried the code?

That's exactly what the line
Application.Dialogs(xlDialogChartTrend).Show

does.

[mgphill]

That is what brings the dialog box up, but when I just run the macro, that box doesn't stay up long enough for me to click what I want.