A Question Regarding Comments

[Guest]

I insert a comment in one of the locked cells. If I protect the sheet
without the Edit Objects box checked and when I point to that cell, the
comment won't show.

On the other hand, if I protect the sheet with the Edit Objects box checked,
the comment will show; however, it will also allow the user to make changes
to the comment.

My sheet contains multiple columns, and the column headers are protected. I
am inserting comments in the column headers so that the users know the
purpose of those columns and what to enter. I like it to be able to show
when the user points to the header, and I don't like the users able to change
the comments.

Is there a way to do it?

Thanks.

Alan

 

[Dave Peterson]

I just tried this in xl2003.

I added a comment to a cell.
I protected the worksheet (just "select locked cells" and "select unlocked
cells" were checked).

The comment showed up when I moused over that cell with the comment.

(I had "Comment indicator only" checked in tools|Options|view tab.)

If you try it again and it fails, you may want to post back with more
info--including the version of excel and that setting.

 

[Guest]

Hi Dave,

Thanks for your help. I am also using Excel 2003, and I do have the
"Comment Indicator Only" selected.

When I protect the sheet, in addition to the two items that you mentioned, I
also have the following boxes checked: "Format columns", "Format rows",
"Insert rows" and "Use AutoFilter". I have those checked so that the users
can hide, insert and expand the rows and columns and use the filter.

In addition to those items, again, if I have the Edit objects box checked,
not only the comment would show, you can even edit it. If I don't have it
checked, the comment would not show at all, but you can see the comment
indicator in the cell. When I move the mouse over to the cell, it turns into
a hand pointer.

In the Format Comment, both lock boxes are checked.

Thanks again.

 

[Dave Peterson]

I could still see the comment when I did what you did.

But since you have AutoFilter checked, I bet that the comment is showing--it's
just way off the screen.

If you show all the data, I bet it shows up closer to the cell.

Debra Dalgleish has a way to reposition them when they move where you don't
want them:

http://contextures.com/xlcomments03.html#Reset

 

[Guest]

Thanks Dave.

After playing around with it, I found a way to ask the users to see it. I
didn't check the Edit objects box, and I checked both lock boxes in the
Format Comment. When I move the mouse over, it still would not show it.
Then what I did was, I right-click on one of the headers (the one with a
comment) and click on Show/Hide Comments from the drop-down menu. At that
time, it showed the comment.

I guess, I can just ask the users to do the same way if they want to see the
comments.

Thanks again.

 

[mevetts]

Just another comments related query. Is there a way of getting comment
to appear on the left rather than the right.

I have info in all the columns on the screen with comments in te mos
right hand column. When I mouse over them the appear, but are off th
screen. If I reposition the comment to the left, it only pops up ther
if I want to edit it, but still pops up on the right on mouseover.

Is there a way around it, has Micro$oft just missed a trick here? Seem
a bit of an oversight if they have.

Cheers,

Mark

 

[Dave Peterson]

You can use that code to reposition the comment, but I don't think you can do
anything to make it show on the left by default.

 

[mevetts]

Do you mean on the link to Debra Dalgleish's codes?

Which one should I use

 

[Dave Peterson]

Yep. From earlier in this thread:

Debra Dalgleish has a way to reposition them when they move where you don't
want them:

http://contextures.com/xlcomments03.html#Reset

 

[mevetts]

Sorry Dave, I think you must have misunderstood me. I want a way of
making the stay in the position I place them. If I move a comment to
the left of a cell, the next time I mousover the cell the box pops up
on the right again!

 

[Dave Peterson]

I gotcha.

From an earlier post in this thread:

You can use that code to reposition the comment, but I don't think you can do
anything to make it show on the left by default.

 

[mevetts]

Which code? Can't see which would be appropriate?

 

[Dave Peterson]

http://contextures.com/xlcomments03.html#Reset

From Debra's Site:
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

Adjust the +5 to -20 (or whatever works best).

 

[mevetts]

Will this be applied to all comments, or just if I move them? I onl
want the comments in the column on the very right of my screen to b
moved, the rest are fine.

Thanks,

Mark

 

[Dave Peterson]

I was thinking/hoping that this would work, but it didn't for me:

Option Explicit
Sub ResetComments()
Dim cmt As Comment
With Worksheets("sheet99")
For Each cmt In .Comments
If Intersect(cmt.Parent, .Range("o")) Is Nothing Then
'do nothing
Else
cmt.Shape.Top = cmt.Parent.Top - 5
cmt.Shape.Left = cmt.Parent.Left - 25
End If
Next cmt
End With
End Sub

But the comment was repositioned if I explicitly showed it.