Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: automate charts

  1. #1
    4 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    402
    Thanks
    1
    Thanked 1 Time in 1 Post

    automate charts

    greetings,

    I have the attached data sample that I want help to automate charts creation.
    Note the first two from the left will be for Serv "Oper" and the two on the right will be for Serv "Eng", and that the charts title is a combination of Dep and Serv names and that the Code varies.





    Attached Files Attached Files
    Last edited by dubdub; 2018-07-08 at 12:07.
    TIA
    dubdub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,011
    Thanks
    157
    Thanked 786 Times in 716 Posts
    Hi dubdub,

    See the attached file. If you change any of the values, the affected chart will update. You can add or delete rows anywhere within the matrix to add additional data rows and the code will adjust the ranges for each chart and update.

    Hope this is what you are trying to achieve.
    Maud
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    402
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Maudibe,
    thank you so much for the help, but i could not see the code, and when i add more rows of data, attached,no change/update happened on the chart side, no new chart is created for D3.
    Attached Files Attached Files
    TIA
    dubdub

  4. #4
    4 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    402
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Maudibe,
    i presume below is the code, but when i ran it, i got an error message that says compiler error method or data member not found with reference to the text FullSeriesCollection in the third row of the code. any help

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$D$2:$D$8"
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$2:$C$8"
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$D$2:$D$7"
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$2:$C$7"
    Range("H17").Select
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "D1-Opert"
    Selection.Format.TextFrame2.TextRange.Characters.T ext = "D1-Opert"
    With Selection.Format.TextFrame2.TextRange.Characters(1 , 8).ParagraphFormat
    .TextDirection = msoTextDirectionLeftToRight
    .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1 , 8).Font
    .BaselineOffset = 0
    .Bold = msoTrue
    .NameComplexScript = "+mn-cs"
    .NameFarEast = "+mn-ea"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(0, 0, 0)
    .Fill.Transparency = 0
    .Fill.Solid
    .Size = 18
    .Italic = msoFalse
    .Kerning = 12
    .Name = "+mn-lt"
    .UnderlineStyle = msoNoUnderline
    .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
    End Sub
    Last edited by Rick Corbett; 2018-07-11 at 14:19. Reason: Removed yellow font as this is invisible to many people with colour blindness.
    TIA
    dubdub

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,011
    Thanks
    157
    Thanked 786 Times in 716 Posts
    Hi dubdub,

    No, that was not the code. It was a left over module that I forgot to remove.

    The code lies in the worksheet module. it is designed to automatically break apart data listed vertically for numerous charts. If you insert a blank row within the existing data you will see a gap in the data form in the chart. As you add data to that row, you will see each series update in that chart. if you insert a row then paste the values for each series on that row, the chart will update. The same will happen when deleting rows.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '---------------------------
    'FIND LASTROW OF DATA
    LASTROW = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    '---------------------------
    'FIND FIRSTROW OF DEPARTMENT FOR CHANGED CELL
    DepRow = ActiveSheet.Cells(Target.Row + 1, 1).End(xlUp).Row
    Dep = Cells(DepRow, 1) 'NAME OF DEPARTMENT
    '---------------------------
    'FIND ASSOCIATED SERV VALUE
    If Cells(DepRow, 2) <> "" Then
        Serv = Cells(DepRow, 2)
    Else
        ServRow = ActiveSheet.Cells(DepRow, 2).End(xlUp).Row
        Serv = Cells(ServRow, 2)
    End If
    '---------------------------
    'LOOP THROUGH CHARTS
    For Each Cht In ActiveSheet.ChartObjects
        Cht.Select
    '---------------------------
    'IDENTIFY CHART BY CHART TITLE AND UPDATE
        If ActiveChart.ChartTitle.Text = Dep & "-" & Serv Then
            EndRow = Cells(DepRow, 1).End(xlDown).Row - 1
            If EndRow > LASTROW Then EndRow = LASTROW
            x = "=Sheet1!D" & DepRow & ":D" & EndRow
            ActiveChart.FullSeriesCollection(1).Values = x
            x = "=Sheet1!E" & DepRow & ":E" & EndRow
            ActiveChart.FullSeriesCollection(2).Values = x
            x = "=Sheet1!F" & DepRow & ":F" & EndRow
            ActiveChart.FullSeriesCollection(3).Values = x
            x = "=Sheet1!C" & DepRow & ":C" & EndRow
            ActiveChart.FullSeriesCollection(1).XValues = x
            Target.Offset(1, 0).Select
            Exit Sub
        End If
    Next Cht
    End Sub
    The code will not create a new chart because I misread your initial post. Will work on that.

    Maud

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,398
    Thanks
    3
    Thanked 237 Times in 216 Posts
    Quote Originally Posted by dubdub View Post
    i got an error message that says compiler error method or data member not found with reference to the text FullSeriesCollection in the third row of the code. any help
    If your version of Excel is earler than 2013, you won't have access to the FullSeriesCollection property.
    Regards,
    Rory

  7. #7
    4 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    402
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Maudibe,
    still getting the same compiler error "method or data member are not found" and this as highlighted by Rory attributed to excel version which is valid since mine is 2010.
    i hope this not going to complicate the matter more....appreciate your patience
    TIA
    dubdub

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,398
    Thanks
    3
    Thanked 237 Times in 216 Posts
    Try just using Seriescollection instead of Fullseriescollection.
    Regards,
    Rory

  9. #9
    4 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    402
    Thanks
    1
    Thanked 1 Time in 1 Post
    Quote Originally Posted by rory View Post
    Try just using Seriescollection instead of Fullseriescollection.
    hi rory,
    i made that chnage as shown below, and it gives run-time error 424 Object required, what is wrong or missing. note to help me run it any time i am using public sub

    Public Sub Test_macro()
    '---------------------------
    'FIND LASTROW OF DATA
    LASTROW = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    '---------------------------
    'FIND FIRSTROW OF DEPARTMENT FOR CHANGED CEL
    DepRow = ActiveSheet.Cells(Target.Row + 1, 1).End(xlUp).Row
    Dep = Cells(DepRow, 1) 'NAME OF DEPARTMENT
    '---------------------------
    'FIND ASSOCIATED SERV VALUE
    If Cells(DepRow, 2) <> "" Then
    Serv = Cells(DepRow, 2)
    Else
    ServRow = ActiveSheet.Cells(DepRow, 2).End(xlUp).Row
    Serv = Cells(ServRow, 2)
    End If
    '---------------------------
    'LOOP THROUGH CHARTS
    For Each cht In ActiveSheet.ChartObjects
    cht.Select
    '---------------------------
    'IDENTIFY CHART BY CHART TITLE AND UPDATE
    If ActiveChart.ChartTitle.Text = Dep & "-" & Serv Then
    EndRow = Cells(DepRow, 1).End(xlDown).Row - 1
    If EndRow > LASTROW Then EndRow = LASTROW
    x = "=Sheet1!D" & DepRow & "" & EndRow
    ActiveChart.SeriesCollection(1).Values = x
    x = "=Sheet1!E" & DepRow & ":E" & EndRow
    ActiveChart.SeriesCollection(2).Values = x
    x = "=Sheet1!F" & DepRow & ":F" & EndRow
    ActiveChart.SeriesCollection(3).Values = x
    x = "=Sheet1!C" & DepRow & ":C" & EndRow
    ActiveChart.SeriesCollection(1).XValues = x
    Target.Offset(1, 0).Select
    Exit Sub
    End If
    Next cht
    End Sub
    Last edited by dubdub; 2018-07-13 at 00:07.
    TIA
    dubdub

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,398
    Thanks
    3
    Thanked 237 Times in 216 Posts
    You seem to have tried to change a Worksheet_Change code into a normal routine, but that means you have no Target range set (the range that was changed in a Change event). Why are you trying to make it into a normal routine?

  11. #11
    4 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    402
    Thanks
    1
    Thanked 1 Time in 1 Post
    i noticed with headings"Private Sub Worksheet_Change(ByVal Target As Excel.Range)" every time i want to run using the run macro menu button the macro window to create a macro pop in, even-though i changed the side drop down menu from "General" to "Worksheet" and in the other drop down menu i select "Change". to summarize i want to use the code in a normal module any time i want as well as to overcome the pop in of the macro window, so, kindly excuse my ignorance if what i said does not make any sense, i am still 6 feet under when it comes to VBA.
    TIA
    dubdub

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,011
    Thanks
    157
    Thanked 786 Times in 716 Posts
    dubdub,

    I fired up my old PC which has Excel 2010 and made the compatability changes rory spoke of. The code has also been amended to add a new chart when a new dept is added in column A.

    HTH,
    Maud
    Attached Files Attached Files

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,011
    Thanks
    157
    Thanked 786 Times in 716 Posts
    My sincere apology, i felt i did not explain my request properly. a feeling i observed by the end product.

    what i have in mind at the time and still is to be able to create multi-charts based on the following steps

    1. Read the data say for example in d2:f22.
    2. For this range column "C" will represent the labels, and It will start at C2
    3. Row D2:f3 will be representing the x-axis values/labels.
    4. the trigger for a new chart will be any time there is a change in columns a2:b22.
    5. The data in these two columns a2:b22 will be used as well for the titles of the chart.

    and i want to thank you again for all the help and time you give to my post and I totally understand if you want to call it a final.

    Hi dubdub,

    In your previous example in post #3, you added an row and inserted "D3" as the department but a new chart was not created
    and when i add more rows of data, attached,no change/update happened on the chart side, no new chart is created for D3.
    In my last posted sample, please insert a row then add the department "D3". You will be prompted for the SERV. Enter it then press OK. A new chart will be created with the new Title. Now just start to fill in the data for the new chart and it will update on the fly. Add new rows anywhere in the table then add data and the existing chart data ranges will be adjusted and the charts will update.

    I think this meets all your 5 points above. Perhaps I should have explained better how to use it. Let me know if you still are looking for something different.

    Maud

  14. #14
    4 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    402
    Thanks
    1
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    Hi dubdub,

    In your previous example in post #3, you added an row and inserted "D3" as the department but a new chart was not created


    In my last posted sample, please insert a row then add the department "D3". You will be prompted for the SERV. Enter it then press OK. A new chart will be created with the new Title. Now just start to fill in the data for the new chart and it will update on the fly. Add new rows anywhere in the table then add data and the existing chart data ranges will be adjusted and the charts will update.

    I think this meets all your 5 points above. Perhaps I should have explained better how to use it. Let me know if you still are looking for something different.

    Maud
    hi Maud,
    it does what you mentioned, but it will take more time to enter departments one by one and then populated each with the required data, I want to avoid this interactive mechanism by the code with a generic one that fulfill those 5 steps. Thanks again.
    TIA
    dubdub

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,011
    Thanks
    157
    Thanked 786 Times in 716 Posts
    Hi dubdub,

    Can you please walk me through a scenario of what data you are staring with, what data is being added by the user, and based on the added data, what you want to occur along with the end result?

    Thanks,
    Maud

Page 1 of 2 12 LastLast

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
  •