Question description:
This user has given permission to use the problem statement for this
blog.
Hi there im just wondering if it would be possible to create a conditional formatting that allows you to click on a point in a scatter plot and have it highlight a row/cell of data?
Solved by A. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
11/08/2017 - 02:21
no dont think so
User
11/08/2017 - 02:22
can this be done?
Excelchat Expert
11/08/2017 - 02:22
Okay, great. Thank you.
Excelchat Expert
11/08/2017 - 02:22
I'm reviewing now.
User
11/08/2017 - 02:22
ok thanks
Excelchat Expert
11/08/2017 - 02:25
It can be done using VBA
Excelchat Expert
11/08/2017 - 02:25
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Dim ElementID As Long Dim Arg1 As Long Dim Arg2 As Long Dim chart_data As Variant Dim chart_label As Variant Dim last_bar As Long Dim chrt As Chart Dim ser As Series On Error Resume Next Me.GetChartElement x, y, ElementID, Arg1, Arg2 Set chrt = ActiveChart Set ser = ActiveChart.SeriesCollection(1) chart_data = ser.Values chart_label = ser.XValues Set txtbox = ActiveSheet.Shapes("hover") If ElementID = xlSeries Then If Err.Number Then Set txtbox = ActiveSheet.Shapes.AddTextbox _ (msoTextOrientationHorizontal, x - 150, y - 150, 100, 100) txtbox.Name = "hover" txtbox.Fill.Solid txtbox.Fill.ForeColor.SchemeColor = 9 txtbox.Line.DashStyle = msoLineSolid chrt.Shapes("hover").TextFrame.Characters.Text = "$ " & Application.WorksheetFunction.Text(chart_data(Arg2), "???.??") & " bn" & Chr(10) & Chr(10) & chart_label(Arg2) With chrt.Shapes("hover").TextFrame.Characters.Font .Name = "Arial" .Size = 12 .ColorIndex = 16 End With With chrt.Shapes("hover").TextFrame.Characters(Start:=1, Length:=11).Font .Name = "Haettenschweiler" .Size = 20 .ColorIndex = 1 End With last_bar = Arg2 End If ser.Points(Arg2).Interior.ColorIndex = 44 txtbox.Left = x - 150 txtbox.Top = y - 150 Else txtbox.Delete ser.Interior.ColorIndex = 16 End If End Sub
User
11/08/2017 - 02:26
Im not too experienced with VBA - I just know the basics, will i need to change anything in that script?
Excelchat Expert
11/08/2017 - 02:26
You would change "hover" to "click"
User
11/08/2017 - 02:26
okay but nothing else?
Excelchat Expert
11/08/2017 - 02:26
I’ve completed the solution, take a look if you would like. We can now continue to the discussion stage and I can answer any questions you may have with the solution I provided.
Excelchat Expert
11/08/2017 - 02:26
correct.
User
11/08/2017 - 02:27
okay so i just add a module
User
11/08/2017 - 02:27
and then copy and paste it in?
Excelchat Expert
11/08/2017 - 02:28
Yup
User
11/08/2017 - 02:28
hmmm the format is pasting in weirdly
Excelchat Expert
11/08/2017 - 02:29
Try that.
Excelchat Expert
11/08/2017 - 02:29
A
Excelchat Expert
11/08/2017 - 02:29
A1
User
11/08/2017 - 02:29
sorry?
User
11/08/2017 - 02:29
what do you mean?
Excelchat Expert
11/08/2017 - 02:29
I pasted the formula in A1 with the proper format.
Excelchat Expert
11/08/2017 - 02:29
Copy and paste that into your macro.
User
11/08/2017 - 02:29
oh darn sorry
User
11/08/2017 - 02:29
i cant see the document
Excelchat Expert
11/08/2017 - 02:29
No worries :)
User
11/08/2017 - 02:29
my company firewall blocks it
User
11/08/2017 - 02:30
are you able to send it as a file?
Excelchat Expert
11/08/2017 - 02:30
https://docs.google.com/spreadsheets/d/1qmQtr6c1yQYYXlWRZ13ApxDcCE0jM3GBmoDgmjvsASc/edit?usp=sharing
Excelchat Expert
11/08/2017 - 02:30
Try that link.
User
11/08/2017 - 02:30
cant even open that sorry
User
11/08/2017 - 02:30
can you send as a word document?
Excelchat Expert
11/08/2017 - 02:31
I can't get it to upload. Sorry.
User
11/08/2017 - 02:32
ok i can probably just go through it and press enter after each line
User
11/08/2017 - 02:32
thanks for your help
Excelchat Expert
11/08/2017 - 02:32
You're very welcome. Is there anything else I can help with?
User
11/08/2017 - 02:32
no should be good, thanks a lot
Excelchat Expert
11/08/2017 - 02:33
You're very welcome :) Have a great day! Please don't forget to rate your experience.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
