Deselect comment text box (2003)

Topic

New Reply

The code below successfully writes a cell’s formula into a comment for that cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range)
Target.AddComment (Target.Formula)
End Sub

However, there is one slight snag to the code. When the macro has finished the comment text box is still active in edit mode. There must be a way to tell Excel I do not want the text box enabled once the macro has run, I just can’t identify how to do it.

Any help gratefully received,

Reply | Quote

Replies

After your macro is done, you continue with the dbl-click event which puts you into “edit mode” for the cell. To prevent this just add the line:

Cancel = True

The macro is run “before the dbl-click event” but then you cancel the dbl-click event from proceeding to the edit mode.

Steve

Reply | Quote

He must also add Cancel as a parameter to the event routine.

Reply | Quote

Yes, you are correct and I had not noticed the omission of it.

When I add the event it always gets put in there, so I don’t think about someone removing it…

Steve

Reply | Quote

Try changing your Sub to:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.AddComment (Target.Formula)
Cancel = True
End Sub

Reply | Quote

Legare,

You dim oComment as comment, but I don’t see where oComment it used in the sub….is it necessary to have that line in the code?

Just trying to understand.

Thanks,

Brett

Reply | Quote

No, that line is not necessary. That is left from some testing I was doing. I will delete it from my original message. Thanks.

The reason that was there is that I was working on the version below which I did not finish then. This would be my preferred routine since it will not fail if the target cell already has a comment.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim oComment As Comment
On Error Resume Next
Set oComment = Target.Comment
On Error GoTo 0
If oComment Is Nothing Then
Set oComment = Target.AddComment
End If
oComment.Text Target.Formula
Cancel = True
End Sub

Reply | Quote

Legare, Brett and Steve,

Thanks for your comments. Sorry I haven’t replied earlier but it is a work problem and I had no desire to follow the thread from home over th weekend or on my day off yesterday.

I had completely overlooked the relevance of Cancel in the event. Thank you for pointing out the error of my ways.

Reply | Quote