Automate Chart with filtered values

[krishnak]

Hi All,
I have a worksheet with a chart for all resources. The chart has three benchmarks (BM1, BM2 and BM3) - horizontal lines running across the chart in different colors.

Now I installed four buttons below the chart. When I click on a button, the series values should be filtered as per the caption of the button and the chart automatically displays the filtered values. But it retains the Benchmark values as earlier. The chart title should change appropriately.

For example, if I actuate "Yellow" button, the values get filtered by the Group column and those will be the series values for the new chart. I do not want this to display as a new chart, but change the display in the present chart.

I have a task of producing charts for all groups and individual groups. And I have many types of resources in other worksheets. It is a pain generating individual charts and adding benchmarks for each chart.

Any help will be appreciated.

[Bob Phillips]

Put this in the worksheet module




Private Sub btnYellow_Click()
Call SetupChart("Yellow")
End Sub
Private Sub btnBlue_Click()
Call SetupChart("Blue")
End Sub
Private Sub btnGreen_Click()
Call SetupChart("Green")
End Sub
Private Sub btnRed_Click()
Call SetupChart("Red")
End Sub

Private Sub SetupChart(ByVal Colour As String)
Dim SeriesValues As String
Dim SeriesLabels As String
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 3 To LastRow

If .Cells(i, "C").Value2 = Colour Then

SeriesValues = SeriesValues & .Name & "!" & .Cells(i, "A").Address(, , True) & ","
SeriesLabels = SeriesLabels & .Name & "!" & .Cells(i, "B").Address(, , True) & ","
End If
Next i

With .ChartObjects("Chart 1").Chart

.SeriesCollection(1).XValues = "=(" & Left$(SeriesValues, Len(SeriesValues) - 1) & ")"
.SeriesCollection(1).Values = "=(" & Left$(SeriesLabels, Len(SeriesLabels) - 1) & ")"
End With
End With
End Sub

[krishnak]

Thanks, xld. This is awesome.
I am not very conversant in using VBA for charts. Let me go through it. In fact I added one more button, "All", to revert back to the original chart.
The buttons do not work from the worksheet, but the code works from the module.
I'll work on this and come back.
Thanks again for the response.

[Bob Phillips]

They work from the worksheet for me.

[krishnak]

This is interesting.
I created a module for this workbook and pasted the code you wrote.
When I come back to the worksheet and click on the buttons, nothing happens. However when I go into the module code and press the "Run" command, this works OK.
What is missing in my worksheet?

[Bob Phillips]

Not this workbook, the worksheet. Right-click the worksheet tab, and click the View code option.

[krishnak]

Thanks, xld. I got it!

[krishnak]

Hi xld,

Here is a supplementary question.

How do I reduce the width of the chart by VBA?
I want to reduce the width of the chart when the number of values are less.

[Bob Phillips]

How about this?



Option Explicit

Private Const BASE_WIDTH As Double = 1002
Private Const BASE_HEIGHT As Double = 418

Private Sub btnYellow_Click()
Call SetupChart("Yellow")
End Sub
Private Sub btnBlue_Click()
Call SetupChart("Blue")
End Sub
Private Sub btnGreen_Click()
Call SetupChart("Green")
End Sub
Private Sub btnRed_Click()
Call SetupChart("Red")
End Sub

Private Sub SetupChart(ByVal Colour As String)
Dim SeriesValues As String
Dim SeriesLabels As String
Dim NumValues As Long
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 3 To LastRow

If .Cells(i, "C").Value2 = Colour Then

NumValues = NumValues + 1
SeriesValues = SeriesValues & .Name & "!" & .Cells(i, "A").Address(, , True) & ","
SeriesLabels = SeriesLabels & .Name & "!" & .Cells(i, "B").Address(, , True) & ","
End If
Next i

With .ChartObjects("Chart 1")

With .Chart

.SeriesCollection(1).XValues = "=(" & Left$(SeriesValues, Len(SeriesValues) - 1) & ")"
.SeriesCollection(1).Values = "=(" & Left$(SeriesLabels, Len(SeriesLabels) - 1) & ")"
End With

.Width = BASE_WIDTH * NumValues / (LastRow - 2) * 2
.Height = BASE_HEIGHT * NumValues / (LastRow - 2) * 2
End With
End With
End Sub

[krishnak]

I am sorry, xld. I was out for a few days and could not visit the forum and your response.

The code is working well now and I made some changes to suit my task.
Thanks again for the immense help I got.