Tuesday, September 22, 2009

Excel Macros : Check Mark Symbols with Double Click

I am sharing with you very interesting trick to insert check mark symbols / to change check mark symbols with the double click in excel.

We can use Wingding font check boxes and change them with the double click with the help of macro

You can add as many of them by just defining the range in your macro. Values can be changed by just a double click.

You will further need to do data validation for that range so that user does not enter any other value by mistake.

You will need to copy this code to worksheet module of your workbook

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

'* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 16, 2009 *

If Not Intersect(Target, Range("G4:G14")) Is Nothing Then ' You can Change the range here

Cancel = True

With Target
.Font.Name = "Wingdings"
.Font.Size = 20
.HorizontalAlignment = xlCenter
End With

If Target.Value = "þ" Then
Target.Value = "ý"

Target.Value = "þ"
End If

End If

End Sub

Download file having trick to insert /change wingdings check mark symbols with double click

Make sure that you have enabled macros before using this file.

This trick can be used to choose any values you want change with the double click and is not limited only to check boxes in wingdings. See the attached file for Yes/No as another example.

Symbol Check Mark , Symbols Check Mark , Type Check Mark , Wingding Font Check Mark , Wingding Check Mark , Wingdings Check Mark , Check Mark Symbol , Check Mark Symbols , Check Symbol , Change values with double click in excel


  1. The problem here is with the two symbols. One is a checkbox with a check mark, the other is a checkbox with an X. Both indicate true, and at a quick glance it is not so easy to distnguish between them.

    You could find an open checkbox symbol. But even better is using an actual checkbox that the user can check with a single click, without having to rely on code.

  2. Hi Yogesh,
    do you also do development work. if yes can you pls send me email at lailysachi@gmail.com I would like to work with you for several development projects I have.
    Thank You

  3. Hi Yogesh,

    I usually use a little trick to build checkboxes in cells.

    1) Format the cell as Wingdings font
    2) Add borders around the cell
    3) Apply the custom format "ü";;"û"
    4) Use a data validation list to limit the cell values to 1 and 0

    The custom format shows a tick for all positive values, nothing for negative values, and a cross for zeroes. Limiting input to 1 and 0 makes it easy to use the cell in True/False fromulas.

    If using Excel 2007 you can make this look even better by using conditional formatting and the 3 symbol icon set.

  4. Hello Ed

    Welcome to myblog. The trick given by you is wonderfull and can be used without any problem. What makes it more usefull is 1 and 0 as true and false values.

    However you will agree that there are endless ways of doing the same thing and it is all in your "mind" (Code) that how do you treat particualr value in your code for achieving the desired results.


  5. Hello Jon Peltier
    Welcome to my blog. Thanks for your inputs about both the symbols indicating true value. In case user find it difficult to distinguish they can use blank check box by replacing "ý" with "o" to get a blank check box.

    However I believe that we have endless ways of doing the same thing and it is all in "mind" (Code) that how do you treat a particular value in your code to achieve desired results.
    As far as using real check boxes are concerned,

    I find then bit difficult to insert when you have to use many many of them.

    Also using excel cells to display the desired output is more easy to handle when you have to sort / delete / filter or copy that data to another sheets. I am not denying the fact of giving single click input option to user but I use it like this.

    Thanks for your inputs.