Run Time Error 91

[Qaspec]

I get the following error message when I try to run a sub in excel 2003:

"Runtime Error 91 Object Variable or With Block Variable Not Set"

I receive the error when the line gets to:

With ActiveChart.SeriesCollection(1)

I'm assuming the means that I need to check one of the libraries in the
references. If I'm incorrect or if anyone knows the correct library for the
SeriesCollection method, please let me know.

 

[Neptune Dinosaur]

You get that error when you aere working with an Object variable and then try
to assign to it as though it were a "normal" variable. To assign to an
Object variable you neeed to use the Set keyword, e.g. Set MyObject =
ActiveChart

 

[Jim Cone]

For the code to execute, there must be a chart that is active.
That means your code must have selected the chart or the chart
must have been selected manually (click the chart) before the code was run.
Also, there must be a Series on the chart.

You should have specified what XL version you are running.

If you are running xl2007, I wouldn't hazard a guess.
--
Jim Cone
Portland, Oregon USA



"Qaspec"
<[email protected]>
wrote in message
I get the following error message when I try to run a sub in excel 2003:

"Runtime Error 91 Object Variable or With Block Variable Not Set"

I receive the error when the line gets to:
With ActiveChart.SeriesCollection(1)

I'm assuming the means that I need to check one of the libraries in the
references. If I'm incorrect or if anyone knows the correct library for the
SeriesCollection method, please let me know.

 

[Qaspec]

I click the chart in order to run the code and I'm using xl2003.

Here is the macro

Sub ColorByValue()
Dim rPatterns As Range
Dim iPattern As Long
Dim vPatterns As Variant
Dim iPoint As Long
Dim vValues As Variant
Dim rValue As Range

Set rPatterns = ActiveSheet.Range("T1:T3")
vPatterns = rPatterns.Value
With ActiveChart.SeriesCollection(1)
vValues = .Values
For iPoint = 1 To UBound(vValues)
For iPattern = 1 To UBound(vPatterns)
If vValues(iPoint) <= vPatterns(iPattern, 1) Then
.Points(iPoint).Interior.ColorIndex = _
rPatterns.Cells(iPattern, 1).Interior.ColorIndex
Exit For
End If
Next
Next
End With
End Sub

 

[Jim Cone]

I ran your code on a column chart in XL2002 with no problems.
--
Jim Cone
Portland, Oregon USA



"Qaspec"
<[email protected]>
wrote in message
I click the chart in order to run the code and I'm using xl2003.
Here is the macro

Sub ColorByValue()
Dim rPatterns As Range
Dim iPattern As Long
Dim vPatterns As Variant
Dim iPoint As Long
Dim vValues As Variant
Dim rValue As Range

Set rPatterns = ActiveSheet.Range("T1:T3")
vPatterns = rPatterns.Value
With ActiveChart.SeriesCollection(1)
vValues = .Values
For iPoint = 1 To UBound(vValues)
For iPattern = 1 To UBound(vPatterns)
If vValues(iPoint) <= vPatterns(iPattern, 1) Then
.Points(iPoint).Interior.ColorIndex = _
rPatterns.Cells(iPattern, 1).Interior.ColorIndex
Exit For
End If
Next
Next
End With
End Sub

 

[Qaspec]

Then is it xl2003? Maybe my method of placement? I placed the code in vba
project in a module and went to the chart and assigned the macro from there.
At that point I click on the chart to run the macro is where I get the error
message. My Macro security is set to low, and trust all sources, and allow
access to vba project.

I did notice that when I searched for the ActiveChart object in the vba
object browser it gave me a message saying it was hidden.

 

[Jim Cone]

Change the unwilling line to...
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

The above assumes there is only one chart on the sheet or that
it is the first chart that was added to the sheet.
--
Jim Cone
Portland, Oregon USA




"Qaspec" <[email protected]>
wrote in message
Then is it xl2003? Maybe my method of placement? I placed the code in vba
project in a module and went to the chart and assigned the macro from there.
At that point I click on the chart to run the macro is where I get the error
message. My Macro security is set to low, and trust all sources, and allow
access to vba project.
I did notice that when I searched for the ActiveChart object in the vba
object browser it gave me a message saying it was hidden.

 

[Qaspec]

The code runs all the way through without an error but it is not changing the
colors of the bars in the chart.

Change the unwilling line to...
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

The above assumes there is only one chart on the sheet or that
it is the first chart that was added to the sheet.
--
Jim Cone
Portland, Oregon USA




"Qaspec" <[email protected]>
wrote in message
Then is it xl2003? Maybe my method of placement? I placed the code in vba
project in a module and went to the chart and assigned the macro from there.
At that point I click on the chart to run the macro is where I get the error
message. My Macro security is set to low, and trust all sources, and allow
access to vba project.
I did notice that when I searched for the ActiveChart object in the vba
object browser it gave me a message saying it was hidden.

 

[Jim Cone]

This line determines whether the color changes...
"If vValues(iPoint) <= vPatterns(iPattern, 1) Then"
So...
Try changing the values the chart uses or the values in range("T1:T3")
--
Jim Cone
Portland, Oregon USA


