Excel Macro - hide comment after edit

Question

I have this for entering a comment on a excel worksheet when user insert C or P, and I need to hide the comment after edit.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range


    Set KeyCells = Range("A1:S1000")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

            Select Case Range(Target.Address)
                Case "C":
                    minhaCelula = Target.Address
                    Range(minhaCelula).AddComment ("")
                    Range(minhaCelula).Comment.Visible = True
                Case "P":
                    minhaCelula = Target.Address
                    Range(minhaCelula).AddComment ("")
                    Range(minhaCelula).Comment.Visible = True

            End Select


    End If
End Sub

score 2 · Accepted Answer · edited May 23 '17 at 12:22

Some problems with that code:

Select Case Range(Target.Address) doesn't make sense - it takes the Target range, takes its address and creates a range from that address, which points to the original Target range, and finally VB takes the default property of that range, because its not being used in an object reference context. So the whole thing should be replaced with Target.Value.
Later same thing happens to minhaCelula.
Code under "C" and "P" is the same and should be placed under the same Case branch.
Colons are not used in VB's Select Case.
AddComment should be called without parentheses. Even better, you should benefit from the fact AddComment returns the reference to the added comment, so you can directly use that (in which case you must keep the parentheses).

So that should be rewriteen as:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Me.Range("A1:S1000"), Target) Is Nothing Then
        Select Case Target.Value
            Case "C", "P"
                Target.AddComment("").Visible = True
        End Select
    End If
End Sub

As for the question, when you use Comment.Visible, Excel stops managing the comment's visibility. To leave the management on Excel side, make the comment's shape visible instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Me.Range("A1:S1000"), Target) Is Nothing Then
        Select Case Target.Value
            Case "C", "P"
                With Target.AddComment("").Shape
                    .Visible = msoTrue
                    .Select
                End With
        End Select
    End If
End Sub

Excel Macro - hide comment after edit

1 Answers1