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.

    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:

    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…-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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!