Comment Box Availability and Sizing

Hello,

I am working my way through 2 issues. Both have to do with comments and are found in the range "L4:L9,L11:L16,L18:L44"

1) Before protecting the sheet, I select "edit objects" so that I can insert comments. However, when I run my "New Period" Macro I lose the ability to insert comments when the sheet re-protects itself at the end. Is there a way to have the "edit objects" option always selected when it re-protects?

2) Is there a way to auto-size the comment box based on the text that is entered?

Adam

I receive an error message of Invalid call procedure or argument when it gets to .AddComment

With Range("L4:L9,L11:L16,L18:L44")
.AddComment
.Comment.LockAspectRatio = msoTrue
.Comment.Visible = False
.Comment.Shape.LockAspectRatio = msoTrue
.Comment.Shape.DrawingObject.AutoSize = True
End With

With Range("L4:L9,L11:L16,L18:L44")
.AddComment
.Comment.Visible = False
.Comment.Shape.LockAspectRatio = msoTrue
.Comment.Shape.DrawingObject.AutoSize = True
End With


It still stops at .AddComment

Message reads:

Run time error '5':
Invalid procedure call or argument

data value
vijay
vijay
vijay 43
vijay
kama
kama
kama 57
kama
ram
ram
ram 70
ram 94
nam 75
nam

This the data of my excel sheet where i need to remove duplicate value in other sheet and want vlookup with value i want to remove blank cell

The Macro runs without error, but now i am unable to insert comments, perhaps i am placing the code in the wrong part of the module.

Attached is my current workbook. Thanks again for the help. I appreciate it.

Thanks, works great.

Adam