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

Pointing mouse to show other series data in the chart

K

kaushik03

Member
Hi all,


I am looking for a kind of a feature to show a floating pop up box with data while pointing mouse on the chart.


Let me explain this in detail:


https://hotfile.com/dl/169082691/6a2d35a/Chart_Sample_data.xlsx.xlsx.html


In sheet1 of attached workbook, I have a chart with two series(2011 number of projects and 2012 number of projects for different clients). Column P and Q contains the dollar amount for this projects.


Now if I point the mouse on X1, I should get the 2011 and 2012 revenue (17 K and 28K, respectively) for X1 in a floating popup box.The same is applicable for other clients.


:please note that, the data for existing clients changes every month and every month new clients data may be added.


As far as I know there is no mouse hovering event in VBA(please feel free to correct me if I am wrong) but is there any way we can achieve something close to what I am looking for?


Your help is much appreciated...


Regards,

Kaushik
 
Hi Kaushik,


Only 3 week before, I also have impression that, no mouse hovering event in VBA,

http://chandoo.org/forums/topic/zoom-in-on-micro-pivot-charts#post-35204


and 5 month before (my first post in this site), I also asked the same question..

http://chandoo.org/forums/topic/chart1_dataseries3_click


Regards,

Deb
 
Hi Debraj,


Thank you for sharing the thread. But this thread talks about zoom in zoom out of a chart and I believe it is something different from what I am looking for.


Or perhaps, I fail to understand the solution provided in the thread.If yes, can you plz help me to understand how can I adapt the technique to my requirement.


Regards,

Kaushik
 
Kaushik


I believe it can be done but it is extremely complex to do so, Please don't ask me as its a level well above my skills.


You can use two sliders to define the starting position and size of a search window to retrieve your data for a chart


Excel 2013 also adds a new Timeline Slicer to the Excel tool-box which does the same thing but in one control.
 
Hi Kaushik ,


I think you can get what you want in these links :


http://www.databison.com/index.php/interactive-chart-in-vba-using-mouse-move-event/


http://www.tushar-mehta.com/excel/software/chart_hover_label/


http://www.clearlyandsimply.com/clearly_and_simply/2010/12/better-chart-tooltips-with-microsoft-excel-2010.html


If you want me to try and do it , please let me know.


Narayan
 
@Hui..


Thank you for the heads up...But I am 101% sure that nothing is impossible for you in excel......and I truly mean it sir...


@Narayan...


Thank you for sharing the links..I will go through them and will get back to you for your expert hand...


Thank you all..


Kaushik
 
Hi Narayan,


The links that you have provided are really very good and indeed, very helpful.


I believe the first link is very relevant to my requirement but only thing is the floating pop up box is coming up at mouse click event(on the bars) not by pointing the mouse at a particular location.


I have tried but not able to adapt the technique to my requirement because of my very limited knowledge of VBA at this point of time.


May I request you to give a try and help me understand how can we achieve this?


Regards,

Kaushik
 
Hi kaushik03


Adapting the link to your file (you need a chart and not a chartobject insered in a worksheet)


Code:

[pre]
Code: 
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim Annee As String, Client As String, Montant As String
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim txtBox As Shape

On Error Resume Next
Set txtBox = ActiveSheet.Shapes("hover")
On Error GoTo 0

If txtBox Is Nothing Then Set txtBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, x, y, 120, 40)
With txtBox
.Name = "hover"
.Fill.Solid
.Fill.ForeColor.SchemeColor = 8
.Line.DashStyle = msoLineDash
With .TextFrame
.VerticalAlignment = xlVAlignCenter
.HorizontalAlignment = xlVAlignCenter
With .Characters.Font
.Name = "Calibri"
.Size = 12
.Bold = True
.ColorIndex = 16
End With
End With
End With

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

If ElementID = xlSeries Then
With Worksheets("Sheet1")
Annee = .Range("O1").Offset(0, Arg1)
Client = .Range("M1").Offset(Arg2, 0)
Montant = Format(.Range("O1").Offset(Arg2, Arg1), "0,""K""")
End With
With txtBox
.Left = x - 150
.Top = y - 100
.TextFrame.Characters.Text = Annee & Chr(10) & Client & ":  " & Montant
End With
Else
txtBox.Delete
End If
Set txtBox = Nothing
End Sub
[/pre]
File: http://speedy.sh/E8Z4A/Copie-de-Chart-Sample-data.xlsm


Regards
 
Good evening mercatog

I have down loaded your file and it does as you set it out to do in your code..........but when you hover over the chart and get the data displayed it is covered by the excel pop up giving the x value and data.
 
@bobhc

Change the Left and Top of the code od txtBox


