Chart depending on combobox selection

Hello,

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.

forum.ozgrid.com/index.php?attachment/68947/

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:

Dim ws As WorksheetDim 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:

Dim Fname As String


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

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!

I also post this message in another forum http://www.excelguru.ca/forums…-charts&p=23915#post23915. User Jon Peltier answered a great way to do it in excel sheets, but I would need to find a way to do it by vba.

Re: Chart depending on combobox selection

any idea someone?

Any help will be so much appreciated.

Thanks in advance.