How do I add horizontal line to a chart without adding a new series?

As it says, I have a workbook (actually a heap of workbooks) that has the first sheet with 31 charts on it. In the attached workbook there is 11 sheets of data. The first sheet with the charts has buttons to select which sheet (Piece of equipment) to look at. Some of these charts need horizontal lines on them. Some need upper and lower limits and others just an upper or lower limit. If I can work out how to do one of these I can do the rest. For example the the oil Viscosity @100 needs to be between 12.3 and 16.7.

I am escpecting to have to do this unsing code so I have attached all the existing code below.

Hope you can help like last time.

First bit is the button sellect

Please Login or Register  to view this content.

This bit does all the charting. I have only included 2 pieces of equipment.

Please Login or Register  to view this content.

I arrived at those figures as they were supplied by the testing agency as allowable limits for this equipment.

Adding new series in my view would be too over complicated due the the number and variety of limits per chart/per sheet/per equipment.

If you can show me a simple way to do this that ALSO automatically expands when more data is entered it would be greatly appreciated.

Ok. Each piece of equipment may use a different viscosity oil which means that the allowable limits will be different. Each time you select a piece of equipment the chart changes to show the results that have been entered in the corresponding worksheet.

If you have had a look at the file I attached you will see that the amount of data is different for each sheet. Some equipment is tested 3 monthly and other 2 yearly and others somewhere in between.

I was hoping this would be a pretty simple task but I am begining to think I should posted it in the VBA section.

I just had a bit of a fiddle (keep your minds out of the gutter) and and created something like what I am looking for. My question is now is how do I add a series to a chart without screwing up the existing chart?

Worked a dream......except for a couple of small problems. The chart name/label disappeared and everytime I select that piece of equipment the line markers reappear after turning them off. The later is easy to fix I think.

Ok. Markers turned off and line colour set. I would like to get labels for the limit lines now but it doesn't work when I use

Please Login or Register  to view this content.

It comes up with an error, Unable to get count property of the Datalabels class.

I have found another problem with the code you supplied. Everytime I click on the button for Port MPDE it adds a new series. When I look "Select data" there is a heap of new series.

Thanks for your suggestion but not needed. It appears that if I just click on the one button it will add extra series but if I click on a different button and then come back to the original all is good.

I also solved the Chart title problem.

I shall start a new thread for my next problem. I will probably put it in the VBA/code area as it might need some heavy thinking.