@kaushik03 If the chart is an object in your worksheet, it needs a Class Module

(in VBA Editor, Insert> Classe Module). It name is Classe1


The code in Classe1:

[pre]
Code: 
Public WithEvents CHRT As Chart

Private Sub CHRT_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim Annee As String, Client As String, Montant As String
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim txtBox As Shape

On Error Resume Next
Set txtBox = ActiveSheet.Shapes("hover")
On Error GoTo 0

If txtBox Is Nothing Then Set txtBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, x, y, 120, 40)
With txtBox
.Name = "hover"
.Fill.Solid
.Fill.ForeColor.SchemeColor = 27
.Line.DashStyle = msoLineSolid
With .TextFrame
.VerticalAlignment = xlVAlignCenter
.HorizontalAlignment = xlVAlignCenter
With .Characters.Font
.Name = "Calibri"
.Size = 9
.Bold = True
.ColorIndex = 16
End With
End With
End With

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

If ElementID = xlSeries Then
With Worksheets("Sheet1")
Annee = .Range("O1").Offset(0, Arg1)
Client = .Range("M1").Offset(Arg2, 0)
Montant = Format(.Range("O1").Offset(Arg2, Arg1), "0,""K""")
End With
With txtBox
.Left = x ' + CHRT.Parent.Left - 150
.Top = y - CHRT.Parent.Top - 50
.TextFrame.Characters.Text = Annee & Chr(10) & Client & ":  " & Montant
End With
Else
txtBox.Delete
End If
Set txtBox = Nothing
End Sub
In vba Object ThisWorkbook

Dim MyChart As New Classe1

Private Sub Workbook_Open()

On Error Resume Next
Set MyChart.CHRT = ThisWorkbook.Worksheets("Sheet1").ChartObjects(1).Chart
End Sub
[/pre]
Save the file, close it and open it again

Select your chartobject and drag the mouse cursor


Here the second file http://speedy.sh/Waebm/ChartObject-Sample-data.xlsm


Regards
 
Good evening mercatog

I am using 2010 and 2013 and in both the hover works after you select the chart, if you select the data columns then the hover still works but the data columns show as a ghost through the hover data box, if you select the data columns high up this does not happen as the hover data is displayed relative to where you select the columns………but please do not take my post as a criticism, I am only trying to help :)
 
@bobhc

Right when you select series or even legend. Thanks for your positive remarks

If you have any another idea to solve the problem, please share your knowledge with us.


Best regards
 
mercatog

I am unable to help, I was asking about such a hover some time back and as my good friend SirJB7 pointed out unlike Access,Excel had no such function, others have also said this is ether not doable or the code would be some thing unique, I am most interested to see where you or others will take this code.
 
The link was posted by NARAYANK991, I just adapte it the the kaushik03's file.

If you read all posts you will understand what I did.

http://www.databison.com/index.php/interactive-chart-in-vba-using-mouse-move-event/
 
Hi mercatog,


Thank you very much for your help. It's really amazing.


The second file that you have uploaded is the one (where the chart is an object in my worksheet) what I was looking for. But the only thing is the pop up box appears after we select the chart which is little different from what you have achieved in the first file you have uploaded.


The way I envisioned the features, exactly meets in the first file but I want the chart to be used as an object in the worksheet (in the same dashboard interface).


I understand there are some limitation of Excel-VBA due to which, perhaps, we are not able to meet the requirement to 100%. However, you have made it very close to what I was looking for.So, thank you very much again for your outstanding help and sharing the knowledge.


It would not have been possible to this extent unless we got the link (http://www.databison.com) from our great Narayan.


@Request to forum:I am still curious and interested in knowing if anyone could tweak the code to meet the requirement exactly what I am looking for (mouse hovering effect on the chart as an object in the worksheet).


Thank you everybody...


Regards,

Kaushik
 
Hi mercatog ,


I came across this today and thought you might be interested ( in case you have not already read it ).


http://optionexplicitvba.blogspot.in/2012/08/details-on-demand-bring-up-details-on.html


Narayan
 
Hi, kaushik03!


In the thread mentioned by Debraj Roy,

http://chandoo.org/forums/topic/pointing-mouse-to-show-other-series-data-in-the-chart#post-38862

the technique was used to show how hovering could be achieved over a shape (nothing new, I know) and how to perform any action on a chart (in this case, resizing).


With the same method, you could try to overlap a transparent shape over your chart, and then call different macros (or same with different results) based on the relative position of the mouse within the shape (provided by mouse move) which it'll be the same position of your chart (which hasn't hovering capabilities).


I've never tried to do that as I've never done before what at previous link, so you can give a try.


