Double Click to add/remove Tick

In this article, we will use before double click event of the worksheet to check and uncheck the cell.

Raw data consists of some food items. We want to tick mark those items, which we have purchased.

ArrowRawData

On double-clicking a cell, tick mark will be added to it. In the same way, it can be removed by double-clicking on it again.

ArrowOutput

Code explanation

If Target.Column = 3 Then

The above code is used to restrict double-click event to execute only when cell in the 3rd column is double-clicked.

Cancel = True

The above code is used to prevent a cell from getting into edit mode.

Target.Font.Name = "Marlett"

The above code is used to assign Marlett font to the target cell.

If Target = "" Then

Target = "a"

Else

Target = ""

End If

The above code is used to insert a tick mark and remove it as well. In Marlett font “a” represents tick mark and blank is used to remove the tick mark.

 

Please follow below for the code


Option Explicit

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

'Checking whether target cell is in third column
If Target.Column = 3 Then
    
    'Prevent cell going into Edit Mode
    Cancel = True
    
    'Changing font type of the cell
    Target.Font.Name = "Marlett"
    
    'Checking if target cell value is blank then inserting tick
    If Target = "" Then
        Target = "a"
    Else
        Target = ""
    End If
End If

End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Users are saying about us...

  1. Hi,
    this is very nice and almost what I need 🙂
    Could you please improve this code so that it works as follows: Checkmark can appear only once in a range! And, at the same time, it should disappear if you double-click on the cell with the checkmark once again (all cells in the range can be cleared that way e.g. at the beginning of the process. The third requirement is that I must set several ranges that work independently.

    I would really appreciate your help!

    Best regards,
    Robert

  2. Hi, I am learning about using macros on excel. I have been trying to figure out how to add this to a second column on the same worksheet but cannot figure it out due to the repeat name being "ambiguous". Is there a specific way I would have to do this to have 2 columns that contain the double click to tick function

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube