Show/Hide Comments in Excel using VBA

Question

I have comments on an Excel spreadsheet.

I have one button to control whether the comments are displayed or hidden. When a user clicks the button, the comments should show. When the user clicks it again, the comments should go away.

Below is the code I'm trying to use - they both work independently, but when I put in an If Then Else statement, I get errors:

Sub showcomments()

If Comments <> "Visible" Then Application.DisplayCommentIndicator = xlCommentAndIndicator
Else: Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End If
    
End Sub

I tried else if comments = visible.

I usually get the error "else without if".

Answer 1

Try this:

   Sub showcomments()
   Comments = 1
   For Each MyComments In ActiveSheet.Comments
       If MyComments.Visible = True Then
           Comments = 0
       End If
   Next
   If Comments = 1 Then
       Application.DisplayCommentIndicator = xlCommentAndIndicator
   Else
       Application.DisplayCommentIndicator = xlCommentIndicatorOnly
   End If
End Sub