Making graphs with macros on different worksheets

[torbjoen]

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 ]

[Tom Schreiner]

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

[Tom Schreiner]

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

[torbjoen]

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!

[torbjoen]

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

[Tom Schreiner]

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