• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Marco to open a Chart

G

girishg

New Member
Data

Name Maths English Commerce Statistics

Tony 50 90 58 69

John 70 83 60 71

Peter 48 68 74 59

Robin 68 76 85 84

James 80 89 93 85


Requirement

I have the above data which i need to see in graphical format for each student,manually I generate the graph by selecting the data of one student and then clicking the following key strokes "Step 1. Alt+n, Step 2. n Step 3. enter Key" the graph opens up like this http://www.flickr.com/photos/30257292@N02/6195426712/in/photostream , to see the next student's graph I first delete the current student's graph then select the next student's data(basically next row) and then click the same key strokes, And so on for the next student.


in case u do not understand my requirement pl. the check the pictures at the flickr site below are the links


http://www.flickr.com/photos/30257292@N02/6195426712/in/photostream


http://www.flickr.com/photos/30257292@N02/6195438878/in/photostream


What I want

A marco that will navigate and select the next row and generate the corresponding graph, before generation, it should delete the current graph.


What I tried

' Macro1 Macro

'

' Keyboard Shortcut: Ctrl+k

'

Range(Selection, Selection.End(xlToRight)).Select

ActiveSheet.Shapes.AddChart.Select

'ActiveChart.SetSourceData Source:=Range("'monitorsmall (3)'!$E$3:$K$3")

ActiveChart.ChartType = xlLine

End Sub


the above macro works but i need to manually delete the current graph and also select the range, I am not good at vb script hence i don't know to capture the current row and replace the row andcol. However this is the best I could get.


I even tried to create another macro to do the deletion, but it gave an error, here is the code


Sub graph()

'

' graph Macro

'

' Keyboard Shortcut: Ctrl+l

'

ActiveSheet.ChartObjects("Chart 7").Activate

ActiveChart.Parent.Delete

Range("D4").Select

Range(Selection, Selection.End(xlToRight)).Select

Application.Run "Book2!Chart"

End Sub
 
Actually, might I recommend setting up a dynamic chart? You'd only have to do the work once, and then you could easily switch which student you want to look at. Chandoo has written quite a few article on dynamic charting:

http://chandoo.org/wp/tag/dynamic-charts/


In your case, you would just need to define which row you're looking at (using some sort of OFFSET probably).
 
Luke,


Just saw the dynamic chart, unfortunately it does not work for my teacher since it does not give me a graph some thing like this http://www.flickr.com/photos/30257292@N02/6195426712/in/photostream, also could you please tell me how to invoke a macro after refresh, I am bringing data from web into excel every five minutes, after the data is uploaded i needs to be added into another sheet (currently i Invoke the macro manually. Thanks very much for your help LUKE.
 
I'm afraid I can't access that website. Not sure what the problem is, as you can use whatever chart you want. Being dynamic simply means that you change the source data.


For the refresh issue, right click on the sheet tab that the new data is going into, and select "View code".

Setup a worksheet_change event macro. Call the macro you want run in this. Example:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Call MyMacro
End Sub
[/pre]
 
Luke,


Thanks again, the link belongs to a flickr site, in case you cannot open it i can mail you the picture
 
Could you post and example workbook w/ chart to google.docs?

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thanx again, just posted the file below is the link http://hotfile.com/dl/131045147/a5c53de/student_recod.xlsx.html
 
You must log in or register to reply here.
Back
Top