Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro for chart labels (excel xp)

    Sub ShowDataLabels()
    On Error Resume Next

    frmDataLabels.Hide

    DataWSName = frmDataLabels.txtWSdata.Text
    ChartWSName = frmDataLabels.txtWSchart.Text
    ChartWindowName = frmDataLabels.txtChartWindow.Text
    NumSeries = frmDataLabels.txtNumSeries.Text
    NumBars = frmDataLabels.txtNumBars.Text
    range1 = frmDataLabels.txtRange1.Text
    TypeSize = frmDataLabels.txtTypeSize.Text

    Sheets(ChartWSName).Select
    ActiveSheet.ChartObjects(ChartWindowName).Activate
    Call TurnLabelsOff

    ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
    HasLeaderLines:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
    ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False

    For J = 1 To NumSeries 'series
    For i = 1 To NumBars
    ActiveChart.SeriesCollection(J).Points(i).ApplyDat aLabels _
    ShowValue:=True
    ActiveChart.SeriesCollection(J).Points(i).DataLabe l.Text = _
    Sheets(DataWSName).Range(range1).Cells(i, J).Value
    Next i
    ActiveChart.SeriesCollection(J).DataLabels.Select
    Selection.AutoScaleFont = False
    With Selection.Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = TypeSize
    .Background = xlTransparent
    End With
    range1 = Range(range1.Offset(0, 2)).Value
    Next J

    MsgBox ("Done")
    End Sub

    I wrote this macro to update the data labels of charts that i've made. the labels are coming from a range, for example CB11:CB18. When J is incremented to the 2nd series, I want the Range1 to change to 2 columns to the right to CD11:CD18.

    i tried range1=range(range1.offset(0,2).value but that's not right. Could someone help me with the proper code to move 2 columns over when the series increments. thank you.

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for chart labels (excel xp)

    Not so hot on macros/VBA's. The attached worksheet shows a non macro/VBA method of doing what you are looking for, I think?

    Regards
    Peter
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for chart labels (excel xp)

    Try

    range1 = Range(range1).Offset(0, 2).Address

    Comments: since you use range1 = frmDataLabels.txtRange1.Text earlier in the macro, I assume that range1 is a variable of type String that holds the address of a range, not a variable of type Range. Therefore, you can't use range1.Offset(0, 2), for Offset is a method of the Range object. But Range(range1).Offset(0, 2) will work; it returns a Range. You want the address of this range (e.g. "CD11:CD18"), not the value (which would be an array of cell values).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •