Solved

Excel VBA - Add Chart to Userform

Posted on 2014-04-21
5
10,952 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

628 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