Insert line in graph with macro

[elbartje]

I want to insert a line in a graph the x en y data needs to come from an userform (an input box is also ok).
I dont want to select a range of cells but directly enter a value(s) for the x and y data.
This data should not be put in cells but only in the graph itself

So I select a graph, run the macro I get a popup to enter x and y data for example X1=0 X2=10 and Y1=120 and Y2=120.
Now a horizontal line is plotted in the graph.

 

[RickXL]

Hi and welcome to the MrExcel Message Board!

You can do things like this:

Sub MakeChart()

    Dim x(20) As Variant
    Dim y(20) As Variant
    Dim i     As Long
    
    For i = 0 To 20
        x(i) = i * 2
        y(i) = 2 * Sin(i / 5)
    Next
 
    Charts.Add
    
    With ActiveChart
        .ChartType = xlLineMarkers
        .HasTitle = True
        .ChartTitle.Text = "Plot from Array"
        
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .AxisTitle.Characters.Text = "X"
        End With
        
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .XValues = x
            .Values = y
            .Trendlines.Add
            .Trendlines(1).DisplayEquation = True
            .Name = "Y"
        End With
    
    End With
    
End Sub

That draws a curve then fits a trend line to it.

If you replace the loop at the start of the code that populates the arrays with your code to get the data it should do what you want.

Regards,

 

[elbartje]

Thank you for your help already !

It's not exactly what I needed,
I dont want to make a new graph but just enter a few lines(limit lines) into an already existing graph (see the red lines in the left graph).
I made an example what I mean also the start of the macro in attachment.

the example

 

[RickXL]

Hi, thanks for the example. Very useful.

Try replacing your OK_Click program with this one:

Private Sub Ok_Click()
'press ok'
    Dim ch As ChartObject
    Dim x As Variant
    Dim y As Variant
    
    x = Array(CDbl(Range1.Text), CDbl(Range2.Text))
    
    With ActiveSheet.ChartObjects(1).Chart

        y = Array(CDbl(Upperlimit.Text), CDbl(Upperlimit.Text))
        With .SeriesCollection.NewSeries
            .Name = "Upper limit"
            .Values = y
            .XValues = x
            .Format.Line.Weight = 2
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .MarkerStyle = xlMarkerStyleNone
        End With

        y = Array(CDbl(Lowerlimit.Text), CDbl(Lowerlimit.Text))
        With .SeriesCollection.NewSeries
            .Name = "Lower limit"
            .Values = y
            .XValues = x
            .Format.Line.Weight = 2
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .MarkerStyle = xlMarkerStyleNone
        End With
    End With

End Sub

I have assumed that there will be only one chart on the ActiveSheet so ChartObjects(1).Chart will find it.
I have used arrays again but populated them from the UserForm controls. The CDbl changes the text replies into numbers.
Feel free to make any changes you like to the formatting options.

Basically, it finds the first chart then adds two new series to it (NewSeries).
Then it arranges these new series to draw your limit lines.

 

[elbartje]

Wow thank you, this is what I needed.
One extra question, is it possible that the macro automatically detects the x range of dataset from the graph.
So I dont need to fill in range1 and range2 by hand ?

 

[RickXL]

Strange, I thought you might ask that.

Try thia, it reads the values from the chart and sets the x array to the values.

Private Sub Ok_Click()
'press ok'
    Dim ch As ChartObject
    Dim x As Variant
    Dim y As Variant
    
    With ActiveSheet.ChartObjects(1).Chart
        With .Axes(xlCategory)
            x = Array(.MinimumScale, .MaximumScale)
        End With
        y = Array(CDbl(Upperlimit.Text), CDbl(Upperlimit.Text))
        With .SeriesCollection.NewSeries
            .Name = "Upper limit"
            .Values = y
            .XValues = x
            .Format.Line.Weight = 2
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .MarkerStyle = xlMarkerStyleNone
        End With

        y = Array(CDbl(Lowerlimit.Text), CDbl(Lowerlimit.Text))
        With .SeriesCollection.NewSeries
            .Name = "Lower limit"
            .Values = y
            .XValues = x
            .Format.Line.Weight = 2
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .MarkerStyle = xlMarkerStyleNone
        End With
    End With

