Labels on Excel xy scatter-chart data points

[WaltW]

I have a table in Excel with 4 columns: (A) Series, (B) Item, (C) x-coordinate, (D) y-coordinate. There are multiple Items (and data points with the specified x-y coordinates) within each Series. I want to create a x-y scatter-chart in Excel that plots each row in the table using the SAME data marker type for every data point in a Series, but shows the individual Item name (instead of the Series) when you roll the cursor over a data point. I'm certainly no chart expert, and so far I haven't been able to figure out how to do this. Is it possible? If so, how? Any suggestions would be greatly appreciated.

Thanks!
WaltW

 

[SkipVought]

Hi,

It never ceases to amaze me that people try to explain their data, rather than posting a small representative sample of data that someone who wants to help, could use to experiment, aiding in the solution to their problem.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[WaltW]

Thanks for wanting to help, Skip. Here's the sample data I'm using to try to make this work:

Series name Point name X-coord Y-coord
Friday FriName1 37 39
Friday FriName2 89 44
Friday FriName3 64 12
Monday MonName1 55 51
Monday MonName2 23 30
Monday MonName3 62 23
Saturday SatName1 69 98
Saturday SatName2 15 7
Saturday SatName3 69 78
Sunday SunName1 81 52
Sunday SunName2 35 24
Sunday SunName3 84 51
Thursday ThuName1 37 23
Thursday ThuName2 26 27
Thursday ThuName3 80 85
Tuesday TueName1 58 13
Tuesday TueName2 19 78
Tuesday TueName3 71 20
Wednesday WedName1 4 3
Wednesday WedName2 77 69
Wednesday WedName3 5 39

So for example, I want all the "Friday" data markers to look the same visually, but I want to see "FriName1", "FriName2", or "FriName3" when I put the cursor on one of these data markers.

I know this data doesn't look too pretty when posted, so if you'd prefer it posted in a different format, let me know.

Thanks!
WaltW

PS: I appreciate your willingness to help, and no offense intended, but I found your "it never ceases to amaze me" comment to be borderline rude, especially to someone like me who doesn't have occasion to use this forum all that often and was just asking for a little help. A simple request for sample data would have been sufficient. Thanks!

 

[mintjulep]

Can't plot 3-dimensional data on a 2-dimensional chart.

 

[SkipVought]

This is a complex challenge, probably requiring VBA code (macro). The more good information that you can provide, the better your chances are of getting a suitable solution.

Is your chart embedded in a sheet or is it a separate Chart Sheet?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Assuming that it is a Chsrt Sheet, paste this code in the Chart object code window -- right click the chart sheet tab and select View Code...

Dim HasDL As Boolean
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long, sDL As String, oBX As Object, shp As Shape, ser As Series
    Application.ScreenUpdating = False
    ActiveChart.GetChartElement x, y, IDNum, a, b
    Select Case IDNum
        Case xlSeries
            sDL = Application.Index(Sheet1.[Point_name], (a - 1) * 3 + b, 1)
            If Not HasDL Then
                With ActiveChart.SeriesCollection(a)
                    .HasDataLabels = True
                    .Points(b).DataLabel.Text = sDL
                End With
                HasDL = True
            End If
        Case Else
            If HasDL Then
                For Each ser In ActiveChart.SeriesCollection
                    ser.HasDataLabels = False
                Next
            End If
            HasDL = False
    End Select
    Application.ScreenUpdating = True
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

BTW, the sheet name where my source data is, is Sheet1, and I used Named Ranges, using the Column Names you supplied, hence...
[tt]
Sheet1.[Point_name]
[/tt]


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[WaltW]

Thanks so much for the help, Skip. The code you provided sort-of works - some of the points display labels fine, some of them get errors, and some of them display the wrong label. I just did a quick test, so perhaps I didn't plot the data the same way you did, or perhaps I missed something in the code. There's also a lot of "flashing" when I'm moving the cursor around in the chart that's a bit distracting.

