Solved

Excel VBA - Add Chart to Userform

Posted on 2014-04-21
5
10,520 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

739 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