Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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 -- Time is just the thing that keeps everything from happening all at once "Qaspec" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" wrote: 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" 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA "Qaspec" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA "Qaspec" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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. "Jim Cone" wrote: 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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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? "Jim Cone" wrote: 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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" 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? "Jim Cone" wrote: 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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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" wrote: 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" 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? "Jim Cone" wrote: 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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
"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" 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? "Jim Cone" wrote: 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" 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? "Jim Cone" wrote: 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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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. "Jim Cone" wrote: "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" 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? "Jim Cone" wrote: 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" 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? "Jim Cone" wrote: 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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 91
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. "Qaspec" wrote: 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. "Jim Cone" wrote: "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" 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? "Jim Cone" wrote: 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" 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? "Jim Cone" wrote: 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" 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" wrote: 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" 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" wrote: I ran your code on a column chart in XL2002 with no problems. -- Jim Cone Portland, Oregon USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |