• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comment Object Positioning Properties

dparteka

dparteka

Member
I have this macro that creates comments... I want to include in the macro to have it set the Object Positioning Properties to >>> Move but don't size with cells


Sub CreateComment()

ActiveCell.AddComment

ActiveCell.Comment.Shape.TextFrame.AutoSize = True

ActiveCell.Comment.Visible = False

ActiveCell.Comment.Text Text:="Employee No.: "

Application.SendKeys "+{F2}"

End Sub


I appreciate any help... thanks
 
Dparteka


Try the code below

[pre]
Code: 
Sub CreateComment()
With ActiveCell.AddComment
.Shape.TextFrame.AutoSize = True
.Visible = False
.Text "Employee No.: "
.Shape.Placement = xlMove
End With
End Sub
[/pre]
 
Thanks Hui... the macro works great, I thought this would solve my issue but it does not because even though the comment properties are set to not move they still move when using filters. So I found this macro (see below) on another site that resets all comments, it works good, the thing I don't like is that every time I use a filter I get a warning "Fix objects will move"... if I have 50 comments I get 50 warnings that need to be individually acknowledged, is there a way to ignore these warnings?


Sub ResetComments()

Dim cmt As Comment

For Each cmt In ActiveSheet.Comments

cmt.Shape.Top = cmt.Parent.Top + 5

cmt.Shape.Left = _

cmt.Parent.Offset(0, 1).Left + 5

Next

End Sub
 
Hi,


I tried to replicate you scenario but could not trace any issue, may be I did not go in right direction.


Anyways, can you try disabling display alerts ?


Application.DisplayAlerts = False


Do let us know if this works..


Regards,

Prasad DN
 
Hi, dparteka!

What Excel version are you using?

I've just opened a new workbook, added 2 comments, moved them far away from the original position, copied your ResetComments to a Module, run it, and no messages popped up.

I'm using Excel 2010.

Regards!
 
SirJB7... we're using 2003 SP3 here, did you use filtering? When I use filtering I have no problem, when I create a few comments then the warnings appear when selecting a filter.
 
Hi, dparteka!

Sorry I can't help you, but I only have access to machines with Office 2010 and 2007.

Regards!
 
Okay SirJB7... if I set the comment properties to "Move and size with cells" the warning does not appear. I've run several tests to see if this has any undesirable effects on my spreadsheet and so far I've found none, so that's what I'll go for... thanks again for your help
 
Hi, dparteka!

Thanks for the feedback.

Regards!

PS: I've just sent you a merged code for another topic, please test it.
 
You must log in or register to reply here.
Back
Top