The MDUF implementation for pictures in UserForms is incomplete. It isn’t much more than a partial workaround for capabilities that have been missing from Office for Mac going back to pre-2011 days. Although MDUF-generated projects can’t provide dynamic pictures—pictures that users define at runtime—it can provide pictures that are known at the time you build your UserForms.
- The issue
- Concept of operations for pictures on MDUF-generated UserForms
- Working with picture libraries
- Specifying Picture or MouseIcon properties of a control or UserForm
The issue
The Windows versions of VBA for Office include a method named LoadPicture, which takes as its first argument the pathname of the picture file to be loaded into the control that hosts the picture. This method is the means by which the system inserts a picture into a control on a UserForm, or into the UserForm itself. Because LoadPicture doesn’t exist for Office for Mac, and because no functional equivalent exists in Office for Mac, any pictures your system needs must be on board in the workbook before you deliver it to your users.
Moreover, because MDUF regenerates UserForms into a TargetWorkbook as specified in the FormSpec worksheet, MDUF also suffers from the absence of the LoadPicture method. MDUF cannot pick up a picture file from a file server and therefore MDUF cannot splice pictures into the TargetWorkbook. VBA for Office for Mac doesn’t provide that capability.
Fortunately, there is a partial workaround. MDUF can support systems that contain UserForms that include pictures such as your company logo or the logos of your clients. MDUF supports only pictures that exist in the workbooks you deliver to your clients. All pictures that appear in MDUF UserForms must be known in advance, so they can be installed in your delivered system before you deliver it to your users.
The pictures need not be stored in the places your users see them, but they must reside in some UserForm of the delivered workbook. MDUF doesn’t support MouseIcon properties in this release.
Concept of operations for pictures on MDUF-generated UserForms
Although LoadPicture isn’t available for VBA for Office for Mac, VBA for Office for Mac does support setting the Picture property of one control or UserForm to the value of the Picture property of another control or UserForm. That’s the key to MDUF’s generating UserForms that contain Picture objects.
The method MDUF uses is as follows. Any picture one of your controls or UserForms needs at runtime must exist in that UserForm’s workbook on at least one other control or UserForm. For purposes of this description, call that hosting UserForm frmPictureLibrary. This “picture library” must be created in a version of Office that supports the Designer. Office for Windows or Office for Mac 2011 will suffice. After it’s created, frmPictureLibrary must be exported from the workbook in which it resides (the “PictureLibraryWorkbook”) and then imported into the workbook that needs its pictures. UserForms that need pictures from the picture library can then retrieve them from frmPictureLibrary.
Suppose one of your UserForms is an error notification—call it frmAlert—that needs a picture of your system’s logo. You have previously arranged for your logo to be stored in the picture library. Then the UserForm frmAlert executes the picture retrieval by means of a line in the UserForm_Activate event handler of the UserForm frmAlert, as shown below:
Set imgLogo.Picture = frmPictureLibrary.imgLogo.PictureThis action is required because the value of the Picture property when set this way in Office for Mac is volatile—its value isn’t saved when the workbook is saved. But since frmPictureLibrary was created in Designer, its pictures aren’t volatile—they are saved when the file is saved. So your system will meet user expectations. Pictures will be displayed just as if the LoadPicture method existed.
The only piece of the puzzle not described so far is: How do you maintain frmPictureLibrary in the workbooks into which you imported it? At this writing, MDUF doesn’t offer much support for ongoing maintenance of picture libraries. But future releases will provide automation assistance for creating, exporting, and importing picture libraries, and picture library versions will be tagged with release IDs. Watch this space.
Working with picture libraries
This section contains suggestions for managing and structuring picture libraries. They’re merely suggestions. MDUF imposes no constraints of its own on your approach to managing your picture libraries.
Naming your picture library
If you support only one MDUF project, what you name your picture library is of little consequence. The only constraints come from Office. The name of the picture library must be a legal name for a UserForm and it must be unique within the VBA project.
But if you support more than one MDUF Project, there is an advantage to using standard names for picture libraries. The advantage is clear when you examine the code for UserForm_Activate methods (see above). When you use a standard name for your picture libraries, the picture clauses of the UserForm_Activate methods are identical from one MDUF Project to the next. This makes them easier to understand and develop.
Factorize your picture libraries
If some pictures are common to more than one MDUF Project, segregate them from pictures that are specific to a single project. When this is done, in the simplest case, you’ll have two picture libraries in some projects. For example, for the Wxyz Project, you would have, say, frmCommonPicLib and frmWxyzPicLib. This factorization is useful when frmWxyzPicLib needs to use names for images that other projects also need to use, but when those images differ.
Import your picture libraries
To import a picture library into a workbook, use the Import command in the Visual Basic Editor.
For new development, the workbooks that could conceivably require picture libraries are the TargetWorkbook, the TestWorkbook, and the PreviewWorkbook. In the case of new development, the PreviewWorkbook and the TestWorkbook are likely copies of the TargetWorkbook.
For conversion projects, the situation is a bit more complex. If you construct the TestWorkbook, the PreviewWorkbook, or the TargetWorkbook by copying the LegacyWorkbook, then whenever you refresh them, you must also import the picture libraries. MDUF has a ribbon command that performs this work on the active workbook, but there is a better way: import the picture libraries into the LegacyWorkbook. That eliminates the need to import them into the TargetWorkbook, the TestWorkbook, and the PreviewWorkbook, because they’re just copies of the LegacyWorkbook. This approach requires some care, because it can interfere with current users of the LegacyWorkbook. But with care, it is the preferable approach. The CaptureWorkbook doesn’t need the picture libraries.
Picture format is limited
Any pictures you do include in your UserForms must be in 24-bit BMP format.
Specifying Picture or MouseIcon properties of a control or UserForm
| MouseIcon | Picture | |
| CheckBox | ✔ | ✔ |
| ComboBox | ✔ | |
| CommandButton | ✔ | ✔ |
| Frame | ✔ | ✔ |
| Image | ✔ | ✔ |
| Label | ✔ | ✔ |
| ListBox | ✔ | |
| MultiPage | ||
| Page | ||
| OptionButton | ✔ | ✔ |
| RefEdit | ||
| ScrollBar | ✔ | |
| SpinButton | ✔ | |
| TabStrip | ✔ | |
| TextBox | ✔ | |
| ToggleButton | ✔ | ✔ |
| UserForm | ✔ | ✔ |
The table to the right indicates which controls can have MouseIcons or Pictures. If you want to assign an image of some kind to a MouseIcon or Picture property of a control, follow these steps.
- Ensure that the image is available in a picture library. A picture library is just a UserForm containing image controls.
- Ensure that the picture library is loaded into the TargetWorkbook, the PreviewWorkbook, and the TestWorkbook. For Legacy conversion projects, ensure that the picture library is also loaded into the CaptureWorkbook.
- Declare the names of all necessary picture libraries by entering their names as a comma-separated string in the cell Project!PictureLibraries of the Project workbook.
- Select any cell in the record of the control that will be displaying the MouseIcon or Picture. Then invoke the Properties Editor for that control by using the MDUF ribbon command
- In the Properties Editor, Select the TextBox captioned as Picture (for a Picture) or as MouseIcon (for a MouseIcon).
- Immediately to the right of that TextBox is a CommandButton captioned as “Choose…” Click it.
- MDUF exposes a UserForm with two ListBoxes. To the left is a list of Picture Libraries that have been imported into the Project Workbook. If you don’t see the library you expect, click Cancel, import the library, and begin again. If necessary, append the library name to the contents of Project!PictureLibraries. If you do see the library in the left ListBox, select it.
- After you select the library, MDUF fills the right-hand ListBox with the names of the images that are available. Select the one you want and click OK. MDUF then enters the information into the TextBox you selected. For example, frmPicLib.imgLogo.