Sunday, June 7, 2015

Connect states with arrows US Map using X Y Scatter Chart

Create connections on map using X Y Scatter

Download the working file here

Code to create connections:

Option Compare Text
Option Explicit

' for any query contact

Sub create_chart()

    Dim srs As Series
    Dim ap As Points
    Dim valtocheck As String
    Dim i As Long

    valtocheck = Sheets("Database").Range("state_selected").Value
    If Application.WorksheetFunction.CountIf(Sheets("Source Data").Range("A:A"), valtocheck) = 0 Then
        MsgBox "Please make sure selected state data is added on source data tab", vbInformation, "Note:"
        Exit Sub
    End If
    ' xxxxxxxxxxxxxxxxxxxxx delete existing series
    For Each srs In ActiveChart.SeriesCollection
    Next srs
    ' XXXXXXXXXXXXXXX adding series to charts
    With Sheets("Source Data")
        For i = 4 To .Range("a65356").End(xlUp).Row
            If .Range("a" & i).Value = valtocheck Then
                Set srs = ActiveChart.SeriesCollection.NewSeries
                srs.Name = "='Source Data'!$E$" & i
                srs.XValues = "='Source Data'!$B$" & i & ",'Source Data'!$F$" & i
                srs.Values = "='Source Data'!$C$" & i & ",'Source Data'!$G$" & i
                ' xxxxxxx formatting the series
                srs.Border.Color = vbRed
                With srs.Format.Line
                    .Weight = 1
                    .EndArrowheadStyle = msoArrowheadTriangle
                    .DashStyle = msoLineDashDot
                    .EndArrowheadWidth = msoArrowheadWide
                End With
                srs.Smooth = True
            End If
        Next i
    End With
End Sub

No comments:

Post a Comment