In Excel’s GUFEF you specify positions and sizes of controls numerically. That works well, and MDUF supports that method of specification. But MDUF also support relative positioning and sizing. That is, for example, you can declare that “this control’s left edge should be 12 twips from the right edge of that other control.” This ability makes maintenance much easier.

• The UserForm coordinate system
• Inserting new blank control records
• Deleting control records
• Naming controls
• Advantages of naming conventions
• Registering controls
• Renaming controls
• Excel names and macros for setting control positions
• Defined layout constants
• Defining your own constants
• Turning off Excel’s “unlocked formula” indicator
• Cautions regarding relative positioning
• Constructing control position formulas with Formula Maker
• Positioning the UserForm when it’s displayed
• The edit-generate-preview-test-edit cycle
• A word about Frame controls

The UserForm coordinate system

The coordinate system for location of controls within a UserForm is the standard two-dimensional screen coordinate system. The origin (x,y) = (0,0) is the top left corner of the UserForm. The x-axis is horizontal and the x coordinate increases to the right. The y-axis is vertical and the y coordinate increases downward. All coordinates are in units of twips (1/1440 inch).

In MDUF, you set a control’s position by entering formulas into the cells corresponding to its Left and Top. So its upper left corner is located at (Left, Top). The formula in the control’s Left cell and the formula in the control’s Top cell can be any Excel formulas that evaluate to numbers. For example, if the LabelA control that describes the ComboBoxA control should have the same Left as ComboBoxA, and if ComboBoxA’s Left cell is N12, then the formula for LabelA’s Left would be =N12. If you want LabelA’s Left to be 3 twips to the left of ComboBoxA’s Left, then LabelA’s Left formula would be =N12-3.

However, explicit cell references like this, although acceptable, can be difficult to maintain. Errors can result, and that can lead to time wasted making small adjustments to UserForm layouts. For this reason, MDUF includes some Excel names and macros to make these specifications more human-readable. We’ll discuss these names and macros in the section, “Excel names and macros for setting control positions,” but let’s examine some other machinery first. 

Inserting new blank control records

Two commands are available for adding new blank records to the controls specified on a FormSpec worksheet. They are found in the Insert menu of the Group “This FormSpec” of the MDUF tab of the Excel ribbon.

This FormSpec>Insert>One Control…

The MDUF ribbon command presents a UserForm in which you can select one or more of the control types for which you want to insert records. MDUF then inserts one blank record for each of the control types you selected. The inserted blank records appear at the bottom of each record range.

This FormSpec>Insert>Multiple Controls

The MDUF ribbon command  works a little differently. Suppose you want to add records to the CommandButton records. Before you invoke the command, select a range with a number of rows equal to the number of records you would like to add. Arrange for the active cell of the selection to lie within the CommandButton records, or arrange to have the active cell of the selection lie just below the CommandButton records. Next, invoke this ribbon command. The inserted rows then appear in or among the CommandButton records.

To insert just one new control record, ensure that the selection contains just one row.

If the selection is compound, that is, if it consists of more than one area, MDUF ignores all but the last area, which is the area containing the active cell.

One other command for inserting controls is . It’s described in “Managing the Pages of a MultiPage”. 

Deleting control records

To delete a control record, select any cell or cells within the record you want to delete. Then invoke the MDUF ribbon command This FormSpec>Delete>Selected Controls. You need not select all the cells of the target record. If the selection includes cells from multiple records, MDUF deletes all records intersecting with the selection. The selection can be compound—that is, it can consist of multiple areas.

If the record you’re deleting is the only remaining record of a given control type, then MDUF clears its contents, but doesn’t delete it. 

Naming controls

Many people find a particular convention useful for naming controls. They include a prefix in the control’s name that denotes the type of the control. Personal preferences vary, but one such set of prefixes is shown below.

Control Type	Prefix	Control Type	Prefix	Control Type	Prefix
CheckBox ComboBox CommandButton Frame Form Label	chk ddn cmd fra frm lbl	Image ListBox MultiPage OptionButton Page SpinButton	img lst mpg opt pag spn	ScrollBar TabStrip TextBox ToggleButton	scr tbs txt tgl

