A legacy UserForm is any UserForm created in Designer in some version of Microsoft Office or Microsoft 365 other than those that run on modern versions of MacOS. To maintain such a UserForm for the future, you can capture it in MDUF on your version of Microsoft 365 for Mac. When enhancements are needed, you can revise the MDUF representation of the UserForm, and then regenerate it to obtain a revised, fully functional version of the UserForm. There is no need to use any other version of Microsoft 365 to do ongoing maintenance.
Introduction to legacy UserForms
In MDUF, a legacy UserForm is a UserForm that wasn’t created in MDUF. Most legacy UserForms were created in Excel’s GUFEF. Because MDUF users want to work only in Excel for Mac on a modern version of MacOS (Catalina or later), they don’t have convenient access to Excel’s GUFEF. To use MDUF for existing projects, they need to migrate their legacy UserForms into MDUF. Because that migration could be laborious and error-prone if carried out manually, MDUF has a UserForm capture facility (UCF).
The UCF can create MDUF representations of the UserForms it finds in a given Excel workbook. To capture a UserForm you need a place to store what you capture. That place is an MDUF Project workbook. With respect to capture operations, that Project workbook is called the CaptureWorkbook.
Capture operations can be a bit complicated, but it all begins with configuring the CaptureWorkbook. Then, with the CaptureWorkbook active, you invoke the MDUF Ribbon command This Project>Capture…. MDUF then scans the workbook identified as LegacyWorkbook on the Project worksheet of the CaptureWorkbook.
When the scan finds a UserForm in the LegacyWorkbook, MDUF accumulates its name in a list. Upon completing the scan, MDUF presents the list in an mDialogBox titled “Choose the UserForms”. After you select the names of the UserForms you want to capture, and click OK, MDUF captures the selected UserForms of the LegacyWorkbook as FormSpec worksheets of the CaptureWorkbook.
For each selected UserForm, MDUF uses the UserForm’s name as the name of that new FormSpec worksheet. The UCF then harvests the UserForm’s data and back-end code and stores it all in that FormSpec worksheet. You can capture UserForms from a LegacyWorkbook one at a time, in small groups, or all at once.
After you have the MDUF representation of all legacy UserForms, you’re free to make changes as needed, and then generate the UserForms from the MDUF representation, just as you would with a UserForm you specified originally in MDUF. In this way, the UCF enables a smooth transition from Excel’s GUFEF to MDUF for all existing projects.
That’s a quick summary of basic use of the UCF. There’s much more to it, of course. Read on.
Concept of operations for the UserForm Capture Facility
A more detailed description of the procedure for capturing a legacy UserForm into MDUF has seven phases.
- Preparing legacy UserForms for capture
- Preparing the CaptureWorkbook for legacy UserForm capture
- Capturing UserForms from the LegacyWorkbook. This procedure automatically enters the captured data into the CaptureWorkbook.
- Creating the TestWorkbook, to use as a testbed for UserForms generated from the MDUF representations of the legacy UserForms.
- Making adjustments. Inserting the MDUF-generated captured UserForms into the TestWorkbook and making appropriate adjustments.
- Dealing with captured legacy code
- Revising the captured UserForm to make use of MDUF capabilities
Things rarely go step-by-step, one step to the next. For example, you might find that after capturing the UserForm, a typographical error was present in the LegacyWorkbook. Typos can be more evident in MDUF representation than they are in GUFEF representation. Rather than correct them in the captured UserForm, correcting them in the legacy UserForm is often preferable, because at this stage it’s uncertain that the rest of the legacy UserForm is free of typos. If you fix errors in the captured UserForm, and then later re-capture the legacy UserForm, you lose the correction, because the UCF overwrites your repaired version. So in the early stages, when you find errors in the legacy UserForm, it’s best to correct them in the legacy UserForm and then recapture it.
After you’ve captured a UserForm, its MDUF representation is available in the CaptureWorkbook. You can then generate it to verify that the capture is accurate. If you find that some minor adjustments are needed, you can make them in the MDUF representation in the CaptureWorkbook. But again, such changes are at risk of loss if you ever need to recapture the legacy UserForm.
Back-end code presents some additional complications, but MDUF provides powerful tools that simplify the task of capturing it. See the section, “Dealing with captured legacy code“ for details.
At this point the generated UserForm and the legacy UserForm should look alike and perform alike. If you want to exploit the power of MDUF, you can make changes in the MDUF representation. For example, you can define colors for the project, instead of defining colors control-by-control. Or you can use relative placement of controls. Because these changes reduce maintenance costs, you’re more likely to make these changes if you expect the UserForm or the project to change in the future.