Pasting a Bitmap to a UserForm control
This one is odd because it's undocumented and somewhat counter-intuitive. But, on the other hand, it's also a very useful tip.
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 wait! 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.
More
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. For ways to display a "live" chart on a UserForm, see: