In MDUF, a UserForm is represented on a worksheet of an Excel workbook. The worksheet is a FormSpec worksheet. That workbook can hold multiple FormSpec worksheets representing UserForms that are typically members of the same project. We call the workbook the “Project workbook” for that project.

• Introduction to the Project workbook
• Indicating the version identifier of your Project
• New FormSpec worksheets and new templates
• Specifying target workbooks
• Deleting UserForms from target workbooks
• Deleting other UserForms
• Protecting yourself from making mistakes
• A wrinkle in UserForm generation

Introduction to the Project workbook

To generate a UserForm using MDUF, you begin by defining it on a worksheet (called a “FormSpec” or a “FormSpec worksheet”) of an MDUF Project workbook. We show you how to do this in detail in “UserForm layout: the basics.” Our purpose in mentioning it here is only to describe the general framework MDUF provides.

An MDUF Project workbook can hold specifications for many UserForms—many FormSpec worksheets. Together, the UserForm specifications in the Project workbook comprise a project. Keeping related UserForm specifications together in the same Project workbook simplifies the task of making related UserForm specifications consistent with each other.

As delivered to your end user, a large project might have several different UserForms residing in one or more Excel workbooks. Keeping the MDUF UserForm specifications in the same MDUF Project workbook makes it easy to regenerate the UserForms when you make a change in your system’s appearance, or when you add a feature, or when Microsoft makes a change that requires an adjustment in the design of your UserForms, or when Microsoft offers a new capability that you want to exploit. When one of these events occurs, and after you make needed adjustments to the specifications of your UserForms in the Project workbook, all you need do is issue a command to generate the UserForms for your project.

It’s possible to keep multiple projects in a single Project workbook, but doing so compromises the capability described above. It’s best to keep the specifications of only closely related UserForms in the same Project workbook. 

Indicating the version identifier of your Project

Some MDUF users have multiple versions of MDUF Project workbooks. The versioning in question here isn’t the version identifier of a particular project. Rather, it’s the version identifier of the skeleton Project workbook—the blank from which a project can be made. You would do this, for example, if you have one design scheme for a particular client, and another scheme for another client.

To indicate the version of a project, enter the version identifier in the cell Project!ProjectVersion of the MDUF Project workbook.

This facility supports macros you can write to detect all Project workbooks that descend from a particular Project workbook. The macro can then report all descendants or perform operations you specify. 

New FormSpec worksheets and new templates

Two commands in the “This Project” group of the MDUF ribbon provide convenient ways of creating new FormSpec worksheets and new Template worksheets.

This Project>New>FormSpec…

The MDUF ribbon command This Project>New>FormSpec… creates a worksheet suitable for specifying a new UserForm in the active Project workbook. After you invoke this command, Excel presents the mDialogBox titled “Choose a FormSpec template”, which gives you an opportunity to select which UserForm or Template you want to copy to make the new UserForm. The mDialogBox also asks you to supply a name for your new FormSpec.

MDUF is distributed with a Project workbook that contains a template called “T.Generic”, which you can use as a template if you haven’t created a template for your applications yet. You can also copy any existing FormSpec worksheet to create your new FormSpec worksheet.

After creating the new FormSpec worksheet, MDUF suggests that you change the name of the worksheet, and change the contents of the cell named Form.IsTemplate from TRUE to FALSE. By convention in MDUF, the first two characters of the names of worksheets that are templates are “T.”.

This Project>New>Template…

This command provides a small convenience. In truth, the command would serve well for creating new FormSpec templates. Absent the command , to create a new template, you would create a new FormSpec worksheet and then enter the name of the new template in the cell TemplateName. You would then enter TRUE in the cell Form.IsTemplate. Finally you would edit the name of the sheet tab, and that would be that.

This command does some of this bookkeeping for you. It invokes an mDialogBox that offers you an opportunity to choose the template or FormSpec worksheet from which you want to create the new template. It also asks you for a name for the new template (that is, the part after the “T.” on the sheet tab). Then MDUF appends a “/” and that name after the template name in the cell named TemplateName on the sheet you created. This helps you keep track of the origins of your templates in case changes are needed later. 

Specifying target workbooks

When you generate a UserForm from a FormSpec worksheet, MDUF creates an internal Excel representation of the UserForm and inserts it into the target workbook. A cell named Targets on the FormSpec worksheet holds the specification for the target workbook or workbooks. If that cell is empty, then MDUF uses the information in the cell named Targets on the Project worksheet. So when considering the target of an MDUF UserForm, the concept of EffectiveTarget is useful. The EffectiveTarget of an MDUF UserForm is the contents of the cell named Targets on the FormSpec worksheet, or, if that cell is empty, then the EffectiveTarget is specified by the contents of the cell named Targets on the Project worksheet.

Specifying a target workbook on the FormSpec worksheet

To tell MDUF that a particular FormSpec worksheet applies to a particular target workbook, there are three cases of interest.

• If the target workbook resides in the same folder as the Project workbook, you can enter the target workbook’s filename in the cell named Targets. This is the usual (and recommended) case. Example: Bluebird.xlsm.
• If the target workbook resides elsewhere, then you enter the target workbook’s full path relative to the Project workbook. For example, if there is a folder Cardinal residing in the same folder as the Project workbook, and if the target workbook is Bluebird.xlsm residing in the folder Cardinal, then the contents of the cell Targets would be Cardinal/Bluebird.xlsm.
• If the target workbook is located somewhere else, and the relative path of the target is ugly and complicated, it might be tempting to create a MacOS Finder alias to simplify the relative path. But Excel VBA can’t follow paths that include Finder aliases. That’s why you must either use a complete path (in Excel VBA terms, the workbook’s FullName), or the ugly relative path. For an example of an “ugly” relative path, suppose the Project workbook resides inside a folder SeaGull, whose parent is a peer of a folder Penguin that contains the target file Albatross.xlsm. Then the ugly relative path to the target would be ../../Penguin/Albatross.xlsm.

The EffectiveTarget can contain more than one file specification. You might do this if the UserForm is needed in more than one workbook. In that case, use a single comma to separate the target paths.

Specifying a target workbook on the Project worksheet

If the cell named Targets on the FormSpec worksheet is empty, MDUF uses the information found in the cell named Targets on the Project worksheet. All of the above conventions apply to Project!Targets.

When all FormSpec worksheets in the project have the same target, specifying the target workbook on the Project worksheet is the preferred method for specifying the target.

If the cell named Targets on the FormSpec worksheet is not empty, then MDUF ignores Project!Targets when generating the UserForm defined on that FormSpec worksheet. 

Deleting UserForms from target workbooks

The MDUF ribbon command This FormSpec>Delete>Target UserForms… deletes all MDUF-generated UserForms from those target workbooks you specify. Because there is no undo, use this command with care.

This command serves the needs of one particular occasion. When a target workbook, or a collection of target workbooks, contains many UserForms, from time to time several of those UserForms require adjustment, updating, or correction. In this situation, the usual approach for manually created UserForms is to manually edit them. But with MDUF, it’s easier and safer to edit the FormSpec worksheets, and then just regenerate all UserForms those workbooks contain, including the UserForms that don’t actually need correction. If the replacement UserForms have the same names as the UserForms they replace, the regeneration approach works well, because regeneration just overwrites the old UserForms.

But if one or more of the UserForms undergoes a name change, or if one or more of them has been removed from the project design or targeted to another workbook, their old versions remain in the target workbook even after regeneration. By deleting all UserForms first, and then regenerating their replacements, we get the result we want. This command makes this process convenient.

Caution: this command deletes any UserForms from the target workbooks, but only if they were generated by MDUF. It does not delete UserForms that were created in some other way, unless they meet MDUF’s conditions. MDUF identifies its own UserForms by examining the first line of text in the form’s code pane. If that first line begins with the string below, then MDUF assumes that the UserForm was created by MDUF.

<MDUFHeader Comment=

Tip: Before you change the target of a FormSpec worksheet or the target of a project, and you want to remove previously generated UserForms from the former targets, invoke the ribbon command This FormSpec>Delete>Target UserForms…. If you don’t, the previously generated UserForms will remain in place unless they are overwritten by the regeneration event.

Caution: If you’re relying on the code pane of a UserForm to store any part of the code associated with that form, be sure to copy it to a safe place before deleting that form. MDUF provides a means for doing so. See “Back-end code” for more information. 

Deleting other UserForms

In the MDUF ribbon tab is a command This Workbook>Delete UserForms…. This command examines the active workbook. If the active workbook has no UserForms, it posts a notice to that effect, and you can dismiss it. If it finds UserForms, it presents the mDialogObject titled “Delete UserForms”. This dialog displays a ListBox containing the names of all UserForms in the active workbook. After you choose the items you want to delete, and click OK, MDUF deletes them. It’s a convenience for deleting UserForms not generated by MDUF. There is no Undo. Use this command when you need to remove selected UserForms from existing projects after you’ve created their MDUF replacements. 

Protecting yourself from making mistakes

Excel’s Undo command (on the Edit menu in Excel for Mac) can Undo many actions you take yourself, but it can’t undo actions initiated by VBA code. It’s possible to write undo handlers for VBA actions, but MDUF hasn’t yet exploited that capability. Consequently, when you issue a command in MDUF, there is only a small chance that you can undo it. And since many of the MDUF commands do large amounts of work, using an MDUF ribbon command mistakenly does expose you to a small risk of doing large amounts of irreversible damage.

MacOS does come equipped with an automatic backup capability called Time Machine. It is superb. If you work in an environment that permits its use, Time Machine is the recommended tool for managing the risk of doing damage inadvertently using MDUF. If you already use Time Machine, of if you intend to, read no further in this section. But if you can’t use Time Machine, there are some alternatives.

If you’re using Microsoft 365, its applications (Excel, Word, PowerPoint and so on) have an AutoSave feature that saves your work frequently and automatically. You can then backtrack to an earlier version if you’ve done something you regret. But AutoSave is disabled unless the file is stored on OneDrive, OneDrive for Business, or SharePoint Online. Unfortunately, many Mac users don’t use these capabilities. They prefer iCloud for shared storage. AutoSave doesn’t support iCloud. There are other AutoSave restrictions as well. See What is AutoSave? at support.Microsoft.com.

That’s why it’s more important than usual to take steps to protect yourself from yourself. Three measures you can take to limit damage are:

• Save your work often
• Familiarize yourself with the MDUF Reload command
• Familiarize yourself with the MDUF Snapshot command

Save your work often

This is always good advice. But when you’re working with very powerful commands, it’s even more important.

Familiarize yourself with the MDUF Reload command

The MDUF ribbon tab has a command This Workbook>Reload. It does only one thing: it reloads the active workbook. If you’ve been saving your work often, and if you make a hugely damaging mistake, reload the workbook. That restores the state of the workbook to whatever it was at the time you last saved it. You could lose some work, but the damage is gone too.

Reloading is also useful when you want to make a change just to see what it looks like. You can intentionally choose not to save the file until you like what you see. You make some changes, view the results, and if they aren’t what you want, you can reload the file. If the changes you’ve made are acceptable, then you save the file.

Naturally, this approach is unsafe if you’re relying on AutoSave, because Excel doesn’t know that your latest changes are just experimental. Excel will overwrite the “real” version with the experimental version. Moreover, if another workbook references the workbook you’re reloading, Excel will object. But typically this situation won’t arise if the workbook in question is an MDUF Project workbook.

Familiarize yourself with the MDUF Snapshot command

The MDUF ribbon command This Project>Snapshot does only one thing: it saves the active workbook and copies it as a sibling, to a name it generates. It generates the name by appending an integer to the base name of the active workbook. If the active workbook is Foo.xlsm, then the snapshot workbook is in the same folder as Foo.xlsm, but its name is Foo-1.xlsm. If Foo-<N>.xlsm (for some integer value of <N>) exists in the same folder as Foo.xlsm, and it’s the highest numbered snapshot workbook, the next snapshot workbook is Foo-<N+1>.xlsm.

Making snapshots is helpful because you can control the timing of their production. Even though your backup software also makes snapshots, this snapshot facility is a little lighter weight, because it’s just one file. The light weight makes timing of the snapshots more flexible.

Of course, this wouldn’t be much of an advantage if AutoSave worked with local storage. But since it doesn’t, if the file you’re snapshotting is in local storage, snapshotting is a helpful aid. Snapshotting also works if the file is stored in a DropBox folder.

One very important limitation: I haven’t been able to make snapshotting work for project workbooks located in iCloud. VBA SaveCopyAs is unable to create a copy. Not sure why. 

A wrinkle in UserForm generation

There is a known problem in Excel relating to renaming a newly created UserForm. Unfortunately, the workaround creates a complication for the MDUF user. MDUF does its best to make this workaround invisible to the MDUF user, but I haven’t yet found a way to make it completely invisible. This section describes what you need to know and in what circumstances you’ll be affected.

When this problem arises

This problem arises in only one circumstance, which unfortunately is common. It arises when you’re generating UserForms into a target workbook, and that workbook already has previous versions of the UserForms you’re generating, and the code-behind for the existing UserForms is meant to be inserted into the newly generated UserForms.

For example, suppose you have a UserForm already placed in an Excel workbook. And suppose you have an MDUF Project workbook that contains the information that was used to generate that UserForm. Finally suppose you have made some revisions in the FormSpec worksheet for the UserForm and you want to generate the UserForm to deploy those revisions. You intend for the code-behind of the existing UserForm to be carried along to the newly generated UserForm.

The above is one scenario that would encounter this wrinkle. There are other scenarios as well. Fortunately, you as an MDUF user don’t need to know how to recognize the scenarios that encounter the wrinkle. MDUF does that for you. When it finds one, it queries you, as described below.

What the workaround is

MDUF uses a workaround to avoid a problem in Excel. That problem occurs when Visual Basic code creates a new UserForm and then tries to set its name property. The workaround is to save the UserForm’s file before setting the name property. It is that saving operation that creates problems for the scenario above.