Posted by: shijesh | January 31, 2010

Adding Labels to XY Scattered Chart in MS Excel

Few days back on of my colleague wanted to know how to add label to XY-Scattered Chart in MS Excel.

Well here is the VB Code for the same.


Sub label_xy_chart()
    Dim xchart As Chart
    Dim label_row As Integer
    Dim no_of_points As Integer

    'selets the chart for adding label
    Set xchart = ActiveChart

    On Error Resume Next

    xchart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue

    '# of point in the chart
    no_of_points = xchart.SeriesCollection(1).Points.Count

    'address of first cell containing the label
    Set xrange = Application.InputBox("Select 1st cell containing label", Type:=8)

    'looping through each lable and adding to the chart
    For label_row = xrange.Row To no_of_points
    xchart.SeriesCollection(1).Points(label_row).DataLabel.Text = _
    ActiveSheet.Cells(label_row, xrange.Column).Value
    Next label_row
End Sub

To use this first create your XY Scattered Chart. Then Run the above vb code.

A input window will come… Select the first cell of the column which contain the label .. click OK.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: