Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deselect comment text box (2003)

    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,

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deselect comment text box (2003)

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect comment text box (2003)

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deselect comment text box (2003)

    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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect comment text box (2003)

    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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect comment text box (2003)

    Try changing your Sub to:

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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect comment text box (2003)

    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
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect comment text box (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •