How to make this code run smoother? And have it unselect at the end

Question

I am trying to change the color of my bar graph based on each button that I click. And at the end of the code, I do not want it to select on the graph. Here is one of my code for the button. Hoping if there any way to make this code better or maybe go at it in a different direction. I am also trying to combine these two codes.

Sub Add_Value_Field

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim sField As String

    Set pt = ActiveSheet.PivotTables(1)
    sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text

    For Each pf In pt.DataFields
    If pf.Name <> "Values" Then
        pf.Orientation = xlHidden
    End If
    Next pf

    pt.PivotFields(sField).Orientation = xlDataField

End Sub

With this code:

Sub Unselect()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoTheColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
End Sub

I hope this made it easier to understand.

Have a look at [this answer](https://stackoverflow.com/a/10717999/8769365) on how to avoid select. — Nacorid, Apr 16 '19 at 13:06
Yea, sorry I am new to VBA and I did this from the macro record. I am not really sure how to select the chart without the .select — Phiking, Apr 16 '19 at 13:10

score 0 · Answer 1 · answered Apr 16 '19 at 14:28

Charts are tricky to work with. I've seen myself in a position that the code just will not work if I don't use .Activate on the chart. In this case the way to make the code simpler, but it will work the same without being any faster.

Sub Unselect()
    Set wk = ThisWorkbook
    'We are working with charts, we have to activate the charts sometimes.
    'Adding this next two lines will let your code work from any other Sheet.
    wk.Sheets("SheetName").Activate
    wk.Sheets("SheetName").ChartObjects("Chart 1").Activate
    'Skip the select portion and go right to the format.
    With ActiveChart.FullSeriesCollection(1).Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoTheColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
End Sub

Thank you, but for some reason, I see object not supported. Sorry, maybe I was not being too clear and specific. I want to combine this code with another code. I just edit the main post. Thank you! — Phiking, Apr 16 '19 at 14:55

How to make this code run smoother? And have it unselect at the end

1 Answers1