Clear cell edit

[mdmackillop]

The following code will write/clear "a" in a cell, but it leaves the insertion point in the cell. I've tried adding SendKeys "{ESC}" but to no effect. Any other suggestions?
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Exit Sub
End If
End Sub

[lucas]

I use this Malcolm:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Target.Offset(0, 1).Select
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Target.Offset(0, 1).Select
Exit Sub
End If
End Sub

[Tommy]

I tried not to post this I really really tried. I couldn't resist::devil2:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count = 1 Then
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Target.Offset(0, 1).Select
ElseIf Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Target.Offset(0, 1).Select
End If
End If
End Sub

[johnske]

another Alternative - use Target.Next.Select in place of Target.Offset(0, 1).Select as per Steve's code

[Norie]

Why not try Cancel=True?

[johnske]

Why not try Cancel=True? :thumb

[lenze]

Why not try Cancel=True?
Right on Norie!!

lenze

[mdmackillop]

That's what I was looking for Norie!
Thanks to all.

[mdmackillop]

Also, turn off "Edit directly in cell" in Tools/Options/Edit as I would have discovered by reading the whole of the KB entry!

[mvidas]

Is it possible to have target.count be more than 1 in a double click event?

[mdmackillop]

It seems not. The code was copied from a KB Item.
BTW, in testing the poosibility, I discovered double clicking a top or left border focus goes to the top/left. Never new that shortcut.

[Bob Phillips]

Don't think so.

[geekgirlau]

Just tested in v2003 - you're right! I never knew this shortcut existed!