PDA

View Full Version : Cant refer to graph from other sheet?



JKB
10-12-2015, 03:52 AM
Hi everybody!

I want to refer to a graph from my sheet called "Overview", the graph is located in a sheet called "Historic". The code is the following:

Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lInterval As Long: lInterval = shtHis.Range("A2").End(xlDown).Row


shtHis.ChartObjects("Chart 11").Activate
ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

The code is located in a module for the whole workbook.
When i push the button to run the code in "overview" i get a bug: "Run-time error "1004": Method "range" of Object" worksheet" failed.

Im not very familiar with graphs within VBA, am i refering to it the wrong way?
- When i press f8 within the vba window, in compile mode, the code works just fine.

Hope someone can help! Cheers!

Aflatoon
10-12-2015, 04:20 AM
Which line is highlighted by the debugger? The error message is not related to charts.

BTW, it's generally safer to use End(xlUp):


lInterval = shtHis.Cells(shtHis.Rows.Count, "A").End(xlUp).Row

and you don't need to activate the chart:


shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

JKB
10-12-2015, 04:33 AM
Ah thank you for the tip! :)

The line which is bugging, is the following:
ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

The code above the bugging line is the following, it doesn't seem to have any problems running these:
Sub Adjust_graphs()


Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lInterval As Long: lInterval = shtHis.Range("A2").End(xlDown).Row


shtHis.ChartObjects("Chart 11").Activate
ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

Aflatoon
10-12-2015, 05:06 AM
What's the actual value of lInterval when the error is shown?

Paul_Hossler
10-12-2015, 06:02 AM
Try something like this, That wasn't the correct way to specify a Range (as you found out) :)

Note the colon in the .Range




Option Explicit

Sub Macro1()
Dim L As Long

L = 8

Worksheets("Sheet2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Worksheets("Sheet1").Range("A1:B" & L)
End Sub

JKB
10-12-2015, 06:04 AM
I checked under "Locals" it says 1238, which is my last observation, so it should be ok!?

JKB
10-12-2015, 06:24 AM
Hi Paul!
The reason for why i have written it as i have, is because i dont have 1 graph but 4!
Im using Column A,B for graph 1, Column A, C for graph 2, A,D for graph 3 and A,E as input for graph 4 :)

The weird thing is that the code goes into debug mode, when launching from the button, but when i launch it in the compile mode, it works fine!

Aflatoon
10-12-2015, 06:30 AM
The code is fine - just tested running from a button without issue. What kind of button is it and where is it located?

Paul_Hossler
10-12-2015, 06:45 AM
Ok, thanks for the additional information

Then you'd want to do something like this



Option Explicit


Sub Macro1()
Dim L As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

L = 8

'ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval) -- wrong way


Worksheets("Sheet2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("B1:B" & L))

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("C1:C" & L))
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("D1:D" & L))
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("E1:E" & L))

End Sub

JKB
10-12-2015, 06:51 AM
Thanks man! And good news that im not completely useless at this! ;) But the button you used to launch the code, was it in another sheet than where the code took in the data? I think that might be my problem.

The button is located in another sheet called Overview, all the data related to the button is in a specific sheet called "Historic"
The button i just created using insert and form controls, the code for it is located under "Module1"

Aflatoon
10-12-2015, 06:56 AM
I tested it with the button on another sheet and it still works without problem in 2010.

To be clear: there is nothing wrong with the way you are referring to the range. It is perfectly valid syntax
since it resolves to this:

shtHis.Range("A1:A1238,B1:B1238")

JKB
10-12-2015, 07:08 AM
Yeah i think i get you! :) Now i just have to figure out, whether i placed some code in the wrong module or something like that - i suppose :)

Paul_Hossler
10-12-2015, 07:08 AM
Never mind, forgot the comma

Aflatoon
10-12-2015, 07:15 AM
You missed the comma before the B1. :)

Paul_Hossler
10-12-2015, 07:19 AM
U B 2 fast

I was typing as fast as I could to cover my Oops

Aflatoon
10-12-2015, 07:26 AM
:)