AX2298

Setting up lock and unlock actions

Using action code processing, you can lock or unlock cells in the sheet. The Lock tag is used to lock cells, and the Unlock tag is used to unlock cells. If sheet protection is used on the sheet, locked cells cannot be edited, and unlocked cells can.

Use of lock and unlock actions requires an ActionCodes tag to be set up in the sheet. Within the ActionCodes control column and control row, you can define copy, lock, and unlock actions. For more information, see Defining the ActionCodes tag.

Creating action tag pairs for lock or unlock actions

To define a lock or unlock action, you create an action tag pair by placing corresponding lock or unlock tags in the ActionCodes control row and control column. The intersection of the action tag pair defines an action cell for processing. For example:

The lock and unlock tags support one optional parameter, to define a target size for the lock or unlock operation. Within an action tag pair, one tag can have the optional parameter, and the other tag must be just [Lock] or just [Unlock].

In this example, the lock tag with the size parameter is placed in the ActionCodes control row, and the corresponding "simple" lock tag is placed in the ActionCodes control column. You can reverse the placement of the tags if desired. As long as the action tag pair is valid, it doesn't matter where each tag is placed. If you are not using the optional parameter, then the lock and unlock pairs are exactly the same.

If you want the lock or unlock operation to apply when inserting or overwriting calc methods, the calc method itself must be saved with the necessary action tag for the control column. That way, when the calc method is brought into the sheet, the control column will contain an action tag, and a lock or unlock operation can be applied to the calc method.

If an action tag pair is invalid, or if a tag contains invalid syntax, then the action code processing is stopped before any actions are taken and an error message is displayed (or logged, if processing as part of a utility). For lock and unlock tags, the only invalid combination is if you have two intersecting tags that both use the optional size parameter. For example, if [Lock;4] in the control row intersects with [Lock;2] in the control column, that is an invalid action tag pair. However, if you have a lock tag that intersects with an unlock tag (or vice versa), that combination is ignored and no error occurs.

NOTE: Only matching action tag pairs are validated. If a copy tag intersects with a lock tag, those tags are not considered an action tag pair and are ignored. However, each individual tag is validated for correct syntax.

Lock and Unlock tag syntax

Lock and unlock tags use the following syntax:

In both cases, size is an optional parameter that determines the size of the area to lock or unlock. The size is specified using the format WxH (width by height), in relation to the action cell. If omitted, only the action cell is locked or unlocked.

If specified, the area to lock or unlock is calculated starting at the action cell. For example, if the size is specified as 3x2, that is an area three columns wide by two cells high, starting at the action cell. All of the cells in that area are locked or unlocked.

When specifying a size, you can omit the height if you want to use the default height of 1. For example, if you want lock an area that is three cells wide, you can specify 3 instead of 3x1. However, if the width is 1 and you want to specify a height, the width must be included: 1x3. If only one number is specified as the size, it is always interpreted as the width.

NOTE: Action tags can be placed within a formula if desired. For example, you might use an IF formula to determine whether the cell contains an action tag or not.

Lock and Unlock tag example

The following example shows several lock and unlock actions. This is not a real-life implementation example; it is intended to illustrate the lock and unlock concepts.

• The first action cell is E8. Because the lock tag in the control row uses the size parameter, the four blue cells will be locked.
• The next action cell is I8. The size parameter is not used here, so only the green cell will be locked.
• The next action cell is H9. The size parameter is not used here, so only the orange cell will be unlocked.

The intersections of lock and unlock (in H8, E9, and I9) are ignored. Only pairs of lock / lock and unlock / unlock are considered to be action tag pairs.