Press space bar or click on mouse button to insert a check mark in acell

[Dave]

Hi,

I need a macro that would on pressing the space bar or click on mouse
button will insert a check mark in a cell.
I have MS Excel 2003.

thanks

Dave

 

[ryguy7272]

You've come to the right place! Here is one way:

Right-click the sheet tab and paste this code into the window that opens:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
If Not Intersect(Target, Range("A1:AZ100")) Is Nothing Then
If Target = "P" Then
Target = vbNullString
ElseIf Target = vbNullString Then
Target = "P"
Else
End If
End If
End Sub

Format cells as Wingdings2

Also, from a recent post by Jacob Skaria (I've learned so much from this guy).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
If Target.Count = 1 Then
Target.Font.Name = "Marlett"
Target.Value = IIf(Target.Value = "r", "e", "r")
'Target.Value = IIf(Target.Value = "a", "e", "a") 'For tick mark
End If
End If
End Sub

That will give you an X, not a check, but you may find a use for it
nevertheless...and it is very cool too...

HTH,
Ryan---

 

[Rick Rothstein]

How about in response to a double click (change the address for the cell in
the first If..Then statement to the actual address of your cell and use the
$ signs in the address)...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$A$1" Then
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub

To install this code, right click the tab at the bottom of the worksheet you
want this functionality on, select View Code from the popup menu that
appears and copy/paste the above code into the code window that appeared.
Now, go back to the worksheet and double click the cell to put the "X" in
the cell, then double click it again to clear it. Instead of the "X" I used,
you can change the font for the cell and then use any character you want.

 

[Dave]

How about in response to a double click (change the address for the cell in
the first If..Then statement to the actual address of your cell and use the
$ signs in the address)...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
  If Target.Address = "$A$1" Then
    Cancel = True
    If Target.Value = "" Then
      Target.Value = "X"
    Else
      Target.Value = ""
    End If
  End If
End Sub

To install this code, right click the tab at the bottom of the worksheet you
want this functionality on, select View Code from the popup menu that
appears and copy/paste the above code into the code window that appeared.
Now, go back to the worksheet and double click the cell to put the "X" in
the cell, then double click it again to clear it. Instead of the "X" I used,
you can change the font for the cell and then use any character you want.

--
Rick (MVP - Excel)







- Show quoted text -

Thanks for all your help. I am going to use Ricks code as below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
If Target.Address = "$A$1" Then
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub


What If I had multiple cells where I would like this action to occur
could I do somethig as below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
If Target.Address = "$A$1" OR "$L$15" OR "$m$34" Then
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub

thanks again

 

[Rick Rothstein]

There are several ways to do it. The standard way is pretty much to
construct a Range referencing the cells in the range and test the
intersection of that range and the Target. To do this, replace this line in
my code...

If Target.Address = "$A$1" Then

with this one...

If Not Intersect(Target, Range("$A$1,$L$15,$M$34")) Is Nothing

--
Rick (MVP - Excel)

How about in response to a double click (change the address for the cell
in
the first If..Then statement to the actual address of your cell and use
the
$ signs in the address)...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$A$1" Then
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub

To install this code, right click the tab at the bottom of the worksheet
you
want this functionality on, select View Code from the popup menu that
appears and copy/paste the above code into the code window that appeared.
Now, go back to the worksheet and double click the cell to put the "X" in
the cell, then double click it again to clear it. Instead of the "X" I
used,
you can change the font for the cell and then use any character you want.

--
Rick (MVP - Excel)







- Show quoted text -

Thanks for all your help. I am going to use Ricks code as below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
If Target.Address = "$A$1" Then
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub


What If I had multiple cells where I would like this action to occur
could I do somethig as below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
If Target.Address = "$A$1" OR "$L$15" OR "$m$34" Then
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub

thanks again