+ Reply to Thread
Results 1 to 2 of 2

Datalabels in chart

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-08-2009
    Location
    Norway
    MS-Off Ver
    Excel 2002
    Posts
    142

    Datalabels in chart

    Hey again

    I'm trying to implement some code into some existing code, the code is working properly and creates the chart i want, but i want to edit the Datalabels with this code:

    ActiveChart.SeriesCollection(2).DataLabels.Select
        Selection.AutoScaleFont = True
        With Selection.Font
            .Name = "Arial"
            .FontStyle = "Halvfet"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
        End With
        ActiveChart.ChartArea.Select
        ActiveChart.SeriesCollection(1).DataLabels.Select
        Selection.AutoScaleFont = True
        With Selection.Font
            .Name = "Arial"
            .FontStyle = "Normal"
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
        End With
        ActiveChart.ChartArea.Select
    End Sub
    I've tried every possible way of implementing that code into my existing chart code:

    Sub Makecharts()
    
        Dim rngData As Range
        Dim rngArea As Range
        Dim objChart As ChartObject
        Dim xx As Series
        Dim DataWS As Worksheet
        
        Set DataWS = Worksheets("Maskin")
        
        Do While ActiveSheet.ChartObjects.Count > 0
            ActiveSheet.ChartObjects(1).Delete
        Loop
        
         
        Set rngData = DataWS.Range("A2", DataWS.Cells(Rows.Count, 1).End(xlUp)) '.SpecialCells(xlCellTypeConstants)
        
        For Each rngArea In rngData.Areas
            Set objChart = ActiveSheet.ChartObjects.Add(rngArea.Left + rngArea.Width, rngArea.Top, 500, 250)
            With objChart.Chart
                .PlotBy = xlRows
                Do While .SeriesCollection.Count > 0
                    .SeriesCollection(1).Delete
                Loop
                With .SeriesCollection.NewSeries
                    .Values = rngArea.Offset(0, 2)
                    .XValues = rngArea
                    .Name = "Skift mot Liter"
                    .ChartType = xlColumnStacked
                End With
                With .SeriesCollection.NewSeries
                    .Values = rngArea.Offset(0, 3)
                    .XValues = rngArea.Offset(0, 2)
                    .Name = "Dag"
                    '.ChartType = xlXYScatter
                End With
                .HasTitle = True
                .ChartTitle.Characters.Text = rngArea.Cells(1, 1).Offset(0, 1).Value
                .Axes(xlCategory, xlPrimary).HasTitle = False
                .Axes(xlCategory, xlPrimary).TickLabels.Orientation = xlDownward
                .Axes(xlCategory, xlPrimary).TickLabelSpacing = 1
                .Axes(xlCategory, xlPrimary).FontStyle = "Halvfet"
                .Axes(xlCategory, xlPrimary).HasMajorGridlines = False
                .ApplyDataLabels AutoText:=True, LegendKey:=False, _
            HasLeaderLines:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
            ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
                .Axes(xlValue, xlPrimary).HasTitle = True
                .Axes(xlValue, xlPrimary).HasMajorGridlines = False
                .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Liter"
                If .HasLegend Then .Legend.Delete
            End With
            
            objChart.Top = rngArea.Top
        Next
        Application.Goto ActiveSheet.Range("A1"), True
        
    End Sub
    The part in bold is my attempt at inserting the code, this does not work however.. Anyone got an answer for this? I want it into the existing code because the code i am using allows me dynamic ranges altso i can loop it down the sheet to create more graphs depending on the amount of data.


    PS: I do not want to have to .select the chart in question it should be part of the existing code as that runs for all charts created. I just don't know where and how to insert it properly.
    Thanks
    Last edited by prefix; 03-04-2010 at 08:02 AM.

  2. #2
    Forum Contributor
    Join Date
    09-08-2009
    Location
    Norway
    MS-Off Ver
    Excel 2002
    Posts
    142

    Re: Datalabels in chart

    Solved it on my own

    Problem was that i didnt have .HasDataLabels = True altso some sites returned that it was supposed to be .HasDataLabel, adding an extra s on the end made it work for me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1