I'm trying to position Comments above the cell they relate to. When running
this code, (trying different numbers for Top, Left), the comment only appears
repositioned when I right click the cell and click "Edit Comment", i.e. in
edit mode. Thus, the code isn't working. I need to resolve this today, if
possible. Help, please.

Sub RePosComments()
Dim myCell As Range
Dim myRng As Range
Sheets("Detail").Activate
Range("dAllHeaders").Select

Set myRng = Selection

For Each myCell In myRng.Cells
If Not (myCell.Comment Is Nothing) Then
With myCell.Comment

.Shape.Top = .Parent.Offset(0, 1).Top + 5
.Shape.Left = .Parent.Offset(0, 1).Left - 5
End With
End If
Next
End Sub

Unfortunately there is no way to change where a comment appears when we
hover the mouse over a cell.

--
Jim
"Perico" wrote in message
...
I'm trying to position Comments above the cell they relate to. When
running
this code, (trying different numbers for Top, Left), the comment only
appears
repositioned when I right click the cell and click "Edit Comment", i.e. in
edit mode. Thus, the code isn't working. I need to resolve this today,
if
possible. Help, please.

Sub RePosComments()
Dim myCell As Range
Dim myRng As Range
Sheets("Detail").Activate
Range("dAllHeaders").Select

Set myRng = Selection

For Each myCell In myRng.Cells
If Not (myCell.Comment Is Nothing) Then
With myCell.Comment

.Shape.Top = .Parent.Offset(0, 1).Top + 5
.Shape.Left = .Parent.Offset(0, 1).Left - 5
End With
End If
Next
End Sub

Actually, that's true, you can move the comments. The following code did
work using these numbers and adding a offset to the Top syntax:


Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Offset(0, 0).Top - 60
cmt.Shape.Left = _
cmt.Parent.Offset(0, 0).Left + 5
Next
End Sub


"Jim Rech" wrote:

Unfortunately there is no way to change where a comment appears when we
hover the mouse over a cell.

--
Jim
"Perico" wrote in message
...
I'm trying to position Comments above the cell they relate to. When
running
this code, (trying different numbers for Top, Left), the comment only
appears
repositioned when I right click the cell and click "Edit Comment", i.e. in
edit mode. Thus, the code isn't working. I need to resolve this today,
if
possible. Help, please.

Sub RePosComments()
Dim myCell As Range
Dim myRng As Range
Sheets("Detail").Activate
Range("dAllHeaders").Select

Set myRng = Selection

For Each myCell In myRng.Cells
If Not (myCell.Comment Is Nothing) Then
With myCell.Comment

.Shape.Top = .Parent.Offset(0, 1).Top + 5
.Shape.Left = .Parent.Offset(0, 1).Left - 5
End With
End If
Next
End Sub

I should have added, that the comment must be visible to do this. When
hidden, it apparently snaps back to a default position over which we have no
control.

"Perico" wrote:

Actually, that's true, you can move the comments. The following code did
work using these numbers and adding a offset to the Top syntax:


Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Offset(0, 0).Top - 60
cmt.Shape.Left = _
cmt.Parent.Offset(0, 0).Left + 5
Next
End Sub


"Jim Rech" wrote:

Unfortunately there is no way to change where a comment appears when we
hover the mouse over a cell.

--
Jim
"Perico" wrote in message
...
I'm trying to position Comments above the cell they relate to. When
running
this code, (trying different numbers for Top, Left), the comment only
appears
repositioned when I right click the cell and click "Edit Comment", i.e. in
edit mode. Thus, the code isn't working. I need to resolve this today,
if
possible. Help, please.

Sub RePosComments()
Dim myCell As Range
Dim myRng As Range
Sheets("Detail").Activate
Range("dAllHeaders").Select

Set myRng = Selection

For Each myCell In myRng.Cells
If Not (myCell.Comment Is Nothing) Then
With myCell.Comment

.Shape.Top = .Parent.Offset(0, 1).Top + 5
.Shape.Left = .Parent.Offset(0, 1).Left - 5
End With
End If
Next
End Sub

Perico,
Yes, if you the set ToolsOptionsViewComments to Comments & Indicator, you
can position the comment, but it is always displayed.
If you only want to see the comment when the mouse is over the cell, the you
are stuck with the default positioning.

NickHK

"Perico" wrote in message
...
I should have added, that the comment must be visible to do this. When
hidden, it apparently snaps back to a default position over which we have
no
control.

"Perico" wrote:

Actually, that's true, you can move the comments. The following code
did
work using these numbers and adding a offset to the Top syntax:


Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Offset(0, 0).Top - 60
cmt.Shape.Left = _
cmt.Parent.Offset(0, 0).Left + 5
Next
End Sub


"Jim Rech" wrote:

Unfortunately there is no way to change where a comment appears when
we
hover the mouse over a cell.

--
Jim
"Perico" wrote in message
...
I'm trying to position Comments above the cell they relate to. When
running
this code, (trying different numbers for Top, Left), the comment
only
appears
repositioned when I right click the cell and click "Edit Comment",
i.e. in
edit mode. Thus, the code isn't working. I need to resolve this
today,
if
possible. Help, please.

Sub RePosComments()
Dim myCell As Range
Dim myRng As Range
Sheets("Detail").Activate
Range("dAllHeaders").Select

Set myRng = Selection

For Each myCell In myRng.Cells
If Not (myCell.Comment Is Nothing) Then
With myCell.Comment

.Shape.Top = .Parent.Offset(0, 1).Top + 5
.Shape.Left = .Parent.Offset(0, 1).Left - 5
End With
End If
Next
End Sub