When testing this, I was looking at the little info box that appears automatically when you put the cursor on a point. It occurred to me that a better solution would be to have the "point name" appear in that box, and forget the data label altogether. For example, inside the box it now says something like "Series Friday, Point 37, (37,39)". Is it possible to change what the box displays to something like "Series Friday, Point FriName1, (37,39)"? For our purposes, displaying the X-value for "point" inside the box is meaningless, but putting the point name there inside the box would be a great solution. Is this possible?

Thanks again for your help.

-Walt

 

[SkipVought]

Concatenate the Series and Point values into ONE string...
[tt]
Series name Point name X-coord Y-coord
Friday FriName1 37 39
[/tt]
becomes...
[tt]
Series name X-coord Y-coord
Friday FriName1 37 39
[/tt]
The just delete the VB Code and employ the hover feature.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

BTW, I agree that the flashing is annoying. I had reduced it using some coding techniques, but not completely.

Concatenating your Series and Point text will eliminate having to use the code altogether.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[WaltW]

Thanks, Skip.

When I concatenate the series and point names, I'm back to having a "series" for every series-point combination, and no common data markers for what I'm calling a series. So I must be missing something - can you fill me in?

Also, Excel seems to be automatically setting the "point" name for each point somehow - can we modify that? I'm trying to put together a simple macro to do some of this - is there a VBA instruction that lets you change the "point" name that appears when you hover?

Thanks!
-Walt

 

[SkipVought]

Duh! I missed that one! What was I thinking???

Sorry

Let me think about this one a bit more. Right now, the code I posted is the only approch that seems valid for what you want.



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

The credit for this goes to

http://www.j-walk.com,

the John Walkenbach site. That an Excel Guru!!!

Created by David Hager

http://www.j-walk.com/ss/excel/eee/eee006.txt

I made a slight modification to accomodate the x-y coordinates.

Here are the caveats:

1. move your tool-tips to the RIGHT of the data
[tt]
Series name X-coord Y-coord Point name
Friday 37 39 FriName1
[/tt]
2. The chart is embedded in a worksheet
3. There is a TEXTBOX from the DRAWING Toolbar on the sheet.

Known problems:

* The Worksheet window must be maximised.
* Excel's Zoom factor must be set to 100% .
* Windows' Font size must be set to small.
* Overlapping plot areas may have unpredictable results.
* The Cursor & Status Bar do not show default Excel behaviour.
* It does not support some chart types.
* It does not support chart sheets.
* Split windows & frozen panes will cause problems.
* Compared to Excel's built-in chart tips, XTips is slow.

Turn the feature On/Off with XTipsOn/XTipsOff

Paste this code in a MODULE.

'API function to find out the position of the cursor.
Declare Function GetCursorPos Lib "user32" (lppoint As CursorCoords) As Long

Type CursorCoords
    X As Long
    Y As Long
End Type

Dim pos As CursorCoords



'API function to find out height of the Windows caption bar.

Declare Function GetSystemMetrics Lib "user32" _
    (ByVal nIndex As Long) As Long

Public Const SM_CYCAPTION = 4


Dim PreviousX As Long
Dim PreviousY As Long
Dim CurrentX As Long
Dim CurrentY As Long
Dim GetIt As Variant
Dim Yoffset As Single
Dim Xoffset As Single
Dim NextTime As Date
Dim TLTop As Single
Dim TLLeft As Single
Dim CurrentChart As Chart
Dim chrt As Object
Dim bar As Object
Dim Fix2D As Long
Dim I As Integer
Dim Counter As Integer
Dim X As Long
Dim Y As Long
Dim ElementID As Long
Dim SeriesIndex As Long
Dim PointIndex As Long
Dim F As String
Dim StartOfRange As Integer
Dim EndOfRange As Integer
Dim EndOfWorkbook As Integer
Dim SeriesRange As String
Dim SeriesWorkbook As String
Dim SeriesWorksheet As String

