Deleted comment with VBA still showing red indicator

S Oberlander

New Member
Joined
Nov 25, 2020
Messages
10
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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
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?
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.
 

S Oberlander

New Member
Joined
Nov 25, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
well this code deletes the comment but not the indicator
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
566
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
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.
 

S Oberlander

New Member
Joined
Nov 25, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
566
Office Version
  1. 2016
Platform
  1. Windows
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.
 

S Oberlander

New Member
Joined
Nov 25, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,804
Office Version
  1. 365
Platform
  1. Windows
You don't need the x variable, you can just do
VBA Code:
For Each cmt In Ws.Comments
    cmt.Delete
Next cmt
 

Watch MrExcel Video

Forum statistics

Threads
1,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

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
Top