VBA & Macro Problems

How to Use VBA Cell Checkmarks Instead of Excel Form Controls

Kushani NimanthikaKushani Nimanthika Oct 22, 2026 871 views

Question details

The user wants to implement a VBA macro that toggles checkmarks inside cells via double-click to replace multiple form control checkboxes.

How to Use VBA Cell Checkmarks Instead of Excel Form Controls
Product
Excel
Device & OS
not provided
Scenario
Creating an interactive checklist in a workbook without causing performance issues from adding hundreds of individual form control objects.
Observed behavior
Adding large numbers of Excel form checkboxes causes the workbook to become slow, difficult to edit, and highly prone to freezing, especially when trying to delete the controls.
Before you start

Before applying the macro, save your file as a Macro-Enabled Workbook (.xlsm) and identify the specific cell range (e.g., A1:A10) where you want the double-click checkmarks to function.

Solution 1Recommended

Implement a Worksheet_BeforeDoubleClick Macro

Use a VBA event to toggle checkmarks directly inside cells, keeping your workbook lightweight and preventing the lag associated with form control objects.

By embedding a Worksheet_BeforeDoubleClick event into the specific sheet's code module, you can make cells automatically insert or remove a checkmark symbol when double-clicked. This method modifies the cell value directly, completely avoiding the need to render heavy graphical form controls.

1
Save as a Macro-Enabled Workbook

Go to File > Save As, and change the file format to Excel Macro-Enabled Workbook (*.xlsm) so your VBA code will be preserved upon closing.

2
Open the Visual Basic Editor

Right-click the worksheet tab at the bottom of your screen (e.g., Sheet1) and select 'View Code' from the context menu to open the VBA Editor directly to that sheet's module.

3
Paste the VBA Code

In the code window, paste a Worksheet_BeforeDoubleClick event macro configured to target your desired range (like A1:A10) and toggle the target cell's value between a checkmark character and an empty string.

4
Close the Editor

Press Alt + Q on your keyboard to close the Visual Basic Editor and return to your main spreadsheet view.

5
Format the Target Cells

Select the cell range defined in your macro (e.g., A1:A10) and change the font to 'Wingdings' or 'Marlett', depending on which specific character code your VBA script uses to render the checkmark.

6
Test the Double-Click Event

Double-click any cell within the formatted range to verify that the checkmark successfully appears and disappears.

Implement a Worksheet_BeforeDoubleClick Macro
Prevent Accidental Toggles: Using the BeforeDoubleClick event is highly recommended over SelectionChange, as it requires a deliberate double-click action. This prevents the checkmark from toggling accidentally if you merely navigate into the cell using your keyboard arrow keys.
Advanced VBA Macros in WPS

Create VBA Macros Seamlessly with WPS Spreadsheets

WPS Office offers comprehensive VBA support, allowing you to run, edit, and troubleshoot macro-enabled workbooks effortlessly. You can easily implement double-click cell checkmarks to keep your spreadsheets lightning-fast.

  1. 1. Launch WPS Spreadsheets: Download and open WPS Office, then launch the Spreadsheets application.
  2. 2. Open Your Workbook: Open your existing .xlsm file or save a new checklist document as a macro-enabled workbook.
  3. 3. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click 'VBA Editor' to launch the coding environment.
  4. 4. Apply the Macro: Paste your BeforeDoubleClick checkmark code into the desired worksheet module, save, and enjoy a lag-free checklist.
Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in Visual Basic Editor for seamless macro managementLightweight software architecture that prevents freezing and laggingFamiliar user interface requiring zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why do form control checkboxes make my Excel workbook slow?

Each form control is an individual graphical object superimposed over the worksheet grid. When you add hundreds or thousands of them, the spreadsheet engine must render and track every single object's state and position simultaneously. This heavily consumes system memory and processing power, leading to lag, slow saves, and freezing.

What is the correct font to use for VBA checkmarks?

The most commonly used fonts for checkmarks are 'Wingdings' and 'Marlett'. In the Wingdings font, character code 252 (or 254 for a boxed checkmark) renders as a checkmark. In the Marlett font, simply typing a lowercase 'a' renders as a checkmark, making it very easy to code into a VBA macro.

How can I restrict the checkmark VBA macro to a specific column?

Within your Worksheet_BeforeDoubleClick macro, you can use the Intersect function to check if the clicked cell falls within your target range. For example, adding an IF statement like 'If Not Intersect(Target, Range("A1:A100")) Is Nothing Then' ensures the checkmark toggle only runs when cells strictly within A1 through A100 are double-clicked.