Show/Hide Comment with VBA

[mikerickson]

My Excel>Preferences>View>Comments is set to Comment Indicator Only.

Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.

When I run the code

...
   Range("B52").Select
End Sub

the cell is selected, but no comment appears.

When I run

...
  With Range("B52")
      .Select
      .Comment.Visible = True
   End With
End Sub

the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.

I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.

Does this require event code?

Thank you.

 

[schielrn]

Typically comments only appear when the mouse is hovered over a cell (when Comment Indicator Only is selected), not necessarily clicked on or moved to with the mouse. Of course when you click on a cell then you are hovered over it, which is why you would see the comment. If you want when it is actually selected, then I believe yes, you would need event code to handle this.

 

[Ragnar1211]

My experience is that comments stay visible until they are set to false.

So it sounds like you may have to have an event that sets them all to false after the cell changes.

 

[Colin Legg]

Hi Mike,

In Excel XP (and assuming Macs work the same way) a comment is shown as a result of the mouse pointer hovering over the cell rather than the cell being activated or selected. So yes, I think that if you want the comment visible until another cell is selected then you would have to show it and then use the Worksheet's SelectionChange event handler to hide it.

 

[schielrn]

This probably isn't the best way, but it works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static oldCell As Range
On Error Resume Next
oldCell.Comment.Visible = False
Target.Comment.Visible = True
Set oldCell = Target
End Sub

It could probably be cleaned up and didn't test everything as I have a meeting to go to. I also tried to capture the error and not use on error resume next, but couldn't figure a way around it right away.

Hope that helps.

 

[mikerickson]

Wow, I wasn't aware how long I hovered after clicking. I can verify that it is the hovering rather than the selecting that causes the comment to appear in Excel2004. Select; Un-hover and the comment goes away.

I could(?) get the desired result by using AppleScript to cause the mouse to hover, but that's not worth it for this use. (Plus, I have no idea if this is possible on Windows.)

I'll go with a redesign of my UI.

Thanks guys.

 

[GTO]

Howdy Mike,

Not sure if a worthwhile suggestion, particularly as I cannot readily think of a way to get the cell's left/top (vs window/how many toolbars etc), but would SetCursorPos be of any use?

If you could accurately say where B52 is, then setting the cursor position would display the comment w/o setting visible to true.

Option Explicit
Private Declare Function SetCursorPos Lib "user32" (ByVal X As Long, _
                                                    ByVal Y As Long) As Long
Sub Test()
Dim rB52 As Range
    Set rB52 = Range("B52")
    
    Application.Goto rB52.Offset(-3), True
    
    With rB52
        .AddComment "Hello"
    End With
    SetCursorPos 80, 190
    
End Sub
Sub reset()
    Range("B52").Comment.Delete
End Sub

Mark

 

[RoryA]

You could use Data Validation instead?

   With Range("G16")
      .Select
      With .Validation
         .Add xlValidateInputOnly
         .InputMessage = "here is your 'comment text'"
      End With
   End With

or maybe Application.OnTime to hide the comment after a set time?

 

[mikerickson]

I like the Validation idea.

It feels cleaner than OnTime or event code.

 

[mikerickson]

I discovered a feature of Excel:
If a cell (with an input validation message) is selected and then the cell's .Valiation is deleted, the message persists until another cell is selected.

This does what I want

    With StartCell
        On Error Resume Next
            .AddComment
            .Validation.Add xlValidateInputOnly
        On Error GoTo 0

        .Validation.InputMessage = resultString

        With .Comment
            .Shape.TextFrame.Characters.Font.Size = 12
            .Text Text:=resultString
        End With

        Application.Goto .Cells(1, 1)
        .Validation.Delete
    End With

Thank you, everyone.