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".

It's for a dashboard I created so I'm trying to make it as simple and clean as possible. — Adam
– Adam, Commented Sep 25, 2013 at 20:53

swan · Accepted Answer · 2015-12-24 20:27:03Z

4

Was wanting to do this in Excel myself. If I'm not mistaken, this works perfectly fine for what you want and requires no looping or extra global variables...

Sub showcomments()
  If Application.DisplayCommentIndicator = xlCommentIndicatorOnly Then
    Application.DisplayCommentIndicator = xlCommentAndIndicator
  Else
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
  End If
End Sub

answered Dec 24, 2015 at 20:27

swan

413 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kay Over a year ago

..this works perfect for me on Win10/MS Office 2010! Thx!

Jair Batista · Accepted Answer · 2013-09-25 21:01:36Z

3

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

answered Sep 25, 2013 at 21:01

Jair Batista

884 bronze badges

Comments

Automate This · Accepted Answer · 2013-09-25 21:12:50Z

0

This method uses a global variable and doesn't have to loop through all your comments.

Public comments As Integer

Sub showcomments()
 If comments = 1 Then
   Application.DisplayCommentIndicator = xlCommentAndIndicator
   comments = 0
  Else
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    comments = 1
  End If
End Sub

edited Sep 25, 2013 at 21:12

answered Sep 25, 2013 at 21:00

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

1 Comment

Adam Over a year ago

This one works too, thanks! Gonna try and compare the two that worked with mine and figure out what went wrong.

cyberponk · Accepted Answer · 2020-09-15 22:25:58Z

0

I used another approach which I find easier:

Sub Show_Hide_Comments(visible As Boolean)
  Dim cmnt As Comment
   For Each cmnt In ActiveSheet.comments
       cmnt.Shape.Visible = visible
   Next
End Sub

This finds all comments and hides their shape. You can even do this individually for certain comments if you wish.

answered Sep 15, 2020 at 22:25

cyberponk

1,88421 silver badges22 bronze badges

Collectives™ on Stack Overflow

Show/Hide Comments in Excel using VBA

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related