Note: Strictly speaking, a Page of a MultiPage isn’t a UserForm control. It can appear only within the MultiPage control. But for purposes of MDUF, it’s treated as a control. When a Page appears on a FormSpec worksheet, MDUF understands that when the UserForm is generated, the Page is to be inserted into the specified MultiPage.

When first creating a FormSpec worksheet, one usually starts with at least a sketch in mind. If you want to use the prefixes above in control names, you can save a bit of trouble in naming controls by entering their names without their prefixes, and then using the MDUF ribbon command This Control>Wrap Text… to insert the prefixes.

Before invoking the command to Wrap Text, select the cells whose contents you want to modify. The Wrap Text command modifies only cells that contain text. It ignores cells that contain formulas, or which are empty, or whose formulas are numbers, or TRUE, or FALSE. 

You can use Wrap Text to prefix abbreviations like the ones in the table, or to append suffixes. A common suffix is a colon, for labels, for example.

After you invoke the command “Wrap Text”, MDUF presents an mDialogBox that contains two TextBoxes. One is for the prefix you want to prepend, and one is for the suffix (if any) that you want to append. Enter what you want, then click OK. MDUF then alters the alterable cells in the selection.

So, for example, if you have eight labels in your UserForm, you can enter the “root” names of the labels in the respective cells of the Label record block of the FormSpec worksheet. After they’re all entered, select the range containing the eight root names, invoke the MDUF ribbon command , enter “lbl” into the Prefix text box of the UserForm called “Wrap Text of Selection”, and click OK.

A second MDUF ribbon command addresses the need to insert a prefix, or alter an existing prefix in the name cell of a control. This need arises when converting legacy UserForms to MDUF, or when converting from one prefix scheme to another. Begin by selecting the name cell of the control you want to modify. The MDUF ribbon command is This Control>Prefix Name…. It causes MDUF to invoke the mDialogBox captioned “Insert Control Abbrev.” Select the abbreviation you need, and click OK. MDUF then prefixes the name of the control with the abbreviation you selected. 

Advantages of naming conventions

Adopting naming conventions for controls can save time and reduce the probability of errors in developing UserForms. The savings are realizable for both UserForms originated in MDUF and for legacy UserForms captured in MDUF.

For example, suppose you have a UserForm that has an OK button named cmdOK in the upper right corner, and a Cancel button named cmdCancel directly below it. The formula that appears in the Top cell of the Cancel button might be something like =Beneath(cmdOK). Since this configuration is fairly common, if you always name the OK button cmdOK, you can reuse the formula in the Top cell of the Cancel button. The reuse might be accomplished by Copy and Paste when you enter the formula, or you might write code to enter the formula. Having naming conventions for commonly used configurations makes this easier. 

Registering controls

MDUF offers two methods for positioning controls on UserForms. In the conventional method, absolute positioning, you enter numeric values into a control’s Left cell and Top cell. Then later, when you generate the UserForm containing that control, MDUF places the control’s left edge and top edge at the positions you specified. But MDUF offers another way to specify position, called relative positioning. With relative positioning you enter formulas in the cells that specify a control’s left edge or top edge. 

For example, if you enter into the cancel button’s Top cell the formula =Beneath{cmdOK}, then MDUF places the cancel button beneath the control called cmdOK. Notice that the argument to Beneath isn’t the name of the control whose name is “cmdOK”. If it were, the formula would have been =Beneath(“cmdOK”). Registering the control “cmdOK” creates the name cmdOK, which refers to a portion of the record for the control. That enables MDUF to compute relative positions more efficiently.

In some instances a control can be specified with mixed positioning. For instance, you can use absolute positioning for the vertical direction, and relative positioning for the horizontal direction.

To support relative positioning formulas, and others like them, you must register the controls. Registration is needed for a given control C only if you intend to (a) position other controls relative to control C, or (b) compute the TabIndex of a control by referencing C. It isn’t necessary to register Pages of MultiPage controls.

