Sub change_source_data()
Dim pw As String
Sheets("Map").Unprotect
Charts("Map").Select
' xxxxxxxxxxxxxxxxxxxxx delete existing series
Dim srs As Series
Dim ap As Points
For Each s In ActiveChart.SeriesCollection
s.Delete
Next s
' XXXXXXXXXXXXXXX adding series to charts
For i = 2 To Sheets("Source Data").Range("a65356").End(xlUp).Row
Set srs = ActiveChart.SeriesCollection.NewSeries
srs.Name = Sheets("Source Data").Range("a" & i).Value
srs.XValues = Sheets("Source Data").Range("c" & i).Value
srs.Values = Sheets("Source Data").Range("d" & i).Value
Next i
' xxxxxxxxxxxxxxxx change shape of labels
For Each s In ActiveChart.SeriesCollection
s.MarkerStyle = 6
s.MarkerSize = 15
s.MarkerBackgroundColor = RGB(255, 0, 0)
s.MarkerForegroundColor = RGB(255, 0, 0)
Next
Sheets("Map").Protect
End Sub
Below code is used to track the mouse movement on charts
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim j As Long
ActiveChart.GetChartElement x, y, ElementID, Arg1, Arg2
If ElementID = xlSeries Then
ActiveSheet.Shapes("Textbox 1").Visible = True
j = Application.WorksheetFunction.Match(ActiveChart.SeriesCollection(Arg1).Name, Sheets("Source Data").Columns("a:a"), 0)
ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text = "State: " & ActiveChart.SeriesCollection(Arg1).Name _
& vbNewLine _
& "Sales: " & VBA.Format(Sheets("Source Data").Range("b" & j).Value, "$#,##0")
Else
ActiveSheet.Shapes("Textbox 1").Visible = False
ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text = ""
End If
End Sub