The technique used by mercatog in his uploaded file works for a graph as a sheet, not for embedded graphs into normal worksheets... so, as far as I know, you'll have to choose one of the two paths.


Regards!
 
Hi SirJB7,


Thank you for your inputs Pablo Sir.


As suggested, I will try and let you know my progress on this.


Regards,

Kaushik
 
Hey kaushik03,


I wrote the article above referenced by NARAYANK991. I used the technique to develop this file for you:


https://docs.google.com/open?id=0B1OBNnu3ZbL0aXhTMEtzS3QzQTQ


Is this the type of capability you were looking for?
 
Hi Jordan,


This is totally awesome....outstanding job dear...


Two quick questions from my side:


1)If I take the mouse out of the chart area, the last highlighted series and the pop up box still remains visible.Is there any way we can get rid of the highlighted series along with the visible popbox once we do not point/hover the mouse on the bars (or take the mouse out of chart area)?


2)May I request you to explain me the logic you have applied?


Also plz note, my number of series may change (during the course time)like it may sometimes 5 or sometimes 3 or 7 or 10 likewise.I mean the chart data source might change every time (number of rows may expand or shrink every month in col M to O). Considering this scenario, how can we modify the formula that you have used in M13:N17?


Thank you very much for such tremendous help on this so far...you are amazing..


Regards,

Kaushik
 
Hi Jordan,


In order to understand the the technique you have used, I redo the work at sheet2 of this workbook.


I have chosen secondary axis for red and blue bars and adjusted the gap width. For highlight series I have set the primary axis with 'no gap'.


Please see the highlighted region at sheet2 where I have written the formula as you did in sheet1, and then adjusted the code accordingly as follows...


Public Function HighlightChartSeriesInd(seriesIndex As Integer)


Sheet2.Range("valSR").Value = seriesIndex


End Function


I am not able to make the highlighter series moving when hovering the mouse.


Can you plz check the workbook and let me know where I am doing wrong?


https://hotfile.com/dl/169747626/9dcb385/Chart_Sample_data_Jordan_Edit.xlsm.html


And also request you to plz answer my questions in above post.


Looking forward to your reply.


Regards,

Kaushik
 
Hi Kaushik ,


Can you please go through the article referred to earlier ? The link is repeated here :


http://optionexplicitvba.blogspot.in/2012/08/details-on-demand-bring-up-details-on.html


Please note that the technique depends on creating 'hot spots' on the chart ; the method is to use the HYPERLINK formula in the cells behind the chart ; go to Sheet1 of your file , move the chart to some place away from the cells , and check out the formulae in cells D6 through H20.


Move the cursor from column D to E to F ,.... and see the chart highlight moving from X1 to X2 to X3 ,...


Narayan
 
Thanks Kaushik03! As Narayank991 suggests, you will want to thoroughly review that blog post. If all of this is new to you, I suggest you start at the very beginning with Chandoo's tutorial:


http://chandoo.org/wp/2011/07/25/video-on-interactive-dashboard-using-hyperlinks/
 
@Jordan


Hi!


Amazing site OptionExplicitVBA, congrats, man. It flies like an X-51 WaveRider.


I was looking at your uploaded US map workbook file and a couple of ideas crossed my mind:


a) What happens if the geographic center of any shape matches exactly with the defined squared grid X,Y values?

On worksheet "Sheet3" column K cell will actually have a value of 0 (zero). So on worksheet "Sheet2" column BS will have zero too, making:

- the zero count on cell BU8 equal to 48

- the boolean value on column BQ equal False

- the SeriesHighLight value on column BR equal to "0XX"

- and... the valState value on cell BU13 blank... so no label control Label1 displayed

To avoid this I thought on introducing a little error on the distance to the geographic center on worksheet "Sheet3" column K from:

=SQRT((I10-$N$2)^2+(J10-$N$3)^2)*H10

to:

=(SQRT((I10-$N$2)^2+(J10-$N$3)^2)+1E-7)*H10


b) The state index formula on worksheet "Sheet2" cell BU12.

You can change the array formula by non-array formula from:

{=MAX((BS5:BS52=BU9)*(BN5:BN52))}

to:

=IF(BU9=0,48,MATCH(BU9,BS5:BS52,0))


The link to my slightly changed version of your file (added St name and made Label1 transparent) without the previous thoughts implemented:

https://dl.dropbox.com/u/60558749/Dynamic%20Hyperlink%20shapes%20-%20Mapper%20%28for%20electricmice%20from%20Jordan%20at%20chandoo.org%29.xlsm


Regards!
 
@ NARAYANK991

Thank you very much for the interting link. Each day I know more and more new ideas.


Thanks for all the community.


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