Monday, November 14, 2011

VBA Code for Chart Event

This event works for Chartsheet not for embedded chart.
VBA Code is mentioned below(for top 10 billionaire chart)




Dim chrt As Chart
Dim ser As Series
Dim chrtdata, chrtlbl, txtBox As Object, elementId As Long, arg1 As Long, arg2 As Long
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
On Error Resume Next
Application.ScreenUpdating = False
Set chrt = ActiveChart
Set ser = ActiveChart.SeriesCollection(1)
chrtdata = ser.Values
chrtlbl = ser.XValues
txtBox.Delete
chrt.GetChartElement x, y, elementId, arg1, arg2
    If elementId = xlSeries Then
 
        Set txtBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, x - 135, y - 125, 100, 100)
        txtBox.Height = 56
        txtBox.Width = 80
        txtBox.Name = "Hover"
        txtBox.Fill.ForeColor.SchemeColor = 27
        txtBox.Line.DashStyle = msoLineSolid
        txtBox.TextFrame.Characters.Text = "Sales Amount: " & "$" & chrtdata(arg2) & Chr(10) & "Sales Person: " & chrtlbl(arg2)
        txtBox.TextFrame.Characters.Font.Size = 10
        txtBox.TextFrame.Characters.Font.ColorIndex = 1
        txtBox.TextFrame.Characters.Font.Bold = True
        ser.Points(arg2).Interior.ColorIndex = 38
     Else
        ser.Interior.ColorIndex = 18
    End If
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyTTBXcjA2TjZmQzg/edit?usp=sharing

No comments:

Post a Comment