Registration is just a fancy word for a simple procedure. When you register a control on a FormSpec worksheet, MDUF defines a range whose name is the control’s name.

To register a control, first ensure that its name is entered into the name cell of its record. If its name is not yet entered, enter it. Then select any cell in its record, and invoke the MDUF ribbon command This Control>Register.

You can register all controls on a FormSpec worksheet by invoking the MDUF ribbon command This FormSpec>Register All. And you can register all controls in a Project workbook by invoking the MDUF ribbon command This Project>Register All.

If you delete or rename a registered control using MDUF ribbon commands, MDUF takes care of registration matters for you. But if you edit the name cell of a control, you need to register the control again. You also ought to unregister the old name. To unregister all obsolete control names in an entire project workbook, invoke the MDUF ribbon command This Project>Unregister All . To unregister all obsolete control names on a FormSpec worksheet, activate that sheet, and then invoke the MDUF ribbon command . 

Renaming controls

From time to time over the course of developing or maintaining a project, you might see a need to change the name of a control. If you’re working in Excel’s GUFEF, the task is straightforward. It has four steps:

• Select the control in Designer
• Locate the control’s name slot in the Properties window. Edit that slot.
• Find any portions of the back-end code that refer to the control by name, and revise them so that they use the control’s new name.
• Find any event handlers whose names include the old name of the control, and rename those handlers so that they use the control’s new name.

In MDUF, the procedure for renaming controls also deals with these tasks, but there is one additional task. As you’ll see when you refer to the section, “Excel names and macros for setting control positions”, some macros used for positioning controls accept as arguments the names of controls. So when you change the name of a control, you must also revise these arguments. You can do this manually, of course, but the MDUF ribbon command This Control>Rename… does most of the work for you. 

To rename a control, select any cell in the control’s record on the FormSpec worksheet. Invoke the MDUF ribbon command This Control>Rename…. MDUF then presents the mDialogBox titled “Rename Control”. It contains a textbox captioned “New name.” It also contains a note that reads, “Renaming control named <OldName>”, where <OldName> is the current name of the control. Verify that <OldName> is the current name of the control you’re renaming. Then enter the new name in the text box and click “OK”.

Renaming Pages of MultiPage controls works the same way as renaming controls.

MDUF then performs all operations required to rename the control, with one exception. It doesn’t revise back-end code. If the FormSpec worksheet contains UserForm code, MDUF checks it for occurrences of the old name. If the old name is found in the UserForm Code Block, MDUF notifies you. If there is no code, or if there is code but it’s free of the old name, MDUF notifies you of that as well. Of course, there can be occurrences of the old name elsewhere in your project’s code. Checking all code manually is only prudent. 

Excel names and macros for setting control positions

Formulas for specifying position can make use of named quantities that enable expressions of relative position:

• Form.Width returns the width of the UserForm, and therefore it is the form’s right-hand edge.
• Form.Height returns the height of the UserForm and therefore it is the form’s bottom edge.
• HCenter(optional mpgName) returns the horizontal coordinate of the point halfway across the UserForm, unless it is supplied the optional argument mpgName. mpgName is the name of an existing MultiPage. If HCenter is supplied with the name of an existing MultiPage, it returns the half-width of the MultiPage.
• VMiddle(optional mpgName) returns the half-height of the UserForm, unless it is supplied the optional argument mpgName. mpgName is the name of an existing MultiPage. If VMiddle is supplied with the name of an existing MultiPage, it returns the half-height of the MultiPage.
• Form.Pad is the inner pad along all edges of the UserForm.
• MyWidth returns the width of the control.
• EffCtrlHeight is the control’s effective height—possibly inherited from the UserForm or Project.
• EffCtrlWidth is the control’s effective width—possibly inherited from the UserForm or Project.
• FlushRight(optional mpgName) returns a number that would be the control’s Left if its right edge is set to the inside pad of the right edge of the UserForm. It is equivalent to Form.Width-MyWidth-MAX(UserForm’s Pad, Control’s margin). When the optional argument mpgName is the name of an existing MultiPage, the control’s Left is set to a value that pushes the control as far to the right edge of the MultiPage as possible, considering the pad and margin requirements.
• FlushLeft() is MAX(UserForm’s Pad, Control’s margin).
• LeftIndent(n) is equivalent to FlushLeft()+n.
• FlushBottom(optional mpgName) is equivalent to setting the control’s Top to Form.Height-MyHeight-MAX(Form.Pad,Ctrl.Margin). When the optional argument is the name of an existing MultiPage, the control’s Top is set to a value that pushes the control as close to the bottom edge of the MultiPage as possible, considering the pad and margin requirements.
• FlushTop() is equivalent to Form.Top+MAX(Form’s Pad, Control’s margin).

