How do make a cell show a check mark on 'click'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I am trying to have a column whose cells toggle a check or a 'tick' sign when clicked on. How can I do this? I can't find any info on the excel books I have

Thanks in advance for your help!
Michelle
 
Excel doesn't really have a way to catch a single click on a cell.

How about a right click (or even double click)?

Here's a routine that checks to see if you rightclicked in column A.

If you did it checks to see what's in that cell. If it's empty, it adds a
formula (=char(252)) and formats the cell with a Wingdings font.

If the cell has something in it, it clears it.

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Formula = "=char(252)"
Target.Font.Name = "Wingdings"
Else
Target.ClearContents
End If

Cancel = True 'stop the rightclick menu

errHandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that should have this behavior. Select view
code and paste this in. Change the range to what you want.

And then back to excel to try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson also has some nice notes about workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm
 
Dave Peterson wrote
If you did it checks to see what's in that cell. If it's empty, it
adds a formula (=char(252)) and formats the cell with a Wingdings
font.

If the cell has something in it, it clears it.
Click to expand...

While this doesn't include the 'clear', it works on a single click in colA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, [a:a]) Is Nothing Then Exit Sub
With Selection
.Value = Chr(252): .Font.Name = "Wingdings"
End With
End Sub
 
I'm not sure if this is important, but this'll put a check mark in the cell if
you select it with the cursor keys via the keyboard, too.

(it could be what the OP was really looking for, too!)
Dave Peterson wrote
If you did it checks to see what's in that cell. If it's empty, it
adds a formula (=char(252)) and formats the cell with a Wingdings
font.

If the cell has something in it, it clears it.
Click to expand...

While this doesn't include the 'clear', it works on a single click in colA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, [a:a]) Is Nothing Then Exit Sub
With Selection
.Value = Chr(252): .Font.Name = "Wingdings"
End With
End Sub
Click to expand...
 
Dave Peterson wrote
I'm not sure if this is important, but this'll put a check mark in the
cell if you select it with the cursor keys via the keyboard, too.
Click to expand...

Good point. On another note, I use code that offsets a checkmark to the
current date column when names are clicked in a range in col A and I had to
be sure my Workbook_Open() routine didn't land me in that range.
(it could be what the OP was really looking for, too!)
Click to expand...

Not sure which version OP ended up using, but we got 'Thanks guys'
 
I agree with that, I found the use of selection too sensitive, and think the
double click or right click choices would be more suitable for most.

There was a reference to my Getting Started with Macros page,
but for Worksheet and Workbook events the better page on my
site would be (and I'll make a direct reference to the checkmarks part)
http://www.mvps.org/dmcritchie/excel/event.htm#tick
To install an event macro, simply right click on the worksheet tab,
then view code. Though the original poster already knew that.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
 
Back
Top