Excel VBA: Coordinates of a Chart Point

 1. Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) 2. Dim ElementID As Long 3. Dim Arg1 As Long 4. Dim Arg2 As Long 5. Dim v As Variant 6. GetChartElement x, y, ElementID, Arg1, Arg2 7. MsgBox ("X = " & x & " / Y = " & y) 8. End Sub This displays the coordinates of the mouse pointer if the chart is in a tab and not embedded in an Excel sheet. ****************** I managed to get the X and Y coordinates of the mouse on the chart embedded in an Excel sheet. In fact, it "just takes" creating a class module "chart" which already exists in Excel and recognizes "mouse down", "mouse up" and "mouse move". Create a new class module, it is called class1 by default. I left it like that (in fact, I couldn't modify it). You need to place "Public WithEvents mychartclass As Chart" at the top of the class. Here is the class code: 1. Public WithEvents mychartclass As Chart 2. 3. Private Sub mychartclass_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) 4. Range("A9") = x 5. Range("B9") = y 6. End Sub 7. 8. Private Sub mychartclass_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) 9. Range("A8") = x 10. Range("B8") = y 11. End Sub ****************** Then you need to activate this class from the module of the Excel sheet (sheet1 for me). The "initializechart" sub is linked to a command button on the Excel sheet. Here is the code: 1. Dim myClassModule As New Classe1 2. 3. Private Sub initializechart() 4. Set myClassModule.mychartclass = Sheet1.ChartObjects(1).Chart 5. End Sub With this, you will see the coordinates of your mouse pointer appear in cells A8 and B8. "All that remains is to" link these coordinates to a line that will be drawn on the chart.

EXAMPLE: Here is an example of code that allows you to automate this process. To implement this program, you can follow the process below: 1. From the data contained in a worksheet, create a graph 2. If the graph is embedded, activate the graph. 3. Select a series. 4. Run the macro described below. ######################################################################### ' English Visual Basic Code Sub AssignLabels() Dim myRange As Variant, myCell As Object, myPoint As Object Dim chartName$, seriesName$, i% ' Detect the type of window (xlChartInPlace or ' xlChartInWindow). chartType = ActiveWindow.Type ' Get the name of the chart and the selected series. chartName = ActiveChart.Parent.Name seriesName = Selection.Name ' Deselect the embedded chart to select a range of cells If chartType <> 1 Then ActiveWindow.Visible = False ' Select the range containing the labels Set myRange = Application.InputBox( _ Prompt:="Select the range containing the labels:", _ Title:="Labels", Type:=8) ' Reactivate the embedded chart If chartType <> 3 Then ActiveSheet.ChartObjects(chartName).Activate ' Select the series ActiveChart.SeriesCollection(seriesName).Select ' Exit if the box was cancelled If VarType(myRange) = vbBoolean Then Exit Sub ' Initialize a counter i = 1 ' Exit if there are more cells than points If myRange.Count > Selection.Points.Count Then MsgBox Prompt:="Invalid selection. Range too large!", _ Buttons:=vbCritical Exit Sub End If Application.ScreenUpdating = False With Selection For Each myCell In myRange Set myPoint = .Points(i) ' Initialize the label with a cell reference ' this will allow for a link With myPoint .ApplyDataLabels Type:=xlShowValue .DataLabel.Text = "=" & myCell.Address _ (ReferenceStyle:=xlR1C1, External:=True) End With i = i + 1 Next End With End Sub ' French Visual Basic Code Proc AttribuerEtiquettes() Dcl maPlage En Variant; maCellule En Objet; monPoint En Objet Dcl nmGraphique$; nmSérie$; i% ' Detection of the type of window (xlGraphiqueDansEmplacement or ' xlGraphiqueEnFenetre). tpGraphique = FenêtreActive.Type ' Retrieve the name of the embedded chart and from the selected series. nmGraphique = GraphiqueActif.Parent.Nom nmSérie = Sélection.Nom ' Deselect the embedded chart to be able to select ' a range of cells Si tpGraphique <> 1 Alors FenêtreActive.Affiché = Faux ' Selection of the range containing the labels AffecteRéf maPlage = Application.BoîteSaisie( _ Invite:="Selectionnez la plage contenant les étiquettes :", _ Titre:="Etiquettes", Type:=8) ' Reactivation of the embedded chart Si tpGraphique <> 3 Alors FeuilleActive.ObjetsGraphique(nmGraphique) _ .Activer ' Select the series GraphiqueActif.Séries(nmSérie).Sélectionner ' Exit if one cancelled the box Si TypeVariant(maPlage) = vbBooléen Alors Sortir Proc ' Initialization of a counter i = 1 ' Exit if there are more cells than points Si maPlage.NombreDe > Sélection.Points.NombreDe Alors Message Invite:="Sélection non valide. Plage trop grande !"; _ Boutons:=vbCritique Sortir Proc Fin Si Application.MiseAJourEcran = Faux Avec Sélection Pour Chaque maCellule Dans maPlage AffecteRéf monPoint = .Points(i) ' Initialize the label with a cell reference ' which will allow for a link Avec monPoint .AttribuerEtiquettesDonnées Type:=xlAffichageValeur .EtiquetteDonnée.Texte = "=" & maCellule.Adresse _ (StyleRéférence:=xlL1C1; Externe:=Vrai) Fin Avec i = i + 1 Suivant Fin Avec Fin Proc ######################################################################### ######################################################################### Excel Macro Language 4.0 nmSérie=SELECTION() nmGraphique=LIRE.DOCUMENT(1) =ACTIVER() maZone=ENTRER("Selectionnez la plage contenant les étiquettes :"; 8;"Etiquettes") =ACTIVER(nmGraphique) =SELECTIONNER(nmSérie) =SI(ESTLOGIQUE(maZone);RETOUR()) noSérie=CNUM(SUBSTITUE(nmSérie;"S";"")) idPoint=1 =ETIQUETTES.DONNEES(4) =ECRAN(FAUX) =POUR.CELLULE("maCellule";maZone) =AJOUTER.TEXTE(4;noSérie;idPoint) =FORMULE("="&TEXTEREF(maCellule)) idPoint=idPoint+1 =SUIVANT() =RETOUR() ######################################################################### NOTES: 1. In the Excel 4.0 macro, the function ETIQUETTES.DONNEES(4) must be removed to work in the 4.0 version of Microsoft Excel. 2. The macros in Visual Basic for Applications and Excel Macro Language 4.0 follow the same reasoning.