1

Is there a way to configure Excel 2010 to show a crossbar on selected point in a scatter chart? The default behavior is to show a tooltip with the point's value when hover over the mouse cursor. When click the point, it would be nice to show the crossbar (which extend to the border, make it easier to read the scale).

1

If I understand the question correctly, you're looking for something like this when you click a data point in a scatter chart?

enter image description here

Robert Mundigl describes how to do this on his Clearly and Simply blog, adapting a Jon Peltier technique. It works really well.

Read the blog for the full detail. I've only summarised the key steps below.


Robert Mundigl Technique - Summary

First set the following named ranges

enter image description here

Then switch Error Bars on

Now we add horizontal and vertical error bars to the data series of the chart using the standard Excel functionality (ribbon Chart Tools | Tab Chart and Layout | Error Bars. In the Format Error Bars dialogue, we select Error Amount “Custom” and specify the values using the error bar amount named formulas (myEB_X_Pos, etc.)

enter image description here

Add the VBA code

If you've never used VBA before, have a read of this VBA intro guide.

In the Visual Basic Editor (press Alt+F11 to access this), insert a module called modAppEvent.

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         modAppEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Turn application events on and off
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Public my_objSheet As clsAppEvent

Sub AppEventsOn()
    On Error Resume Next
    Set my_objSheet = New clsAppEvent
    Set my_objSheet.xlApp = Application
End Sub

Sub AppEventsOff()
    On Error Resume Next
    Set my_objSheet.xlApp = Nothing
End Sub

add another module called modChartEvent

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         modChartEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Setting and resetting chart events
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit
Option Base 1

Public myCharts() As New clsChartEvent

Sub Set_All_Charts()
Dim obj_cht As ChartObject
Dim int_chartnum As Integer

    On Error Resume Next
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim myCharts(ActiveSheet.ChartObjects.Count)
        int_chartnum = 1
        For Each obj_cht In ActiveSheet.ChartObjects
            Set myCharts(int_chartnum).myEmbeddedChart = obj_cht.Chart
            int_chartnum = int_chartnum + 1
        Next
    End If

End Sub

Sub Reset_All_Charts()
Dim int_chartnum As Integer

    On Error Resume Next
    int_chartnum = UBound(myCharts)
    For int_chartnum = 1 To UBound(myCharts)
        Set myCharts(int_chartnum).myEmbeddedChart = Nothing
    Next

End Sub

Sub ActivateSheet(ByVal Sh As Object)
    Set_All_Charts
End Sub

and a third called modDropLines

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         modDropDownLines
'   Author:         Robert Mundigl
'   Copyright:      © 2012 by Robert Mundigl, www.clearlyandsimply.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Change the value of the defined named range based on the data point the user clicked on
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Sub DropLines(lngDataPoint As Long)
' Update the named range after user clicked on a data point
Dim rngCurrentCell As Range

    ' Store the active cell
    Set rngCurrentCell = ActiveCell
    ' Update the selected data point
    ActiveWorkbook.Names("myDataPoint").Value = lngDataPoint
    ' Go back to the cell (prevent Excel from activating the data series)
    rngCurrentCell.Select

End Sub

Then add a class module called clsAppEvent

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         clsAppEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Application Event Class Module
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Public WithEvents xlApp As Excel.Application

Private Sub xlApp_SheetActivate(ByVal obj_Sh As Object)
    ActivateSheet obj_Sh
End Sub

Private Sub xlApp_SheetDeactivate(ByVal obj_Sh As Object)
    Reset_All_Charts
End Sub

and another called clsChartEvent

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         clsChartEvent
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Edited by:      Robert Mundigl
'   Last edit:      27-October-2012
'   Purpose:        Handle clicks on a data point of an embedded chart
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Public WithEvents myEmbeddedChart As Chart

Private Sub myEmbeddedChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
Dim lng_Element As Long
Dim lng_Argument1 As Long
Dim lng_Argument2 As Long

    If Button = xlPrimaryButton Then
        myEmbeddedChart.GetChartElement X, Y, lng_Element, lng_Argument1, lng_Argument2
        If lng_Element = xlSeries And lng_Argument2 > 0 Then
            DropLines lng_Argument2
        End If
    End If

End Sub

Finally in the ThisWorkbook module add

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Interactive Drop Lines on Excel Charts
'   Module:         Workbook code
'   Author:         Jon Peltier
'   Copyright:      © 2012 by Jon Peltier, Peltier Technical Services Inc, www.peltiertech.com. All rights reserved.
'   Last edit:      27-October-2012
'   Purpose:        Initialize and clean up when opening or closing the workbook
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

Private Sub Workbook_Open()
    AppEventsOn
    Set_All_Charts
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    AppEventsOff
End Sub

Download Zip

Robert has added a download zip containing various examples which will get you started (and it's easier than copying and pasting all this VBA).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.