Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Toggle Symbol In a Cell by a single Click

0

I have a spreadsheet with a checklist. I want to be able to go down the list of items and in the adjacent column, click once to insert a checkmark symbol. It I click once on it again, I want it to be removed. In other words, toggle on, then the next click ttoggles it off.

I want to use VBA to do this, but I am not sure which Events I need to use. I also want to restrict any other characters from being entered in the "check box" column.

Can  you help?

excel
posted by: tcarnahan on Sat Feb 7, 2026 at 4:50 pm
Answer
Discuss

Answers

0

 Hello tcarnahan and welcome to the forum,

This can be easily achieved with a Worksheet_BeforeDoubleClick event. The following is what I used in a project some years ago:

As mentioned in the code the checkbox cells are formatted with font 'Wingdings2' and "P" inserts a checkmark

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' macro written by WillieD24, Dec. 27/09
' this code will change the cell value to "P" if it is nothing or
'       change it to nothing if it is "P"
' the checkbox cells are formatted with font 'Wingdings2' and "P" inserts a checkmark

On Error GoTo Line999   ' exit the sub
' restrict code to a particular column (change A:A to suit)
' if any column other than "A" is selected the code will end
If Intersect(Range("A:A"), Selection) Is Nothing Then Exit Sub

If Target.Value = "P" Then
    Target.Value = ""
    Else: Target.Value = "P"
End If

Cancel = True

Line999:
End Sub

This code is placed in the worksheet code window.

If this solves your issue then plesae mark my answer as selected.

Cheers   :-)

posted by: WillieD24 (rep: 723) on Sat Feb 7, 2026 at 5:33 pm
Discuss

Discussion

@Willie - good work but you missed the bit about needing to "... restrict any other characters.. " (I think you could still paste values into column A in your example). I guess you'll use Data Validation or the Worksheet_Change event.
John_Ru (rep: 6792) Feb 9, '26 at 1:36 pm
@John
You're right, good catch. Data validation (for the "checkmark column") would be the easiest way to restrict other characters from being entered.

Cheers   :-)
WillieD24 (rep: 723) Feb 9, '26 at 2:19 pm
For reasons I can't explain, the event "Worksheet_BeforeDoubleClick" does NOT fire.     Are there other things I can do to troubleshoot.   Originally, I had hoped I could achieve the toggling of this symbol with a "single click" OR maybe a right click menu.    Also, I am running the latest Windows 11 and Microsoft 365,but I am experiencing the following:   1) double-clicks don't select things in Word or Excel 2) dragging through items don't select characters or words unless I hold down the Shift key first. Even then, I get a random un-selecting of whatever I selected. I should be able to drag and highlight multiple numbers or characters, then to replace what I have selected, type over them OR paste over them.  That does not work most of the times. Very random.
tcarnahan Feb 11, '26 at 11:24 am
@tcarahan

You have probably placed the code into a regular code module. The code must be placed (copied to) the worksheet code window. To copy the code click "Select All" above the posted code. To paste the code to the worksheet code window, in the visual basic editor window, in the project explorer pane at the left, double click on the worksheet where you want the code to run. In the code window which opens, at the top left use the drop down to select "Worksheet". Then delete the "Worksheet_SelectionChange" event. Now paste in the "Worksheet_BeforeDoubleClick" code. Remember to change "A:A" to the column where you want the checkmarks to appear. Also, be sure to format the cells for the checkmarks to "Wingdings2" font. Also, those cells where you want checkmarks, use Data Validation to restrict data entry to a capital "P". I have included a sample file (Checkmarks - Feb 2026.xlsm) to my answer to show how this works.   If you are still having difficulties let us know. If this solves things, please mark my answer as Selected.

Cheers   :-)
WillieD24 (rep: 723) Feb 11, '26 at 4:17 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login