Displaying A Chart In A Userform

Category: UserForms | [Item URL]

A companion file is available: Click here to download

One of my favorite Excel tricks is to display a chart in a UserForm. You might think that this would be easy: Just add a linked picture of the chart to the UserForm. Unfortunately, that's not possible.

This tip describes a workaround that involves saving the chart as a GIF file, and then displaying the GIF in an Image control. This ensures that the UserForm always displays the current version of the chart.

You might notice a slight delay as the chart is saved and then retrieved. On a fast system, however, this delay is barely noticeable.

How it works

To set this up:

  1. Create your chart or charts as usual.
  2. Insert a UserForm and then add an Image control.
  3. Write VBA code to save the chart as a GIF file, and then set the Image control's Picture property to the GIF file. You need to use the LoadPicture function to do this.
  4. Add other bells and whistles as desired. For example, the demo file displays multiple charts, and the user can cycle through them.

Saving a chart as a GIF file

The code below demonstrates how to create a GIF file (named temp.gif) from a chart (the first chart object on Sheet1).

    Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp.gif"
    CurrentChart.Export FileName:=Fname, FilterName:="GIF"

Changing the Image control's Picture Property

If the Image control on the UserForm is named Image1, the statement below loads the image (represented by the Fname variable) into the Image control.

        Image1.Picture = LoadPicture(Fname)

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy