• 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.

Interactive Excel charts (Crosshair)

J

justdream

New Member
Dear Excel experts,


Could you please support me to create interactive excel charts that could be similar

to below charts..

http://www.xe.com/currencycharts/?from=CAD&to=AED&view=1D


As you will notice, once we move mouse cursor over any point in charts, we get X & Y axis values appeared above - once will help us in much faster and easier reading of chart values


Do you have any idea how to do it over Excel?

Thanks
 
Hi justdream,


It will require VBA and the chart will need to be on a chart sheet (otherwise more coding will be necessary). This code should get you started. Create a chart sheet for your chart, right click the tab and select view code. Paste this into the right:

[pre]
Code: 
Option Explicit

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Data_Points x, y

End Sub

Private Sub Data_Points(ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim Newtitle As String
Dim xVals As Variant

Me.GetChartElement x, y, ElementID, Arg1, Arg2

If ElementID = xlSeries Then
If Arg2 <> -1 Then
With Me.SeriesCollection(Arg1)
xVals = .Values
Newtitle = .Name & ": " & xVals(Arg2)
End With
ActiveSheet.ChartTitle.Text = "This is New Title: " & Newtitle
End If
End If

End Sub
[/pre]
All of the above learned and modified from Jon Peltier @ http://peltiertech.com


This will not work on an area chart.
 
Hi ,


You might find this interesting :


http://www.databison.com/index.php/chart-label-trick-using-interactive-labels-on-chart/


Narayan
 
Guys I tried Narayank991 suggestion see file:


https://skydrive.live.com/?cid=16D0ED9EE1CB5B75&id=16D0ED9EE1CB5B75!102


The problem I'm having is that I want the X Axis to show the month and year from B3 to B10. It is currently showing the comments information in column A (so I deleted it).


I graph consist of oen series plotted ona line graph and 2 series plotted ona n X Y graph.


Thanks in advance.
 
Thanks Kyle McGhee,


Your solution 95% of what I seek :)

Just 1 favor please,

Could you support me to modify your code to have both Y & X axis values appear together..


For example to be in this format:


This is New Title: Y-axis value @ X-Axis value
 
Sure.

Did you want the value of the data point and the x & y? if so, just change this part

[pre]
Code: 
With Me.SeriesCollection(Arg1)
xVals = .Values
Newtitle = .Name & ": " & xVals(Arg2) & " : X-axis value:" & x & " Y-axis value:" & y
End With
if you just want the x and y values only then it is much simpler, just put this in the module, rather than the other code:

Option Explicit

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

ActiveSheet.ChartTitle.Text = "New Title: X-axis value: " & x & " Y-axis value: " & y

End Sub
[/pre]
 
@ Narayank991:


Thanks for trying to assist me. Try this link. Copy and paste the link below:


https://skydrive.live.com/redir.aspx?cid=16d0ed9ee1cb5b75&resid=16D0ED9EE1CB5B75!111&parid=16D0ED9EE1CB5B75!102&authkey=!ANLWF325ShAm7fc
 
Thanks Kyle McGhee,

This is not my objective..

Please see these snapshots to get my point, Thanks to give me of your time


http://www.4shared.com/rar/PvvPE5hh/snapshots.html
 
@Kamarlon ,


This is as far as I can get !


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21150


Narayan
 
@Narayank: Thanks. What did you change so that the months are now showing? I realize though that the green point isn't in line whith the line graph. I'm wondering if it is related to the change you made
 
Hi ,


That's actually a problem ! I merely introduced a secondary axis for the other series , but this has shifted the series from the first one , and I am not able to line them up. I'll keep trying and let you know if I get anything.


Narayan
 
Hi ,


Try formatting the secondary horizontal axis :


The existing minimum value is 0 , and the existing maximum is 8.0


Change this to 0.5 and 8.5 ; the points match !


Narayan
 
Hi justdream,


Sorry for the delay. Thanks for the sample pictures, I see what you mean now. This should give you want you want (except for the orange label thing). Almost the same...

[pre]
Code: 
Option Explicit

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Data_Points x, y

End Sub

Private Sub Data_Points(ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim Newtitle As String
Dim xVals As Variant, yVals As Variant

Me.GetChartElement x, y, ElementID, Arg1, Arg2

If ElementID = xlSeries Then
If Arg2 <> -1 Then
With Me.SeriesCollection(Arg1)
yVals = .Values
xVals = .XValues

Newtitle = .Name & ": " & yVals(Arg2) & " @" & CDate(xVals(Arg2))

End With
ActiveSheet.ChartTitle.Text = Newtitle
End If
End If

End Sub
[/pre]
 
@ justdream

Could you please upload the final xl sheet with the solution

Please

Me too need the same application

Please help

thanks
 
You must log in or register to reply here.
Back
Top