Sub XTipsOn()

    NextTime = Now + TimeValue("00:00:01")

    With Application
        .Cursor = xlNorthwestArrow
        .StatusBar = "Ready"
        .ShowChartTipNames = False
        .ShowChartTipValues = False
        .OnTime NextTime, "XTipsOn" 'Starts a recursive loop.
    End With

    Call GetXoffset
    Call GetYoffset

    'Get the current position of the cursor.
    PreviousX = pos.X - Xoffset - (Application.Left * 1.333) - 3
    PreviousY = pos.Y - Yoffset - (Application.Top * 1.333) - 4
    GetIt = GetCursorPos(pos)
    CurrentX = pos.X - Xoffset - (Application.Left * 1.333) - 3
    CurrentY = pos.Y - Yoffset - (Application.Top * 1.333) - 4

    On Error Resume Next 'happens when no textbox is on the worksheet.
    If CurrentX <> PreviousX Or CurrentY <> PreviousY _
        Then  'The mouse is moving.
            ActiveSheet.TextBoxes(1).Visible = msoFalse
        Else: 'The mouse is at rest.
            If ActiveSheet.TextBoxes(1).Visible = msoFalse Then DisplayTip
    End If
    On Error GoTo 0

End Sub

Sub DisplayTip()

    'Gets the Top & Left values of the cell at the top,left of the screen.
    TLTop = Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Top
    TLLeft = Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Left

    'Works out which chart is underneath the cursor.
    For Each chrt In ActiveSheet.ChartObjects
        If (chrt.Left - TLLeft) * 1.333 < CurrentX And _
           (chrt.Left + chrt.Width - TLLeft) * 1.333 > CurrentX And _
           (chrt.Top - TLTop) * 1.333 < CurrentY And _
           (chrt.Top + chrt.Height - TLTop) * 1.333 > CurrentY _
            Then
            Set CurrentChart = ActiveSheet.ChartObjects(chrt.Index).Chart
            Exit For
        End If
        If chrt.Index = ActiveSheet.ChartObjects.Count _
            Then ' There is no chart underneath the cursor.
            Exit Sub
        End If
    Next

    'Makes an adjustment if the chart does not have a 3D effect.
    On Error GoTo ChartIsNot3D
    Fix2D = CurrentChart.Floor.Interior.ColorIndex
    On Error GoTo 0

    'X & Y will be passed to the GetChartElement method.
    X = CurrentX - (CurrentChart.Parent.Left - TLLeft) * 1.333
    Y = CurrentY - (CurrentChart.Parent.Top - TLTop) * 1.333

    CurrentChart.GetChartElement X, Y, ElementID, SeriesIndex, PointIndex

    If ElementID <> 3 Then Exit Sub

    'Finds the range that contains the Series' Source Data.
    F = CurrentChart.SeriesCollection(SeriesIndex).Formula
    If Mid(F, 1, 10) <> "," _
        Then 'The chart has a range specified for X-axis labels.
        F = Left(F, 9) & Mid(F, InStr(10, F, ","))
    End If
    StartOfRange = InStr(1, F, "!")
    EndOfRange = InStr(StartOfRange + 1, F, ",")
    SeriesRange = Mid(F, StartOfRange + 1, _
                  EndOfRange - StartOfRange - 1)

    'Finds the Workbook & Worksheet containing the Series' Source Data.
    EndOfWorkbook = InStr(F, "]")
    If EndOfWorkbook > 0 _
        Then 'The Source Data is in a separate Workbook.
            SeriesWorkbook = Mid(F, 13, EndOfWorkbook - 13)
            SeriesWorksheet = Mid(F, EndOfWorkbook + 1, _
                         (StartOfRange - EndOfWorkbook - 2))
        Else: 'The Source Data is in the Active Workbook.
            SeriesWorkbook = ActiveWorkbook.Name
            SeriesWorksheet = Mid(F, 11, InStr(1, F, "!") - 11)
    End If


    'Re-position, re-write & display the text box.
    With ActiveSheet.TextBoxes(1)
        .Left = (CurrentX / 1.333) + TLLeft + 5
        .Top = (CurrentY / 1.333) + TLTop + 12
        On Error GoTo WorkbookNotOpen
        .Characters(1).Insert String:= _
             Workbooks(SeriesWorkbook). _
             Worksheets(SeriesWorksheet). _
             Range(SeriesRange) _
             .Offset(PointIndex - 1, 2).Resize(1, 1).Value
        On Error GoTo 0
        .AutoSize = True
        .ShapeRange.ZOrder msoBringToFront
        .Visible = msoTrue
    End With
    Exit Sub

