I am trying to change the position of a comment box.
I can successfully change the position when .visible = True, but when I set .visible = False the comment pops up on mouse-over and is not in the desired position.
Is it possible to change the comment position when the comment is hidden so when it pops up it will be in something other than the default location?
Using Excel 2010 on Windows 7
Cheers,
Rusty
I can successfully change the position when .visible = True, but when I set .visible = False the comment pops up on mouse-over and is not in the desired position.
Code:
Sub Comment_Move_Position()
Dim dbl_X As Double
Dim dbl_Y As Double
With ActiveSheet.Range("J10")
' get position of cell
dbl_X = .Left
dbl_Y = .Top
' format as European date
.NumberFormat = "dd/mm/yyyy;@"
.HorizontalAlignment = xlCenter
' give this cell a comment
.AddComment
With .Comment
' comment text to be added
.Text Text:= _
"Sometimes comments can be useful"
' position comment box based on cell position
With .Shape
' x and y of cell/range were set above
.Left = dbl_X + 100
.Top = dbl_Y - 100
End With
' comment becomes visible on mouse-over when set to False
' when set to True comment remains visible
.Visible = False
End With
End With
End Sub
Is it possible to change the comment position when the comment is hidden so when it pops up it will be in something other than the default location?
Using Excel 2010 on Windows 7
Cheers,
Rusty