Your code worked for me. Is txtPartNum always a valid sheet name? You
might try enclosing it in single quotes:

..SeriesCollection(7).Values = _
"='" & Me.txtPartNum.Value & "'!PerCoTolA"

I have some hints for working with charts in VBA on my web site:
http://www.geocities.com/jonpeltier/...kChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

arobustus wrote:
I really hope that someone can help me with this, because it is driving me crazy.
The code below is part of a routine which adds a worksheet based on a form, then creates a chart for that newly created
worksheet. There are 4 if/then statements similar to the one shown, so that uses can set up to four lines on the chart,
depending on whether or not they have entered anything in a given textbox. (BTW there are 12 text boxes in groups of 3
per each of 4 frames.)
The ".SeriesCollection.NewSeries" statements are from the original recorded macro.

When it gets to the line:
..SeriesCollection(7).Values = "=" & Me.txtPartNum.Value & "!PerCoTolA"
it sometimes (!!!) generates the error:

Run-time error '1004':
Unable to set the Values property of the Series class

"Me.txtBPA" is one of the controls on the form.
"PerCoTolA" is a named range on the newly created sheet. As one creates new sheets, they all have the smae named ranges
and cells.

The really odd thing is that "sometimes", as if the code is basically OK but somewhat unstable, or something like that.
If it would just occur all the time, I could understand. So far I have been unable to figure out what, if any, squence
of actions on my part causes the error to be generated "this" time rather than "that".

I thought it might be better if I created a variable of type "SeriesCollection" & set it to each one in turn, then
manipulated that, but I was unable to come up with a way to do it which did not generate another error.

Charts.Add
Set chNew = ActiveChart
With chNew
.ChartType = xlLineMarkers

'Add Chart Series for 25%, 50%, 75% lines
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries

'Add Chart Series for measured values as needed

If Me.txtBPA.Value < "" Then
.SeriesCollection.NewSeries
'***Error Generated on next line:**
.SeriesCollection(7).Values = "=" & Me.txtPartNum.Value & "!PerCoTolA"
.SeriesCollection(7).Name = "=" & Me.txtPartNum.Value & "!BP_A"
End If

I would be very grateful for any help with this.
-plh

I keep hitting "Esc" -- but I'm still here!