For example, to position a control to be as close as possible to the right-hand edge of the UserForm, enter the following formula into its Left cell: =FlushRight() .

Also included are the following 12 macros. They facilitate placement of controls relative to other controls. For example, the formula =SameTopAs(“lblListBox1”), or, equivalently, =SameTopAs(lblListBox1), in the “Top” cell of the ListBox1 control would make the top of ListBox1 the same as the top of lblListBox1 (the label of ListBox1).

The first nine of these macros accept one argument that is the name of a control, or a cell that contains the name of the control, or any formula that evaluates to the name of a control, or the registration name of the control. (The registration name of the control is the sheet-level defined name that has the same screen representation as the name of the control.)

Then there is one macro, LeftIndent, that accepts a single argument that evaluates to a number. It determines the distance of the left indent of the dependent control, relative to the hosting UserForm or Page.

Finally, there are two macros that accept two arguments—the name of a control, and a distance in twips. They return a number of twips offset from one of the edges of a control.

These macros invoke internally a routine that restricts the independent control (called ctrlName in the descriptions that follow) in two ways. The first restriction is that the independent control must exist. That is, when you first enter the formula to compute, say, the left coordinate of a control relative to a second control, that second control must already exist. If it doesn’t exist (if you haven’t defined it yet), then an error results.

The second restriction is that the dependent control (the control whose position is being calculated) and the independent control must reside either (a) on the UserForm itself, or (b) on the same page of a MultiPage control. You can defeat this restriction temporarily by changing the page of a control after you enter the formulas that calculate its position. But the UserForm generator performs a last-minute verification to ensure that the positioning macros are in compliance. So it isn’t possible to generate a UserForm that circumvents this restriction.

SameBottomAs(ctrlName)

This worksheet function computes the Top of the control so that the control has the same bottom coordinate as ctrlName.

SameTopAs(ctrlName)

This worksheet function sets the Top of the control to be the Top coordinate of ctrlName.

SameLeftAs(ctrlName)

This worksheet function sets the Left of the control to the Left coordinate of ctrlName.

SameRightAs(ctrlName)

This worksheet function computes the Left of the control so that the control has the same Right coordinate as ctrlName.

Beneath(ctrlName)

This worksheet function computes the Top of the control so that the top of the control is Ctrl.Margin below the bottom edge of ctrlName.

Above(ctrlName)

This worksheet function computes the Top of the control so that the bottom of the control is Ctrl.Margin above the top edge of ctrlName. 

ToRightOf(ctrlName)

This worksheet function computes the Left of the control so that the left-hand edge of the control is Ctrl.Margin to the right of the right-hand edge of ctrlName.

ToLeftOf(ctrlName)

With a ctrlName argument, this worksheet function computes the Left of the control so that the right-hand edge of the control is Ctrl.Margin to the left of the left-hand edge of ctrlName.

CenteredRelativeTo(ctrlName)

With a ctrlName argument, this worksheet function computes the Left of the control so that the control is centered relative to ctrlName. That is, their centerlines have the same x-coordinate.

LeftIndent(n)

This is equivalent to FlushLeft()+n, where n is measured in twips.

RightOffset(ctrlName, twips)

With a ctrlName argument, this worksheet function computes the Left of the control so that the left-hand edge of the control is twips to the right of the left-hand edge of ctrlName. This is useful, for example, for placing option buttons within a frame.

BeneathOffset(ctrlName, twips)

