The following is derived from my code library (with minor changes) and is
credited to Stephen Bullen. I don't have a record of the date of the post and
can no longer access it on on the web.

You will have to correct for wordwrap. After setting it up, close and reopen
the workbook.

When you click the chart, the code will return in a message box the
translated x and y values at the position of the mouse pointer. These values
can be used directly to adjust the chart min and max scaling - i.e. they have
already been translated into chart values. I have left it with you to take it
from there. Hope it goes well.

Regards,
Greg Wilson

Paste the following to the ThisWorkbook module:
Private Sub Workbook_Open()
Set myChart.EmbedChart = Sheets("Sheet1").ChartObjects(1).Chart
End Sub

Paste the following to a standard module:
Public myChart As New Class1

Create a Class Module named Class1 and paste the following:
'API's for getting the factors to convert points to pixels
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "Gdi32" (ByVal hDC As Long, ByVal
nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal
hDC As Long) As Long
Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90
Public WithEvents EmbedChart As Chart
Dim dZoom As Double, dMin As Double, dMax As Double

Private Sub EmbedChart_MouseDown(ByVal Button As Long, ByVal Shift As Long,
ByVal X As Long, ByVal Y As Long)
Dim xVal As Single, yVal As Single
On Error Resume Next
dZoom = ActiveWindow.Zoom / 100
'Ungroup to enable changing component rectangle textframe text.
With EmbedChart
'***Use dMin & dMax first to capture XVal max. range.
dMin = .Axes(xlCategory).MinimumScale
dMax = .Axes(xlCategory).MaximumScale
xVal = dMin + (dMax - dMin) * ((X - IIf(dZoom 1, 6 * (dZoom - 1),
0)) * PointsPerPixelX / dZoom - (.PlotArea.InsideLeft + .ChartArea.Left)) /
..PlotArea.InsideWidth
'***Now use dMin & dMax to capture YVal maximum range.
dMin = .Axes(xlValue).MinimumScale
dMax = .Axes(xlValue).MaximumScale
yVal = dMin + (dMax - dMin) * (1 - ((Y - IIf(dZoom 1, 6 * (dZoom -
1), 0)) * PointsPerPixelY / dZoom - (.PlotArea.InsideTop + .ChartArea.Top)) /
..PlotArea.InsideHeight)

End With
MsgBox xVal & vbCr & yVal
End Sub

'The width of a pixel in Excel's userform coordinates
Public Property Get PointsPerPixelX() As Double
Dim hDC As Long
hDC = GetDC(0)
'A point is defined as 1/72 of an inch and LOGPIXELSX returns
'the number of pixels per logical inch, so divide them to give
'the width of a pixel in Excel's userform coordinates
PointsPerPixelX = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
ReleaseDC 0, hDC

End Property

'The width of a pixel in Excel's userform coordinates
Public Property Get PointsPerPixelY() As Double
Dim hDC As Long
hDC = GetDC(0)
'A point is defined as 1/72 of an inch and LOGPIXELSX returns
'the number of pixels per logical inch, so divide them to give
'the width of a pixel in Excel's userform coordinates
PointsPerPixelY = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
ReleaseDC 0, hDC
End Property