End Sub

 

[elbartje]

Yes that is what I need, looks perfect.
Now everthing works with a scatter chart, is it possible that I can dedect what type of chart it is.
For example when it is a line chart I can use the if.. goto.. function en use an other code to plot limits on a line chart.

Sorry for all the questions

 

[RickXL]

Well, I expected so but did not know exactly how so I went to Google and searched on:
excel vba chart types

and it found this page: https://msdn.microsoft.com/en-us/library/office/ff820803.aspx?f=255&MSPPError=-2147217396
so that told me that I needed the ChartType Property of the Chart object.

If you look at the existing code we already have a With block at the Chart level so we could start there and ask about the ChartType. For instance:

'...
    With ActiveSheet.ChartObjects(1).Chart
        Debug.Print .ChartType
        With .Axes(xlCategory)
            x = Array(.MinimumScale, .MaximumScale)
        End With

That will print out the Chart Type ID in the Immediate Window (Ctrl + G switches it on.)
It printed out -4169 when I tried it so we will need to know which numbers relate to which types. Again, there is a web page showing all the chart types. Lots of them: https://msdn.microsoft.com/en-us/library/office/ff838409.aspx?f=255&MSPPError=-2147217396
This means that we can use those codes (e.g. xlXYScatter) instead of using the more difficult to remember numbers (e.g. -4169).

So we will need to check. My preferred method would be to use Select Case in VBA. This structure permits different code to be run for different choices. A sample structure would look like this:

Sub test()

    Dim var
    
    var = 2
    
    Select Case var
        Case 1
            ' run some code
        Case 2
            ' run some different code
            MsgBox "Var was set to two"
        Case 3, 4
            ' run code for cases 3 and 4
        Case Else
            ' catch all the unexpected cases
    End Select

End Sub

In the above example, var is set to 2. The Select Case looks at the value of var and picks the appropriate code to run. It will display a MsgBox if you actually run it.

So if we put that structure into your existing code we would get something like this:

' Link to possible Chart Types:
' https://msdn.microsoft.com/en-us/library/office/ff838409.aspx?f=255&MSPPError=-2147217396

Private Sub Ok_Click()
'press ok'
    Dim ch As ChartObject
    Dim x As Variant
    Dim y As Variant
    
    With ActiveSheet.ChartObjects(1).Chart
        Select Case .ChartType
            Case xlXYScatter
                With .Axes(xlCategory)
                    x = Array(.MinimumScale, .MaximumScale)
                End With
                y = Array(CDbl(Upperlimit.Text), CDbl(Upperlimit.Text))
                With .SeriesCollection.NewSeries
                    .Name = "Upper limit"
                    .Values = y
                    .XValues = x
                    .Format.Line.Weight = 2
                    .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
                    .MarkerStyle = xlMarkerStyleNone
                End With
        
                y = Array(CDbl(Lowerlimit.Text), CDbl(Lowerlimit.Text))
                With .SeriesCollection.NewSeries
                    .Name = "Lower limit"
                    .Values = y
                    .XValues = x
                    .Format.Line.Weight = 2
                    .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
                    .MarkerStyle = xlMarkerStyleNone
                End With
            Case xlLine
                ' code to process line charts here
            Case Else
                MsgBox "Unknown chart type. ID = " & .ChartType
        End Select
    End With

End Sub

 

[elbartje]

Using 'case' works perfect !
You probably hear me coming now, how do I do this with an xlLine.
I tried this:

Case xlLine

With .Axes(xlCategory)
x = .MaximumScale
End With

i = 1

Do Until i = x
i = i + 1

        y = CDbl(Upperlimit.Text)
    With .Chart.SeriesCollection.NewSeries
            .Name = "Upper limit"
            .Values = y
            .XValues = i
            
        
Loop

End With

I get an error:He says I miss a do function I dont know why.

Second to make this work I think I need to use an array (for x and y), but I can't find how I can fill my array from 0 until my .maxscale or y with the value .maxscale

 

[elbartje]

I need to use the redim function. I try something ->