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?
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I use this Malcolm:
[VBA]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[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I tried not to post this I really really tried. I couldn't resist:
[vba]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[/vba]
another Alternative - use Target.Next.Select in place of Target.Offset(0, 1).Select as per Steve's code
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Why not try Cancel=True?
Originally Posted by Norie
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Right on Norie!!
lenze
That's what I was looking for Norie!
Thanks to all.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Also, turn off "Edit directly in cell" in Tools/Options/Edit as I would have discovered by reading the whole of the KB entry!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Is it possible to have target.count be more than 1 in a double click event?
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Don't think so.
Just tested in v2003 - you're right! I never knew this shortcut existed!
Posting Permissions
Reply With Quote