With a ctrlName argument, this worksheet function computes the Top of the control so that the top edge of the control is twips below the top edge of ctrlName. This is useful, for example, for placing option buttons within a frame. 

Defined layout constants

MDUF implements four additive constants for use in UserForm layout. They’re all defined on the worksheet Gnames, and you can modify all of them.

Defining your own constants

You might discover a need for constants for use in layout of UserForms. For example, one common graphical idiom consists of a line of text (a Label) above a ListBox. The text might serve, for example, as a prompt. It might read: “Select an employee name:” And a ListBox of employee names would appear beneath that line of text.

In some cases, setting the Left coordinate of the ListBox to be 10 twips (or so) greater than the Left coordinate of the line of text might serve as a visual cue that the Label refers to the ListBox. In such cases, having a name for that constant (10 twips) can reduce maintenance costs and make the MDUF representation of the UserForms of the project more consistent and more readable.

To define constants such as this one, use the worksheet called Gnames in the project workbook. (“GNames” stands for “Global Names”) In fact, we’ve defined that particular constant already. Its name is IndentUnderPrompt, and its scope is the workbook. You can use the worksheet Gnames to define other constants as you see fit. 

Caution: To avoid redefining an internal MDUF name, verify that the name you choose for your project isn’t already in use. Note: some MDUF names are hidden. But all hidden MDUF names end with “.h” (dot H). If the name you choose isn’t visible already, and doesn’t end in “.h” you should be OK. 

Turning off Excel’s “unlocked formula” indicator

Excel, ever helpful, indicates any cells that contain formulas but which are not locked. It does so with a small green triangle inside the cell. The presumption is, apparently, that formulas always need to be protected. But as explained in the previous section, in MDUF, users need to be able to edit formulas in cells that compute positions, for example.

So it’s nice to be able to remove some visual clutter by suppressing the unlocked-formula indicator. In MDUF, the keyboard shortcut Ctrl+Shift+E toggles the display of the unlocked-formula indicator. 

Cautions regarding relative positioning

Keep in mind two cautions regarding use of the relative positioning macros above. First, every control you define in a given FormSpec worksheet must have a unique name. If two controls on the same FormSpec worksheet have identical names, the results are unpredictable and almost certainly unwelcome. Using the control-type prefixes displayed in the section “Naming controls” can help ensure that names don’t conflict. For example, if a ListBox has a name lstEmployee, its label can be lblEmployee. The two are then clearly related but their names don’t conflict.

Second, when using the positioning macros, it’s always possible to unintentionally arrange things so that circular reference errors result. For example, if control A computes its Top as TopEdgeOf “B” and control B computes its Top as TopEdgeOf “A”, a circular reference results. Circularities this simple (only two nodes) are unlikely, because they’re so obvious. But longer loops are less noticeable, and therefore more likely to occur than are the short loops.

To reduce the chances of creating circular reference errors, you can adopt conventions with respect to relative positioning of controls. Some examples of such conventions are below.

• When a label describes a control, the control it describes is positioned relative to the label, rather than positioning the label relative to the control it describes.
• Relative vertical positions always refer to controls that are higher than (that have lesser y-coordinates than) the control you’re positioning.
• Relative horizontal positions always refer to controls that are to the left of (that have lesser x-coordinates than) the control you’re positioning.

These are only examples of possible conventions. You’re free to devise your own. If you do adopt a set of conventions like the one above, it’s sometimes necessary to rearrange the vertical order of control records. To exchange the contents of two rows, select a cell in each of the two rows and then invoke the MDUF ribbon command [mribanch path=”This|FormSpec>This|Form>Swap|These”]. MDUF then exchanges the positions of the records that own the cells you selected. There is one restriction. The records exchanged must no have any external direct dependents. If either of the records has an external direct dependent, don’t try to swap them manually. Instead, remove the dependency, perform the swap, and then restore the dependency.

There’s nothing special about the relative positioning macros that makes circular reference errors more likely. Circularities are always possible even when you use explicit cell references. We mention it here only as a reminder. 

Constructing control position formulas with Formula Maker

