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!

On Mon, 1 Sep 2003 20:56:30 -0700, "Greg Wilson" kindly wrote:
[snip]

My understanding is that named ranges are the property of
the workbook (as opposed to worksheet) object. Therefore,
you can only have ONE named range with the same name in
the same project. It seems implied that you are trying to
create multiple named ranges with the same name. This may
be causing a glitch. Try removing this part of your code
experimentally.

Thanks for pointing that out. I am copying a sheet called "Blank", using the .Copy After command, resulting in duplicate
range names from sheet to sheet. What I guess I can do is incorporate the new sheet name into the range name, or some
other unique identifier, so that all range names will be unique. Is there any limit on the total number number of range
names one can have?

Also, try changing the range name to
something that does not have a number included (i.e. "XXX"
instead of "PerCoTo1A"). It seems to me that in the past

It is actually "PerCoTolA" but IAC thanks for the tip.
[snip]


Simplified code used for test:
Private Sub UserForm_Click()
Dim chNew As ChartObject, i As Integer, S As Series
Set chNew = ActiveSheet.ChartObjects.Add(50, 50, 200, 200)
With chNew.Chart
.ChartType = xlLineMarkers
For i = 1 To 6
.SeriesCollection.NewSeries
Next
If txbBPA.Value < "" Then
Set S = .SeriesCollection.NewSeries
S.Values = Range("PerCoTo1A")
S.Name = Range("BP_A")
End If
End With
End Sub


Regards,
Greg


-----Original Message-----
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/...artsHowTo/Quic
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!

.


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