The Event Code block of the FormSpec worksheet holds event handlers for events that are needed during development, such as OK_Click or Cancel_Click.
- Introduction to MDUF event handlers
- Two advantages of providing event code on the FormSpec worksheet
- Commands for revising code blocks
- Initialization code
Introduction to MDUF event handlers
Event handling is a necessity for most UserForms. Perhaps the most common events are click events, in particular, for the OK and Cancel CommandButtons. But there are numerous other events. Event types are so numerous that MDUF makes only a very limited attempt to assist UserForm authors in composing their handlers.
Typically, UserForm authors include in the FormSpec worksheet only the click events for the OK and Cancel CommandButtons. These two events are needed for preliminary review of UserForm layout in the beginning of UserForm development. But after that stage, when you’re using MDUF to devise UserForm responses to events and conditions, you can write the code in the UserForm’s code module, just as you would do if using Excel’s GUFEF. So all you need in the FormSpec worksheet Event Code Block is code for the click events for the OK and Cancel CommandButtons. You can include more if you wish, but the Code Module alternative is probably more convenient.
The recommended structure for code for event handlers in the FormSpec worksheet consists of two parts. The first is a brief outer shell that appears on the FormSpec worksheet. The second is a (potentially) more complex piece that’s invoked by the brief outer shell.
This is a common approach, and it greatly simplifies developing and revising MDUF-generated UserForms. Typically, the second piece can accept arguments, but that isn’t a universal.
Both parts are manually authored. The brief outer shell is stored on the FormSpec worksheet in the Event code block. Authors enter the second piece in the code module of the UserForm, beginning after the UserForm is generated for the first time. See “Back-end code”.
Two advantages of providing event code on the FormSpec worksheet
There is no real need to include event code on the FormSpec worksheet. You always have the option of adding it to the UserForm after MDUF generates the UserForm. But it turns out that including event code on the FormSpec worksheet can be very convenient in two different situations.
Developing the UserForm
Let’s suppose that you’re developing a UserForm using MDUF. The usual way of doing this involves entering data into the FormSpec worksheet, generating the UserForm, running or previewing the UserForm to see what it looks like, and then making adjustments or additions. This cycle is repeated many times as the UserForm develops.
But there’s a hitch. When you run the UserForm, you might need a way to trigger a specific event. For example, you might need to dismiss the UserForm in the same way a user would, and with the same consequences that would occur in use, to continue the edit/examine/revise cycle. Including a cancel button, with its event code, in the FormSpec worksheet provides you with a way to dismiss the UserForm just as the user would.
Reviewing UserForms
In some organizations, UserForms are subject to design or layout reviews to ensure that they meet UX standards, and to ensure that they include all the needed controls and only the needed controls. The reviewers don’t need working UserForms for this purpose. That’s one reason why developers deliver screenshots to the reviewers.
But producing the screenshots is additional effort. And there is a small risk that the screenshots might not reflect the current state of the UserForm at review time. We can save effort, and reduce risk, if the reviewers can inspect the actual UserForm. They don’t need a working UserForm for this purpose, but they do need the ability to dismiss it as a user might.
The FormSpec worksheet event code can provide that capability.
Commands for revising code blocks
Commands for revising this section of the FormSpec worksheet are Insert Code Block…, Delete Code Block, Insert Code Lines, and Delete Code Lines. You can invoke these commands by clicking the respective command buttons that lie directly above the Event Code Block as shown in the screenshot to the right.
The button captioned “Go to UserForm Code” are for use during more advanced stages of development, or when dealing with captured legacy UserForms. See “Dealing with Captured Legacy Code”.
In all four of the following commands, compound selections are reduced to single-area selections. 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.
Insert Code Block…
The behavior of this command is shown in the table below. There is no undo, but, of course, you can delete the event code block you just added.
| If the row of the active cell falls… | …then the added code block is inserted… |
|---|---|
| …within an existing code block E | …directly above the code block E. |
| …between the code blocks E and F, which lies directly below E | …between the code blocks E and F. |
| …above the rows of all code blocks | …above the uppermost code block. |
| …below the rows of all code blocks | …below the lowermost code block. |
Delete Code Block
The behavior of this command is shown in the table below. There is no undo.
| If the row of the active cell falls… | …then the code block to be deleted is… |
|---|---|
| …within an existing code block E | …the code block E. |
| …between the code blocks E and F, which lies directly below E | …the code block F. |
| …above the rows of all code blocks | …the uppermost code block. |
| …below the rows of all code blocks | …the lowermost code block. |
Insert Code Lines
To insert blank lines in an existing code block, make a selection in the code block, and click the command button Insert Code Lines. The number of lines inserted is the number of rows in your selection. The lines are inserted directly above the active cell. There is no undo. To undo what you’ve done, select what you want to delete and click the command button Delete Code Lines.
Delete Code Lines
To delete lines from an existing code block, select the lines you want to delete and click the command button Delete Code Lines. There is no undo. The minimum number of lines required for a code block is 1. To delete a code block altogether select the block in question and click the command button Delete Code Block.
Initialization code
Some UserForms require initialization or activation before being presented to the user. The Activate and the Initialize events serve these purposes well. Write Subs UserForm_Activate and UserForm_Initialize in the UserForm’s code module to implement whatever actions you require.