Solved

Excel VBA - Add Chart to Userform

Posted on 2014-04-21
5
9,266 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:
teylyn 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now