Toggle a cell

[Guest]

I've tried several methods but can't quite figure it out. How can I write
the code so that when I click on a cell within a column it will toggle it
(i.e. if blank, put an "X" value or if it has an "X" value it will leave it
blank).
I want this to happen whether I click on the active cell or on a new cell
within the column.
Also, how do I do this if it requires a double click.
Thanks,
Brad

 

[Frank Kabel]

Hi
use the Selection_Change event of your worksheet. Put the following code in
your worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Target.Value <> "" Then
Target.ClearContents
Else
Target.Value = "X"
End If
Application.EnableEvents = True
End Sub

 

[Guest]

Thanks Frank,
This is not quite what I was looking for. I am specifically trying to
"toggle" the cell only when it is clicked on (or double-clicked), not just
when the cell becomes active. Further, even when the cell is active I would
like to be able to click on it again to toggle it again.
Thanks,
Brad

 

[Frank Kabel]

Hi
only processing the click event is not possible. What you could do is using
the Before_Doubleclick event instead of the selection_change event in the
same manner

 

[Guest]

Thanks Frank. I got this work. One more question. After Doubleclick I do
not want the cell left open for editing. I have been able to turn off cell
editing at beginning of code but when I turn it on again at the end of the
code it appears to negate turning it off at the start. Here is what I have:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.EditDirectlyInCell = False
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Value <> "" Then
Target.ClearContents
Else
Target.Value = "X"
End If
Application.EnableEvents = True
Application.EditDirectlyInCell = True
End Sub

 

[Frank Kabel]

Hi
try:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.EditDirectlyInCell = False
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Value <> "" Then
Target.ClearContents
Else
Target.Value = "X"
End If
cancel=true
End Sub