Deleted comment with VBA still showing red indicator

S Oberlander

Board Regular
Joined
Nov 25, 2020
Messages
147
Office Version
  1. 365
Platform
  1. Windows
My code is simple, I know the names of my comment shape so I just did:
VBA Code:
        With ActiveSheet
        .Shapes("Comment 1").Delete
        .Shapes("Comment 2").Delete
        .Shapes("Comment 4").Delete
        End With
But the cells that contained the comments still have that little red triangle indicating a comment even though there is none.
How can I get rid of that triangle?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
My code is simple, I know the names of my comment shape so I just did:
VBA Code:
        With ActiveSheet
        .Shapes("Comment 1").Delete
        .Shapes("Comment 2").Delete
        .Shapes("Comment 4").Delete
        End With
But the cells that contained the comments still have that little red triangle indicating a comment even though there is none.
How can I get rid of that triangle?
I do not believe comments have a shape name

Do you mean the comment is in a Named Range?

But then I do know Excel has modified the way comments work in newer versions of Excel so I may be wrong.
 
Upvote 0
This is something new to me. I will watch this thread and see what I can learn.
I guess the comment testbox is a form of shape. That is why that comment symbol is still there

Should have use range to address the location

Sheet1.Range("A1").Comment.Delete
 
Upvote 0
I guess the comment testbox is a form of shape. That is why that comment symbol is still there

Should have use range to address the location

Sheet1.Range("A1").Comment.Delete
That is what I thought also. Not sure why you used Shape.
Glad you figured it out.
 
Upvote 0
I guess the comment testbox is a form of shape. That is why that comment symbol is still there

Should have use range to address the location

Sheet1.Range("A1").Comment.Delete
I know that is probably the more correct way to do it, but I know the name of the comment, not its location... any idea how I can get the location?
 
Upvote 0
VBA Code:
Dim wb As Workbook
Dim ws As Worksheet
Dim cmt As Comment

Set wb = ActiveWorkbook

For Each ws In wb.Sheets
    For Each cmt In ws.Comments
        Debug.Print cmt.Parent.Address
    Next cmt
Next ws

Note: Parent returns the parent object. For Comment, the parent would be the cell where comment resides.
 
Upvote 0
VBA Code:
Dim wb As Workbook
Dim ws As Worksheet
Dim cmt As Comment

Set wb = ActiveWorkbook

For Each ws In wb.Sheets
    For Each cmt In ws.Comments
        Debug.Print cmt.Parent.Address
    Next cmt
Next ws

Note: Parent returns the parent object. For Comment, the parent would be the cell where comment resides.
I modified to the below, now the comments are getting deleted along with the little red triangle :)
VBA Code:
Dim wb As Workbook
Dim ws As Worksheet
Dim cmt As Comment

Set ws = ActiveSheet

For Each cmt In ws.Comments
    x = cmt.Parent.Address
    Range(x).Comment.Delete
Next cmt
I'm just not sure how I'm supposed to dim the x, when I tried dimming it the code didnt work.
 
Upvote 0
You don't need the x variable, you can just do
VBA Code:
For Each cmt In Ws.Comments
    cmt.Delete
Next cmt
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top