What it does
Uses a shortcut-based menu (picker prompt) to let you set and use your custom-defined tickmarks to indicate review steps and other worked performed in your Excel files.  These can include cell formatting, values, symbols, pictures, and icons.  You can set up individual tickmarks with the Tickmark Picker macro or set them up in bulk with the Bulk Edit Tickmarks macro.

When to use it
When you want to quickly and easily set and use tickmarks to indicate your review or other work performed.

Why to use it
Tickmarks are an efficient and consistent way to indicate review and other work performed in Excel, and they are especially popular with accountants.  This macro (and the related Bulk Edit Tickmarks macro) allow you to easily save your custom tickmarks to a central library and quickly apply them in a shortcut-based approach.

Default shortcut
None

Other Details

• Category: Accounting / Other
• Difficult: 2/5
• Usage/frequency: 3/5
• Automation factor: 3/5 (estimated 15 seconds saved each time used)
• Type: Shortcut
• Date added: 12/27/2024
• Tags: Tickmarks, custom, picker, bulk

Related Macros
Bulk Edit Tickmarks

Other Articles
None

Tickmarks Example File

Prerequisites
With your custom tickmarks configured, select a cell where you want to apply your desired tickmark.  Use the Tickmark Picker or the Bulk Edit Tickmarks macro to set your custom tickmaks (see steps below).  Use the example file in the section above as a guide for what to use as your custom tickmarks.

Edit a cell with the formatting, value, symbol, icon/picture (in-cell) you want to use.  Add Lambda custom functions to your workbooks.  If enabled, they will automatically be added to your library when you save files (see below on how to toggle this).  Alternatively, you can add them to your library in bulk with the Bulk Edit Lambda Formulas macro (see below for instructions on the bulk edit approach).

Instructions – Search Lambda Formulas
With custom tickmarks configured, run the Tickmark Picker macro.  It will show you shortcut-based menu (picklist) of your configured tickmark labels, and the associated code to apply them in any cells you have selected.  Simply enter the code (1 through 0 or A through E) and press the Enter key.  To set your default code, enter the code and click the default checkbox at the bottom.  The tickmark will be entered into any selected cells.

To set a custom tickmark, first edit a cell with the formatting, value, symbol, and/or icon/picture (in-cell) you want to use as your custom tickmark.  Then run the Tickmark Picker macro.  Enter an X to indicate defining and saving a new custom tickmark.  Then enter a space, then a label describing the tickmark, then another space, then the word DEFAULT (all caps), and finally the code to apply it to.

Example: Enter “X Agreed to prior year DEFAULT9” to set a custom tickmark with the label “Agreed to prior year” to slot 9 on the picklist menu.

To edit any tickmarks, simply edit the cell to use as the tickmark, then repeat this step, changing the label and the code slot to apply it to.  Alternatively, you can set multiple custom tickmarks using the Bulk Edit Tickmarks macro (see below).

Instructions – Bulk Edit Tickmarks
To manage your custom tickmark library, where you can add, edit, delete, review, or share the library, run the Bulk Edit Tickmarks macro.  It looks for the Bulk_Edit_Tickmarks sheet within your active workbook.  If not found, it is added.  Any existing tickmarks in your library will be displayed (they are all blank by default).  Make any edits to the columns described below, then run the Bulk Edit Tickmarks macro again to process your contents and save the custom tickmarks in the sheet to your library.  These are the columns within the Bulk_Edit_Tickmarks sheet to review or fill in:

• Column B – Label (Required): give each custom tickmark a unique label to tell them apart when displayed on the picker.
• Column C – Format/Value (Required): set the formatting, value, symbol, and/or icon/picture (in-cell) for each tickmark you want to set.

TBD…