Pointing mouse to show other series data in the chart

[Jordan]

@SirJB7 - I like your ideas. Ever since reading Denial Ferry's articles about Sumproduct and "I heart IF," I've really avoided use lookup functions and ifs. Here, I don't think using Match and an iIf really makes a negative difference on speed. If anything, it makes the work more readable. I just want to give you some context on why I create such awkward formulas.


I like your idea about introducing error. Something else to consider is that when the mouse is over a given cell, the coordinates reported back to the spreadsheet represent the vertex at the upper-left corner. Most of us though probably think of the "center" of the cell as the "true" coordinates of the mouse pointer. This could be modified to report that center - or, alternatively, the entire space of the cell can be considered in the hotspot.

 

[kaushik03]

@Narayan,


Thank you for the explanation again. Actually I did not read the article thoroughly due to which I missed the 'hot spot' technique. I should read that out carefully My apologies!!! I have now understood the technique completely.


@Jordan


Thank you for sharing Chandoo's link regarding "dynamic dashboard with hyperlink' technique; I did not read this article before. It actually helps me to understand the mystery from the beginning as I was completely unaware such advanced technique earlier. The OptionExplicitVBA site is really awesome....Thank you for sharing such wonderful techniques with entire excel world community.


I am very privileged to have such wonderful mentors who can really guide us in the right direction in order to learn excel better each and every day....


Thank you everyone for your guidance and help...


Best Regards,

Kaushik

 

[SirJB7]

@Jordan


Hi!


Today's a complicated journey, but surely tomorrow or in next days I'll be getting back to you.


Regarding the performance issue I agree with you, in this case is just to improve readability and it's always easier to handle a normal formula than an array formula, and not about time processing or resources usage as there's a very short number of cells involved.


I was wondering about a customizable grid size (reducing the actual one) just to avoid the issue of left-top vs. center-center.


Have a nice weekend.


Regards!

 

[Jordan]

@SirJB7


I would love a customizable grid size, the future is definitely headed in that direction. Right now, the grid size I use is based on my own personal preference. To set the square sizes, I use the same code from my blog post, The MOST Squares Method, excerpted below (Get the the pun??):

Private Sub AutoSize()


    Dim cels As Range

Set cels = Me.Cells


    cels.ColumnWidth = 1  'Adjust me

cels.RowHeight = cels(1, 1).width


    Set cels = Nothing


End Sub

For the hotspots in all the spreadsheets I've made so far, I've set the columnwidth = 2. I think with shape properties set not to move/size with columns, we could have the grid size change dynamically. We'd have to keep track of the boundaries somehow (shouldn't be too hard I think, grab right-, left-, top-, and bottom-most shapes and try to find the cell's that immediately "bound" them).


If you want to take this to "the next level," please by all means do! If you really make something rocking, I'll make sure to sing your praises - even in the shower.


@Kaushik03


It is my pleasure to help. Do you understand what's going on in the spreadsheet? If not, please do ask for help if you require assistance. If you do end up using this in your work - or something like it in your work - please let me know! I'd love to take a look. If you also take this to the next level - and there's no doubt in my mind that you can and will once you understand what's going on underneath - please do share! I'll sing your praises too - even from the mountaintops


***

The code above is from this blog article, which I excerpt for reference:

http://optionexplicitvba.blogspot.com/2011/07/completing-square.html.

 

[Kyle McGhee]

kaushik03,


You can try this code that I did for someone else about 7 months ago, modified slightly.

It updates the chart title with the X values of both series, prefixed by the x-axis label for that data point. You have to roll over the actual columns, rather than the actual x-axis. The chart needs to be on a chart sheet.

[pre]

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(1)
xVals = .XValues
Newtitle = xVals(Arg2)
xVals = .Values
Newtitle = Newtitle & ": " & xVals(Arg2)
End With

With Me.SeriesCollection(2)
xVals = .Values
Newtitle = Newtitle & ", " & xVals(Arg2)
End With

ActiveSheet.ChartTitle.Text = Newtitle

End If
End If

End Sub

[/pre]

Let me know if that works.


Kyle