Chart Linkage Sample

Option Explicit

Private Sub UserForm_Initialize()
    SpdShtCtrl.Names.Add Name:="XValues", _
        RefersTo:="=OFFSET('Sheet1'!$A$2,0,0,COUNT('Sheet1'!$A$2:$A$100),1)"

    SpdShtCtrl.Names.Add Name:="YValues", _
        RefersTo:="=OFFSET('Sheet1'!$B$2,0,0,COUNT('Sheet1'!$B$2:$B$100),1)"

    With ChartControl
        .DataSource = SpdShtCtrl
        .Clear
        .Charts.Add
        .Charts(0).Type = chChartTypeScatterSmoothLine
        With .Charts(0).SeriesCollection.Add
            .Caption = "Data"
            .SetData chDimSeriesNames, 0
            .SetData chDimCategories, 0
            .SetData chDimXValues, 0, SpdShtCtrl.Range("XValues").Address
            .SetData chDimYValues, 0, SpdShtCtrl.Range("YValues").Address
        End With
    End With

End Sub