The MDUF ribbon command This Control>Enter Formula… is a convenience for entering relative position formulas for controls. It causes MDUF to display the mDialogBox titled “FormulaMaker”. For fast typists, this command doesn’t save much time, if any. Saving time isn’t what motivated the development of this command. The advantage of this command is that you need not learn or remember or search for the names of the MDUF relative positioning macros or the names of your controls. Formula Maker knows the names of all relative positioning macros, and all of the controls on the ActiveSheet.

So, for example, the Formula Maker can construct a formula such as =Beneath(cmdOK) with just three clicks. You click “Beneath”, then “cmdOK”, and then “OK”.

There is one additional feature in the Formula Maker UserForm. It enables you to append a constant term to the relative positioning function you selected. The constant term can be applied with any one of the four arithmetic operations: +, -, *, or /. You choose the operation by clicking the operation toggle button repeatedly until you reach the desired operation. Then choose the constant from the ComboBox to the right of the operation toggle button. The constant can be any of the predefined MDUF layout constants, or 0, 1, or 2, or any other entity you enter, provided it has a numeric value.

If you don’t need this appended constant term, just choose a blank for the operator. 

Positioning the UserForm when it’s displayed

To position the UserForm when it first appears, you can set the Left and Top of the UserForm in its UserForm_Activate method. If you generate the UserForm with CodeSource equal to Code Generator, MDUF can do this for you. Use the Left and Top cells of the Form record on the FormSpec worksheet to instruct the code generator as to your desired initial position for the UserForm. MDUF ignores these cells unless CodeSource is set to Code Generator. 

The edit-generate-preview-test-edit cycle

Laying out a UserForm for the first time entails a bit of guesswork. Unlike the approach we use when Designer is available, in other versions of Excel, we can’t be entirely certain about sizes and positions of controls. For example, we don’t know how wide a label control must be, because we can’t actually see its caption. Relative positioning isn’t difficult, though, if we use the positioning functions.

In any case, we usually traverse a cycle:

• Edit the FormSpec worksheet
• Generate the UserForm.
• Preview the result.
• Test it.
• Edit and repeat.

MDUF provides shortcut commands for generating and previewing the UserForm specified on the active FormSpec worksheet. If you intend to traverse this cycle, begin by telling MDUF which UserForm you’re working on. Activate the FormSpec worksheet of the UserForm you’re working on, and then invoke the MDUF ribbon command This FormSpec>Latest>Set Latest to declare that the active FormSpec worksheet represents the “Latest” form. Then, after you make edits, the command This FormSpec>Latest>Generate Latest generates the UserForm for the active sheet. The command This FormSpec>Latest>Preview Latest UserForm… previews the UserForm of the active sheet. And the command This FormSpec>Latest>Generate and Preview Latest UserForm… generates the latest UserForm and previews it.

If your work is interrupted, and you want to re-enter the edit-generate-preview-test-edit cycle, you might want to load the target workbook or workbooks. For convenience, use the MDUF ribbon command This. MDUF then presents the Choose Targets mDialogBox. Chose the targets you want to load and then click OK. 

A word about Frame controls

Although earlier versions of Office for Mac—specifically Office 2011—did support Frame controls defined using the Designer, we found no evidence that support for Frames defined by means of VBA was working correctly. That problem has persisted into Office 365.

The effect of the issue is that Frames defined by means of VBA are always frontmost in the UserForm. They therefore obstruct the user’s view of their contents. In addition, because the ZOrder method doesn’t appear to work in Mac versions of Office, there is no workaround possible based on ZOrder. There are reports of various hacks pushing Frames back to the rearmost position in ZOrder, but we have found nothing that works consistently. At this writing, our recommendation is to avoid using Frames in MDUF. The recommended workaround has two steps.

Create a label

Create a label to hold the Frame’s caption, and place it where you would have placed the Frame.

Install an invisible Frame

If you must have a Frame, set its Visible property to False. This step is recommended for Capture projects. In fact, in the current MDUF release, Captured Frame controls always have the Visible property set to False. If you want a captured Frame control to be visible, you can make this alteration manually using the Properties Editor, or by means of the Script command set-property.