Displaying “live” chart data in Excel Userform

Question

I have been researching the modes of displaying charts in a userform.

The general consensus seems to be to save the chart as a .GIF file and then upload this within the userform as an image. However, this would mean that anyone using the userform would have to have the file saved as an image to view the information. (I do not believe that any of my associates will take the time to learn how to do this, they just want a quick chart-view).

Is there a workaround to display the chart (which is constantly being updated by data that comes in) within a userform?

I tried multiple avenues and did not finding anything. Also, my Excel 2013 does not appear to have the Microsoft Office Charts option within the Toolbox, is this something that has been changed?

Answer 1

Case 1: If the chart is on the worksheet, it will be easier as below:

Private Sub UserForm_Initialize()
    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("Data").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp1.gif"
    MyChart.Export Filename:=Fname, FilterName:="GIF"
End Sub

Case 2: If chart is not on the worksheet, you may need to create a temporary chart, save it as GIF, delete it, and finally load the picture {

Me.Image1.Picture = LoadPicture(Fname)

} when the Userform is initialized.

Case 1 is easier to code. The above code still works even I cut and paste the chart in a later-hidden worksheet.