Solved

Excel VBA - Add Chart to Userform

Posted on 2014-04-21
5
10,066 Views
Last Modified: 2014-04-26
I have an Excel VBA userform that loads with some data from a spreadsheet when it opens/initialises.  I am also wanting to include in the userform a chart image (the chart exists on the spreadsheet).
Can anybody help?  I am hoping to avoid the image being stored in a folder (i would prefer it to be stored in the file) but would reconsider if that makes it work better and easier.
Chart-Userform.xlsm
0
Comment
Question by:JohnNZExcel
  • 3
5 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 40013893
Kia ora,

there are several different approaches you can take. John Walkenbach has a technique that saves the chart as a GIF and then imports it into the userform. Details here: http://www.j-walk.com/ss/excel/tips/tip66.htm

Quoting:

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"

Open in new window

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)

Open in new window


Another approach is to copy the chart, then use Stephen Bullen's PastePicture routine to put it into the user form.

The userforms in Excel 97 are great, apart from one BIG letdown - you can't put charts on them!  A kludgy workaround has been to export the chart as a gif or jpg, then load it into an Image control using LoadPicture.  That works ok-ish for charts, but it can't handle word art, shapes or other pictures created on the fly.  This file includes code to create a Picture object from whatever is on the clipboard.  Display a chart on a userform is now as easy as a copy/paste.
Download PastePicture.Zip from this link: http://www.oaltd.co.uk/Excel/Default.htm

cheers, teylyn
0
 

Author Comment

by:JohnNZExcel
ID: 40013945
Thanks Teylyn, i had read various blogs and forum posts about both of these methods and was testing the water to see; 1. how best to piece it together to make it work 2. if there were any new methods for 2010 and 2013 versions of Excel and also 3. where to find the Load Picture code (i had lost the CD that came with a Programming book that described the method).  Seems like there have been no recent developments but i have made it work for my purposes fine (thank you).  I will see if it also works on mac....
0
 

Author Comment

by:JohnNZExcel
ID: 40019687
I managed to get everything connected OK.  I could not make it work on my Mac but that was somewhat to be expected.  Hopefully Excel 2013 opens some new pathways for this kind of task.  Happy with the outcome!
0
 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 40025149
FWIW, the Stephen Bullen code, while powerful, was written using Windows APIs for 32-bit Excel. You will need to translate it to 64-bit Windows APIs, then use conditional compiling so that users of both 32- and 64-bit Excel will be able to use the code. And it will not run on a Mac.

John Walkenbach's code is simpler and should work on all platforms.
0
 

Author Comment

by:JohnNZExcel
ID: 40025358
Greatly appreciate you taking the time to add to the discussion - thank you.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question