Consulting

Results 1 to 1 of 1

Thread: Moving data labels outside each bar of an xlBarStacked Chart

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location

    Moving data labels outside each bar of an xlBarStacked Chart

    Hello.
    On 03/17/2016 @Andy Pope uploaded here a code (post # 2) to move labels with data out of each bar of variance.


    I have an xlBarstacked Chart and I need data labels outside each bar.


    As a first step, from MS Access I export a Query to Excel and create an xlBarStacked Chart (image1).
    This is the code from MS Access to create the bars and the horizontal axis:
    With .Chart
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Values = ws.Range("A2", ws.Range("A2").End(xlDown))         
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Values = ws.Range("C2", ws.Range("C2").End(xlDown))
    .SeriesCollection(2).XValues = ws.Range("E2", ws.Range("E2").End(xlDown))
    .SeriesCollection(2).Format.Fill.ForeColor.RGB = rgbBlue
    End with
    To show the labels on each bar I recorded a macro in Excel (image2). This the Macro with small changes:
    Set ws = ActiveSheet
    Set ch = ActiveChart
    ws.ChartObjects("Chart 1").Activate
    
    
    ch.FullSeriesCollection(2).ApplyDataLabels
    ch.FullSeriesCollection(2).DataLabels.Select
    ch.SeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.InsertChartField _
     msoChartFieldRange, "=qry_123!$D$2:$D$15", 100
    Selection.ShowRange = True
    Selection.ShowValue = False    
        With Selection.Format.TextFrame2.TextRange.Font.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
            .Transparency = 0
            .Solid
        End With
        With Selection.Format.TextFrame2.TextRange.Font
            .Bold = msoTrue
            .Size = 12
        End With
        ActiveChart.ChartArea.Select
    End Sub
    Then, since I could not write a code to move labels, I moved each label one by one to the right of each bar (image3). The values of these labels are in column D (hours) of the WorkSheet.
    I attache the three images in a .zip file

    I need to run those codes from MS Access.
    Any help with (links/tutorials), codes is welcome.
    Cheers
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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