Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Making graphs with macros on different worksheets

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •