Making graphs with macros on different worksheets

torbjoen

New Member
Joined
Apr 9, 2002
Messages
5
Hi!

I'm new to Visual Basic and Macros in Excel, but I've experimented a few hours now.

I'd like to make a graph out of a single column, and display it in the same worksheet as the datasource. This will be repeated for several worksheets.

This is my code (which does not work as I'd like):
/***************************/
Sub Macro6()
'
' Macro6 Macro
' Macro recorded 10.04.02 by student
'

'
Range("B2:B202").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("1000mm_auto_(2)").Range("B2:B202")
ActiveChart.Location Where:=xlLocationAsObject, Name:="1000mm_auto_(2)"
End Sub
/********************************/

This macro always refers to the same worksheet, but I'd like to do the similar action on different sheets, placing the graphs in the correct sheets.

Also, I'd like to make a scatter diagram: think of a the position of a mouse on the screen. Once each millisecond, the position of the mouse is recorded. Then, after recording posistions in two seconds, I would like to display a scatter diagram of where the mouse has been moving, using the width and height of the screen as x- and y- axis on the scatter diagram. How can I do this? I've got one coloumn of each of the x- and y-positions, and would like to display this in a sensible manner.

Thanks, folks :)
This message was edited by torbjoen on 2002-04-10 01:27
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
This should solve your first problem:

Sub Macro6()
Dim PlaceChartHere As String
' Macro6 Macro
' Macro recorded 10.04.02 by student
'

PlaceChartHere = ActiveSheet.Name
'Range("B2:B202").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("1000mm_auto_(2)").Range("B2:B202")
ActiveChart.Location Where:=xlLocationAsObject, Name:=PlaceChartHere
End Sub

Tom
 
Upvote 0
As for the second question....
It's too late for me to try this one.
My first thought would be to draw an image covering the whole screen, make it invisible, group it to the highest layer, and manipulate your chart coordinates via the image1 mouse move event???
Try it out.
Tom
 
Upvote 0
Thanks, Tom :)

My first problem:
Doesn't this sentence also have to be changed in some way?:
/*****************/
ActiveChart.SetSourceData Source:=Sheets("1000mm_auto_(2)").Range("B2:B202")
/*****************/

Guess maybe I can do something like what you've done on top of the function?

I think I made a bad explanation of my second problem: It is not necessary to have the entire graph as large as the screen. Actually I just need the values on the scatter diagram to be the same value as the height and the width of the screen. The graph does not have to cover the screen - I only need to put it into my worksheet.

Thanks, again!
 
Upvote 0
My problem 1 works like a charm using:

/*************************/
Dim PlaceChartHere As String
PlaceChartHere = ActiveSheet.Name
'Range("B2:B202").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets(PlaceChartHere).Range("B2:B202")
ActiveChart.Location Where:=xlLocationAsObject, Name:=PlaceChartHere
/*********************************/

Torbjørn :)
 
Upvote 0
Hi
If I understood you correctly, the chart location was to placed on the active sheet?
The source data is also to be on the active sheet?
If so, change to:
ActiveChart.SetSourceData Source:=Sheets(PlaceChartHere).Range("B2:B202")

As for the mouse chart. The image would cover the whole screen, not the chart.
You could use the mouse move event tp track the x,y coordinates if I understand what you are trying to accomplish...


Tom
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top