Dynamic charts

ignasis

New member
Joined
Apr 1, 2016
Messages
28
Reaction score
0
Points
0
Hello again!

This time I would like to ask you about charts. My first problem is that I don't know which of the different methods to use charts in vba is better for my situation.

I attach you an example which I think you will understand easy.

View attachment charts.xlsm

What I need is to show a chart depending on combobox selection. However, this charts are not previously created, so the code should select the correct range and display a chart. And I don't need the charts saved as .gif files. An idea would be to create the .gif and when I close the form, delete that gif.


So, for example:

- I select May 2016 and Chairs --> it should appear a column chart with only 1 column showing number of chairs sold in May (this case would be cell G11)


- I select May 2016 and Chairs and checkbox Year to Date --> it should appear a column chart with 5 columns, every column for number of chairs sold from January to May (cells C11:G11)


- I select May 2016 and show all and Year to date --> it should appear a column chart displaying tables, chairs, beds and tvs sold from january to may (c8:G8, C11:G11, C14:G14 and C17:G17)


I was going to use a code like this:
Code:
Dim ws As Worksheet
Dim lcol As Long


Set ws = Sheets("Home")


With ws
    For lcol = 3 To .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, searchdirection:=xlPrevious).Column
        If CStr(.Cells(5, lcol)) = cboYear.Value And .Cells(6, lcol) = cboMonth.Value Then
        
'now this should select the range depending on combobox selection


        End If
    Next
End With

and the chart code I found that maybe could be used for this:
Code:
Dim Fname As String


    Call SaveChart
    Fname = ThisWorkbook.Path & "\temp1.gif"
    Me.Image1.Picture = LoadPicture(Fname)
End Sub


Code:
Private Sub SaveChart()
    Dim MyChart As Chart
    Dim Fname As String


    Set MyChart = Sheets("Home").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp1.gif"
    MyChart.Export Filename:=Fname, FilterName:="GIF"
End Sub

I hope I explained good, and thanks for your attention!
 
Last edited:

Jon Peltier

New member
Joined
Mar 21, 2011
Messages
8
Reaction score
0
Points
0
Location
Worcester, MA
Website
peltiertech.com
I wouldn't use VBA at all. This is more a data question than a chart question.

I would first rearrange the data into a columnar layout. The columns would contain Year, Month, Item, View ("Monthly" or "YTD"), and Monthly (either the per-month value or the cumulative YTD). Then I would turn this range into a Table.

I'd make a pivot table from the table above. View would go in the Filters area, Year and Month into the Rows area, Item into the Columns area, and Monthly into the Values area.

I'd insert three slicers, one each for Month, Item, and View. Probably a fourth for Year, but I omitted that one in my practice worksheet. All but the View slicer would be multi-select enabled.

Finally, I'd select the pivot table and insert a column chart. This automatically becomes a pivot chart, and plots all the data showing in the pivot table. At first the chart is pretty cluttered, but you can use the slicers to easily and quickly adjust what is viewed.
 

Jon Peltier

New member
Joined
Mar 21, 2011
Messages
8
Reaction score
0
Points
0
Location
Worcester, MA
Website
peltiertech.com
Here is my illustrated example, which I merely described in words in the previous post.

I wouldn't use VBA at all.

First I would rearrange the data, and place it into a table as shown below. In the same view are the pivot table I would make from the table. Year and Month go into the Rows area, Item into the Columns area, View into the Filters area, and Sum of Monthly into the Values area. Finally next to the pivot table are the slicers I would add. One for Month, which is multi-select enabled, one for View which is single select, and one for Item, which is multi-select enabled.

attachment.php



Now click on the pivot table and insert a chart. It's shown below next to the slicers. The chart is pretty cluttered, but that's where the slicers help out.

attachment.php



The slicers below have been adjusted to show YTD sales of Chairs for January through May:

attachment.php



Now the slicers have been adjusted to compare Monthly sales of Chairs and Tables just for May:

attachment.php



This simple setup gives you a great deal of flexibility. My test workbook is View attachment furniture_sales.xlsx.
 

Attachments

  • Table-Pivot-Slicers.png
    Table-Pivot-Slicers.png
    68.6 KB · Views: 62
  • PivotChart1.png
    PivotChart1.png
    25.9 KB · Views: 61
  • PivotChart2.png
    PivotChart2.png
    21.7 KB · Views: 57
  • PivotChart3.png
    PivotChart3.png
    20 KB · Views: 61

ignasis

New member
Joined
Apr 1, 2016
Messages
28
Reaction score
0
Points
0
Jon Peltier, thanks very much for your time and effort on this.

This actually is very useful to solve my problem. Now I have to think about a relationship between your idea and my form, as I would prefer to see the charts in the form.

So maybe I could link a checkbox/optionbutton/combobox to the filters in your sheet and display the resulting chart in the form?

Again, thank you very much Jon!
 

ignasis

New member
Joined
Apr 1, 2016
Messages
28
Reaction score
0
Points
0
I've been trying to fit your solution with my form but i'm afraid i haven't succeeded in.

so unfortunately i will have to ask for more solutions. Anyways, thanks Jon Peltier, I will use your solution in other issues I am dealing with too!
 

ignasis

New member
Joined
Apr 1, 2016
Messages
28
Reaction score
0
Points
0
Finally used your brilliant example to solve all my needs. It's simply the best way to manage charts i've been able to find.

Thanks a lot again Jon.
 
Top