VBA to open & centre comment on cell selection

[men5j2s]

hi,

The current code i have to make this work is as follows, but it is clunky and causing the worksheet to be incredibly slow.

Any ideas if i can achieve the same outcome in a simpler way?

I want column H to have cells that i can click and a comment box will instantly open up in the centre of the screen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim Sh As Shape


Application.DisplayCommentIndicator _
= xlcommentIndicator0nly


Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2
cWidth = rng.Left + rng.Width / 2


If ActiveCell.Comment Is Nothing Then
'do nothing
Else
Set cmt = ActiveCell.Comment
Set Sh = cmt.Shape
Sh.Top = cTop - Sh.Height / 2
Sh.Left = cWidth - Sh.Width / 2
cmt.Visible = True
End If

End Sub

 

[svendiamond]

Add this to the beginning so it only works for column H:

If Not Intersect(Target, Range("H:H")) Is Nothing Then

'your code

End If

 

[men5j2s]

Add this to the beginning so it only works for column H:

If Not Intersect(Target, Range("H:H")) Is Nothing Then

'your code

End If

Hi,

Thanks for the suggestion, this instantly helps navigation around other cells, but then as soon as i select a cell in H:H then the same issue occurs. Also with this modification the Comment does not disappear when i select another cell.

 

[svendiamond]

Editing the shape is probably what takes so long... try stepping through the code and see how long each line takes to execute. Also, as far as the comment not disappearing when you select another cell, put this line:

Application.DisplayCommentIndicator _
= xlcommentIndicator0nly

before the "If not intersect" part, that way no matter where you click the comment will be hidden

 

[My Aswer Is This]

I would be careful using this code. While testing this it turned off my comments. I had to find the code to turn them back on.

Application.DisplayCommentIndicator _
= xlcommentIndicator0nly

 

[men5j2s]

Editing the shape is probably what takes so long... try stepping through the code and see how long each line takes to execute. Also, as far as the comment not disappearing when you select another cell, put this line:

Application.DisplayCommentIndicator _
= xlcommentIndicator0nly

before the "If not intersect" part, that way no matter where you click the comment will be hidden

That code was already in the script and it was that code that was causing the issue (as discovered by stepping through.

The following code works well, but the comments never leave, they just overlay each other. there must be a way of sorting this?

the bizarre thing is that this used to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim Sh As Shape


Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2
cWidth = rng.Left + rng.Width / 2


If ActiveCell.Comment Is Nothing Then
'do nothing
Else
Set cmt = ActiveCell.Comment
Set Sh = cmt.Shape
Sh.Top = cTop - Sh.Height / 2
Sh.Left = cWidth - Sh.Width / 2
cmt.Visible = True
End If


End Sub