WorkbookNotOpen: ActiveSheet.TextBoxes(1).Characters(1).Insert String:= _
                "The workbook containing" & Chr(10) & _
                "the source data for this" & Chr(10) & _
                "chart needs to be open. "
                Resume Next
                Exit Sub

ChartIsNot3D: CurrentX = CurrentX - 1
              CurrentY = CurrentY - 1
              Resume Next
End Sub

Sub GetYoffset()

    'Adds up the heights of all toolbars docked at the top of the screen.
    'If multiple Toolbars share the same RowIndex, only one is counted.
    Yoffset = 0
    ReDim TheArray(0)
    For Each bar In Application.CommandBars
        If bar.Visible = True And bar.Position = msoBarTop Then
            For I = 1 To UBound(TheArray)
                If TheArray(I) = bar.RowIndex Then _
                    Yoffset = Yoffset - bar.Height _
                    : Exit For
            Next I
            Yoffset = Yoffset + bar.Height
            Counter = Counter + 1
            ReDim Preserve TheArray(Counter)
            TheArray(Counter) = bar.RowIndex
        End If
    Next

    'Accounts for the height of the Windows caption bar.
    Yoffset = Yoffset + GetSystemMetrics(SM_CYCAPTION)

    'Accounts for the height of the Formula Bar.
    If Application.DisplayFormulaBar = True Then
        Yoffset = Yoffset + 17
    End If

    'Accounts for the height of Column Headers.
    On Error Resume Next
    If ActiveWindow.DisplayHeadings = True Then
        Yoffset = Yoffset + 17
    End If
    On Error GoTo 0

End Sub

Sub GetXoffset()

    'Adds up the widths of all toolbars docked at the left of the screen.
    'If multiple Toolbars share the same RowIndex, only one is counted.
    Xoffset = 0
    ReDim TheArray(0)
    For Each bar In Application.CommandBars
        If bar.Visible = True And bar.Position = msoBarLeft Then
            For I = 1 To UBound(TheArray)
                If TheArray(I) = bar.RowIndex Then _
                    Xoffset = Xoffset - bar.Width _
                    : Exit For
            Next I
            Xoffset = Xoffset + bar.Width
            Counter = Counter + 1
            ReDim Preserve TheArray(Counter)
            TheArray(Counter) = bar.RowIndex
        End If
    Next

    'Makes an adjustment if any toolbars are docked at the left.
    If Xoffset > 0 Then Xoffset = Xoffset - 1

    'Accounts for the width of Row Headers.
    On Error Resume Next
    If ActiveWindow.DisplayHeadings = True Then
        Xoffset = Xoffset + 26
        'If your charts are near row 1000 or row 10000 ,
        'you may need to adjust the values 963 & 9963 .
        If ActiveWindow.ScrollRow > 963 Then Xoffset = Xoffset + 7
        If ActiveWindow.ScrollRow > 9963 Then Xoffset = Xoffset + 7
    End If
    On Error GoTo 0

End Sub

Sub XTipsOff()

    With Application
        .OnTime NextTime, "XTipsOn", schedule:=False
        .Cursor = xlDefault
        .StatusBar = False
        .ShowChartTipNames = True
        .ShowChartTipValues = True
    End With

    ActiveSheet.TextBoxes(1).Visible = msoTrue

End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}