• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12155
  • Last Modified:

Excel VBA - Add Chart to Userform

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
JohnNZExcel
Asked:
JohnNZExcel
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
JohnNZExcelAuthor Commented:
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
 
JohnNZExcelAuthor Commented:
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
 
Jon_PeltierExcel DeveloperCommented:
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
 
JohnNZExcelAuthor Commented:
Greatly appreciate you taking the time to add to the discussion - thank you.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now