"Qaspec"
<[email protected]>
wrote in message
The code runs all the way through without an error but it is not changing the
colors of the bars in the chart.

Change the unwilling line to...
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

The above assumes there is only one chart on the sheet or that
it is the first chart that was added to the sheet.
--
Jim Cone
Portland, Oregon USA




"Qaspec" <[email protected]>
wrote in message
Then is it xl2003? Maybe my method of placement? I placed the code in vba
project in a module and went to the chart and assigned the macro from there.
At that point I click on the chart to run the macro is where I get the error
message. My Macro security is set to low, and trust all sources, and allow
access to vba project.
I did notice that when I searched for the ActiveChart object in the vba
object browser it gave me a message saying it was hidden.

 

[Qaspec]

I've tried to change the values in the T range and the data in the chart's
source data to no avail. I wonder if I could send you the worksheet and
possibly you can see something I missed? Thank you for all the help.

This line determines whether the color changes...
"If vValues(iPoint) <= vPatterns(iPattern, 1) Then"
So...
Try changing the values the chart uses or the values in range("T1:T3")
--
Jim Cone
Portland, Oregon USA


"Qaspec"
<[email protected]>
wrote in message
The code runs all the way through without an error but it is not changing the
colors of the bars in the chart.

 

[Qaspec]

I feel stupid....the code does work. It is changing the colors on the first
chart on the page and not the chart I'm clicking. Can this code be set to
automatically produce thise behavior for every chart on the sheet?

This line determines whether the color changes...
"If vValues(iPoint) <= vPatterns(iPattern, 1) Then"
So...
Try changing the values the chart uses or the values in range("T1:T3")
--
Jim Cone
Portland, Oregon USA


"Qaspec"
<[email protected]>
wrote in message
The code runs all the way through without an error but it is not changing the
colors of the bars in the chart.

 

[Jim Cone]

That is why I said...
"The above assumes there is only one chart on the sheet or that
it is the first chart that was added to the sheet."

The following line references the chart that calls the code...
With ActiveSheet.ChartObjects(Application.Caller).Chart.SeriesCollection(1)

Note: A Chart on a worksheet is contained within a ChartObject.
Application.Caller (in this case) returns the name of the ChartObject.
So... ChartObjects(Application.Caller) = ChartObjects("Chart x").
And... ChartObjects("Chart x").Chart ... is the chart you clicked.
--
Jim Cone
Portland, Oregon USA



"Qaspec"
<[email protected]>
wrote in message
I feel stupid....the code does work. It is changing the colors on the first
chart on the page and not the chart I'm clicking. Can this code be set to
automatically produce thise behavior for every chart on the sheet?

This line determines whether the color changes...
"If vValues(iPoint) <= vPatterns(iPattern, 1) Then"
So...
Try changing the values the chart uses or the values in range("T1:T3")
--
Jim Cone
Portland, Oregon USA


"Qaspec"
<[email protected]>
wrote in message
The code runs all the way through without an error but it is not changing the
colors of the bars in the chart.

 

[Qaspec]

You're right. Because the other charts were a different type i completely
spaced out and forgot about them!

The application caller works great. Everything is just as I need it so far.

Just one more question though. Would it be possible to change the codes run
event for each chart to run when the source data values change the chart
changes triggering the change in bar code color?

 

[Jim Cone]

"I think what we have here is mission creep"...
http://en.wikipedia.org/wiki/Mission_creep

You may want to review your needs and make a separate post with
your request for help
-or-
Email the workbook to me with your specific requirements and I can
prepare a quotation outlining the cost to do the coding.
(remove the xxx from my email address)
--
Jim Cone
Portland, Oregon USA
james.coneXXX at comcast.netXXX



"Qaspec" <[email protected]>
wrote in message
You're right. Because the other charts were a different type i completely
spaced out and forgot about them!
The application caller works great. Everything is just as I need it so far.
Just one more question though. Would it be possible to change the codes run
event for each chart to run when the source data values change the chart
changes triggering the change in bar code color?

 

[Qaspec]

Actually you did help me greatly and I apologize. I'm not purposely trying to
mission creep. As each question was answered it led me to another question as
I thought of improvements if they were possible. Plus to be honest it's how I
learn through asking these questions. If you want I'll show you the file and
you can see that this whole chart issue is a very small portion of the file
I'm working on which is actually a scorecard used to measure employee
performance. I definitely thank you for all of your help Jim.

 

[Qaspec]

Plus the last 2 questions I asked after the code worked fine are not required
for completion of my file. I was actually just curious. The last question I
was going to ask on the chart would have definitely been my last one posted
since I was simply wondering if it were possible to set the code so that you
dont' have to click each chart anytime the values in the chart changed.

Normally I try to outline my request, or question specifically and entirely
in one post, in this case I would have been wise to just thank you after you
helpd with that line of code causing the run time error. Your were very
gracious to help Jim. I apologize if by any means you felt I were trying to
take advantage or attempt to have someone else complete my project for me.