Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: Solved: VBA to group few datalabel formatting options into a macro

  1. #1

    Solved: VBA to group few datalabel formatting options into a macro

    Hi All,

    I have few charts on ppt slides. In each slide i need to select particular chart and select its data labels for format few of them as. border style = dash and border color = Green(R=0,G=128,B=0), underline and Bold. I need to do this for each chart and selected few data labels in it. Can i group the above formatting work in to a macro. so that I just need to select the data label and click the macro which will do the required formatting at single go. it would save me few hours as I report has around 400 slides.

    I am an beginner to VBA.

    Can anyone kindly help,

    Thanks a lot!!

    Regards,
    Praveen

  2. #2
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    You really need to say which version of Office.
    For 2010 this might work (not tested)

    Sub DLFormat()
    Dim ocht As Chart
    Dim i As Integer
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
    For i = 1 To ocht.SeriesCollection.Count
    If ocht.SeriesCollection(i).HasDataLabels Then
    With ocht.SeriesCollection(i).DataLabels.Format.TextFrame2.TextRange.Font
    .Fill.Visible = msoTrue
    .Bold = True
    .UnderlineStyle = msoUnderlineSingleLine
    End With
    With ocht.SeriesCollection(i).DataLabels.Format.Line
    .Visible = True
    .Weight = 1
    .DashStyle = msoLineDash
    .ForeColor.RGB = RGB(0, 128, 0)
    End With
    End If
    Next i
    End Sub
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  3. #3

    VBA to group few datalabel formatting options into a

    Hi John,

    Thanks for the help it really works. But I need a little tweek
    This code currently selects all the data labels in the chart and applies a border green dotted line to it. but i need to apply for selected data labels only.

    I need to select a particular datalabel and apply this formatting. so can we modify this a little for applyying to only the selected data label.

    Thanks so much..

    Regards,
    Praveen

  4. #4
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    Not simple to do that but this might work to choose the series:

    Sub DLFormat()
    Dim ocht As Chart
    Dim i As Integer
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
    i = InputBox("Which series?", "Select series to modify")
    If ocht.SeriesCollection(i).HasDataLabels Then
    With ocht.SeriesCollection(i).DataLabels.Format.TextFrame2.TextRange.Font
    .Fill.Visible = msoTrue
    .Bold = True
    .UnderlineStyle = msoUnderlineSingleLine
    End With
    With ocht.SeriesCollection(i).DataLabels.Format.Line
    .Visible = True
    .Weight = 1
    .DashStyle = msoLineDash
    .ForeColor.RGB = RGB(0, 128, 0)
    End With
    End If
    End Sub
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  5. #5

    VBA to group few data label formatting options into a macro

    Hi John,

    The above ask code for no.of series but then returns an error. Attached is dummy report with a single slide. I need to format the data labels as shown there.


    the number of series varies from 20 to 30 some charts. It may not be easy to remember the exact number of series to format.

    So is there way to select a partcular data label and then click on the macro button. 'coz i update these dotted boxes using another excel sheet.(by Alt+Tab). so is there a way to modify this code so that it runs based the data label that is selected inside the chart. one data label at a time.The one that is selected.

    I know i am asking too much, but this is one of daily tasks..

    Please see the attachement...

    Thanks for your help...

    Regards,
    Praveen.
    Attached Images Attached Images

  6. #6
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    I don't kmow any way to work with the selected labels.

    The chart you show has only one series and 14 points (numbered 1 - 14 starting at the bottom)

    Maybe this would help NOTE The website has inserted a space in Fo nt - you will need to remove it.

    Sub DLFormat2()
    Dim ocht As Chart
    Dim i As Integer
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
    i = InputBox("Which Point?", "Select point to modify")
    If ocht.SeriesCollection(1).HasDataLabels Then

    With ocht.SeriesCollection(1).Points(i).DataLabel.Format.TextFrame2.TextRange.Fo nt
    .Fill.Visible = msoTrue
    .Bold = True
    .UnderlineStyle = msoUnderlineSingleLine
    End With
    With ocht.SeriesCollection(1).Points(i).DataLabel.Format.Line
    .Visible = True
    .Weight = 1
    .DashStyle = msoLineDash
    .ForeColor.RGB = RGB(0, 128, 0)
    End With
    End If
    End Sub
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  7. #7
    Thanks so much John!!!!!

    This will reduce much of work... Thanks a ton!!!1

  8. #8
    Hi John,

    I made a little change to your code as below.

    Sub Green()
    Dim ocht As Chart
    Dim i As Integer
    Dim j As Integer
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
    j = InputBox("Which bars?", "Select bars set to modify")
    i = InputBox("Which Point?", "Select point to modify")
    If ocht.SeriesCollection(j).HasDataLabels Then

    With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
    .Visible = True
    .Weight = 1
    .DashStyle = msoLineDash
    .ForeColor.RGB = RGB(0, 128, 0)
    End With
    End If
    End Sub

    i assigned this to a green button on toolbar. now everytime i run it. i need to run it 2 times for the output to come. the dotted line border doesnt appears in the first instance.

    Do you know why its showing on the second run but not on the 1st run. i am working on ppt2010.

    Thanks & Regards,
    Praveen

  9. #9
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    Looks like the visible has to come AFTER you set the border

    Sub Green()
    Dim ocht As Chart
    Dim i As Integer
    Dim j As Integer
    Dim x As Integer
    On Error GoTo err
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
    Do
    j = InputBox("Which bars?", "Select bars set to modify")
    If j > ocht.SeriesCollection.Count Then
    MsgBox "Out of range"
    End If
    Loop While j > ocht.SeriesCollection.Count
    Do
    i = InputBox("Which Point?", "Select point to modify")
    If i > ocht.SeriesCollection(j).Points.Count Then
    MsgBox "Out of range"
    End If
    Loop While i > ocht.SeriesCollection(j).Points.Count
    If ocht.SeriesCollection(j).HasDataLabels Then
    With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
    .Weight = 1
    .DashStyle = msoLineDash
    .ForeColor.RGB = RGB(0, 128, 0)
    .Visible = True
    DoEvents
    End With
    End If
    Exit Sub
    err:
    MsgBox "You must select a chart", vbCritical
    End Sub
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  10. #10

    VBA to group few data label formatting options into a macro

    Hi John,

    The code is inserting a black dotted border. Though its working in the first instance.

    The RGB code is fine, but i am not able to fix why the color is black instead of green???

    Thanks,
    Praveen

  11. #11
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    Chart vba has always been a nightmare!

    Maybe move the color line up.

    Sub Green()
    Dim ocht As Chart
    Dim i As Integer
    Dim j As Integer
    Dim x As Integer
    On Error GoTo err
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
    Do
    j = InputBox("Which bars?", "Select bars set to modify")
    If j > ocht.SeriesCollection.Count Then
    MsgBox "Out of range"
    End If
    Loop While j > ocht.SeriesCollection.Count
    Do
    i = InputBox("Which Point?", "Select point to modify")
    If i > ocht.SeriesCollection(j).Points.Count Then
    MsgBox "Out of range"
    End If
    Loop While i > ocht.SeriesCollection(j).Points.Count
    If ocht.SeriesCollection(j).HasDataLabels Then

    With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
    .Visible = True
    .ForeColor.RGB = RGB(0, 128, 0)
    .Weight = 1
    .DashStyle = msoLineDash
    DoEvents
    End With

    End If
    Exit Sub
    err:
    MsgBox "You must select a chart", vbCritical
    End Sub
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  12. #12

    VBA to group few datalabel formatting options into a macro

    Hi John,

    The code is working fine.. Thanks so much again... Thanks a lot!!

    Do you have any tutorials to learn more about chart VBA... Please ignore my request if i am not supposed to ask these in the forum....

    Regards,
    Praveen

  13. #13
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    I don't have much. As I said chart vba in PPT is a nightmare and varies from version to version.

    I usually start by recording a macro in Excel to give me a clue but this rarely produces working code straight away!
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  14. #14
    Ok, thanks John!!!

  15. #15
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    Quote Originally Posted by kevvukeka
    Ok, thanks John!!!
    Thought you would like the file I worked with!
    When you select a chart and Chart Tools > Format there should be a new button to run the code.

    http://www.pptalchemy.co.uk/Downloads/Green.pptm

    If you want it ALWAYS available save as choose ppam as the type and then go to Developer tab > AddIns and Add New.

    If you cannot see the Developer Tab >> Options > Customize Ribbon and tick DEVELOPER
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  16. #16
    Thanks John.. Will use it.. Actually I need to know all the options that can be done for a seriescollection-points. basically most of my work includes formatting the data labels in a chart or adding text boxes besides the data labels in a chart putting some alphabest in thos etext box..

    What is the best to know all the options available for a data label. do i need to record them first in excel to know all the formatting options???

  17. #17
    I have learned so much from reading this post alone - great

  18. #18
    Hi John,

    Below is the code which you helped me few days back. In this code can we use multiple values for the input box. Using the below code I need to run it for each label which I need to format. which is good.

    When I have a chart, where I know point 4, 5, 6 needs to have dotted green line I need to run this 3 times. So how can I use a input to give multiple values. For e.g 4,5,6 data labels need to be formattted with green dottled line.

    Sub Green()
    Dim ocht As Chart
    Dim i As Integer
    Dim j As Integer
    Dim barc As Integer
    Dim pointc As Integer
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
    barc = ocht.SeriesCollection.Count
    pointc = ocht.SeriesCollection(1).Points.Count
    j = InputBox("Which bars?", "Select bars set to modify-" & barc)
    i = InputBox("Which Point?", "Select point to modify-" & pointc)
    If ocht.SeriesCollection(j).HasDataLabels Then

    With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
    .Visible = True
    .Weight = 1
    .DashStyle = msoLineDash
    .ForeColor.RGB = RGB(0, 128, 0)
    End With
    End If
    End Sub


    Thanks Lot for your help.

    Regards,
    Praveen

  19. #19
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    This sort of thing maybe

    Sub Green()
    Dim ocht As Chart
    Dim i As String
    Dim x As Integer
    Dim rayPoints() As String
    On Error GoTo err
    Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart

    i = InputBox("Which Points?", "Select points to modify, separated by comma e.g. 2,3,4")

    If ocht.SeriesCollection(1).HasDataLabels Then
    rayPoints = Split(i, ",")
    For x = 0 To UBound(rayPoints)
    With ocht.SeriesCollection(1).points(rayPoints(x)).DataLabel.Format.Line
    .Visible = True
    .ForeColor.RGB = RGB(0, 128, 0)
    .Weight = 1
    .DashStyle = msoLineDash
    DoEvents
    End With
    Next x
    End If
    Exit Sub
    err:
    MsgBox "You must select a chart", vbCritical
    End Sub
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  20. #20
    Thank you John!!!

Posting Permissions

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