Pasting An Image To A UserForm Control
VBA programmers are familiar with Excel's UserForms. The UserForm object, as well as many of the ActiveX controls that are placed on a UserForm, can display an image -- the object's Picture property.
How to set the Picture property
If you believe Excel's online help, you'll think that there are two ways to set the Picture property for an object:
- At design time: Browse for an image file, by clicking the "..." button in the Properties box
- At run-time: Use VBA's LoadPicture function to retrieve an image file.
But there's another way. And it doesn't even require that the image is stored in a file. Here's the trick:
- Locate the image that you want to use, and copy it to the Clipboard.
- Activate the VB Editor and select the object that will contain the image (i.e., a UserForm, or a control that has a Picture property).
- Make sure the Properties window is displayed (press F4 it it's not).
- Click the Picture item. By default, this displays "(None)" as in the figure above.
- Press Ctrl+V to paste the image on the Clipboard to the control.
Importantly, the picture that you paste to a control need not be stored in a file. Any picture that can be copied to the Clipboard can be pasted to an object's Picture property. And this includes charts stored in a worksheet. The trick here is to copy the chart as a picture: Click the chart, press Shift, and choose Copy Picture from Excel's Edit menu. Once copied, you can paste the picture of the chart to your UserForm control.
When pasting a chart, it's important to understand that the picture is not linked to the original chart. So if the chart changes, the picture will not change.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks