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.

 

[elbartje]

I think I need somthing like this:

Case xlLine

With .Axes(xlCategory)
        n = Value.MinimumScal
        m = Value.MaximumScale
    End With
        
For i = n To m
ReDim matrixx(i)
ReDim Preserve matrixx(UBound(yourArray) + 1)
matrixy(UBound(matrix)) = CDbl(Upperlimit.Text)



   With .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = r           '(r= titelname)
    .SeriesCollection(1).Values = matrixy
    
    End With

I send you my whole file becaus now I also get a case ... select error
(Case else and case xlXYScatter are normaly working perfect)
MY FILE

 

[elbartje]

Ok I fixed some problems!
I can plot a line in my x1Line chart but still not what I want.
My code is doing somthing strange but I dont know why:

Case xlLine


With .Axes(xlValue)
n = .MinimumScale
m = .MaximumScale

    End With
        
For i = n To m
ReDim matrixy(i)
ReDim Preserve matrixy(UBound(matrixy) + 1)
matrixy(UBound(matrixy)) = CDbl(Upperlimit.Text)
Next i


   With .SeriesCollection.NewSeries
    .Name = r
    .Values = matrixy
End With

The file

 

[elbartje]

When I read out the scale he uses the Y scale not the scale from x.
That explains a part of the problem.
I need some function that counts the data (from 1 line) in the graph, this wil represent my x scale.

 

[RickXL]

Hi again,

I think you need to try something like this:

' X1line
    Case xlLine
        With ActiveSheet.ChartObjects(1).Chart
            y = .SeriesCollection(1).XValues
        End With
    
        For i = LBound(y) To UBound(y)
            y(i) = CDbl(Upperlimit.Text)
        Next

        With .SeriesCollection.NewSeries
            .Name = r
            .Values = y
        End With
        
        For i = LBound(y) To UBound(y)
            y(i) = CDbl(Lowerlimit.Text)
        Next

        With .SeriesCollection.NewSeries
            .Name = r
            .Values = y
        End With
        
    'case else
    Case Else

This section:

        With ActiveSheet.ChartObjects(1).Chart
            y = .SeriesCollection(1).XValues
        End With

is used to create an array called y that has the same number of elements as there are categories on the x-axis. The values will be overwritten next.

The values in the array are changed to be the UpperLimit values in this loop:

        For i = LBound(y) To UBound(y)
            y(i) = CDbl(Upperlimit.Text)
        Next

Then a new series is created to add that line to the chart:

        With .SeriesCollection.NewSeries
            .Name = r
            .Values = y
        End With

The process is then repeated using the lower limit.

 

[elbartje]

I want to thank you !
Now I understand a bit more of VBA thanks to you !

 

[RickXL]

You're welcome.

You seemed to be getting on quite well by yourself.

Regards,

 

[Vfriendsp]

Hi RickXL,
I wonder whether you can help me as well. I have a similar problem. I have created a clustered Bar chart where bars measure how many days it took so far to complete a certain task by a certain person. On my X axis I've got peoples' names and y axis horizontal bars showing the amount of days. As soon as it passes 14 days I want to draw a red limit line to show colleagues that they exceeded the limit. At the moment I draw it manually, but I would like to have it appeared automatically. On top of that red limit line I want to have it mentioned '14 days'. I could possibly use VBA, but would need some help with them.
Many thanks for your help.
Regards,
Vytas

 

[RickXL]

Hi,

First, it is usually better to create a new question rather than adding yours to the end of someone else's. That way, the people who can best solve the problem are the most likely to answer. Also, people who answered earlier questions may have left the forum and so it could be that no-one will see your question.

From what you have said it sounds as if you really need a stacked bar chart and might not need a macro at all. For instance, if you chart the first three columns here. Column D has the actual days in it and columns B and C are the values charted:

Excel 2013
	A	B	C	D
1	Name	Number1	Number2	Days
2	jan	14	1	15
3	feb	5	0	5
4	mar	7	0	7
5	apr	14	13	27
6	may	14	10	24
7	jun	9	0	9
8	jul	6	0	6
9	aug	14	10	24
10	sep	14	8	22
11	oct	14	12	26
12	nov	13	0	13
13	dec	14	3	17
Sheet1

Cell Formulas
Range	Formula
B2	=MIN(D2,14)
C2	=MAX(0,D2-14)
D2	=RANDBETWEEN(0,28)

Regards,

 

[Vfriendsp]

Hey many thanks for your help. This is now what I needed I have attached the example. I just need the graph to automatically draw a red line on 14th day
https://drive.google.com/file/d/0B0X3LWTkHeH5QXo3NllVdzFiZzA/view

 

[Vfriendsp]

Thank you. I have managed the red line to appear automatically on the graph without a macro!