VBA to Display comments in centre of active window

[ccameron]

Hi guys,
I got a piece of code from webpage that claims it show the selected comment in the centre of the active window. I have tried to use the code in a workbook I am using but it does not seem to work for me. Not sure if its me or the code.
using Excel 2007

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'www.contextures.com/xlcomments03.html
Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim sh As Shape

Application.DisplayCommentIndicator _
= xlCommentIndicatorOnly

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

Any help would be appreciated.

Thanks in advance
Regards ccameron

 

[AlphaFrog]

Make sure the code is placed in the proper location. The code goes in a worksheet module. Right-click on the sheet tab that you want this to work on and select View Code from the pop-up menu. Then paste this code in the VBA edit window.

Then back in Excel on that same sheet, select a cell that has a comment.

 

[ccameron]

Thanks AlphaFrog,
I will give it a go

 

[ccameron]

Sorry AlphaFrog,
It still is not working any suggestion would be very helpfull and very much appreciated.

Thanks guys.

 

[Jaafar Tribak]

Sorry AlphaFrog,
It still is not working any suggestion would be very helpfull and very much appreciated.

Thanks guys.

the code provided should work unless the you want the comments to be relocated without selecting the cells. ie : just by hovering the mouse over the cell .

 

[ccameron]

Thanks Jaafar,
You are right, but it only works if you run the macro. Can it be changed so that when you select the cell it automatically shows the comment. Other people that will need to use this workbook only know very basic excel and would not know how to run a macro.

Any help would be fantastic.

Thanks everyone for your help.

ccameron

 

[Jaafar Tribak]

Thanks Jaafar,
You are right, but it only works if you run the macro. Can it be changed so that when you select the cell it automatically shows the comment. Other people that will need to use this workbook only know very basic excel and would not know how to run a macro.

Any help would be fantastic.

Thanks everyone for your help.

ccameron

That's exactly what AlphaFrog's code does.

 

[ccameron]

That's exactly what AlphaFrog's code does.

Thanks for the reply Jaafar,
What I am trying to do is to be able to select any cell with a comment, and have that comment display in the centre of the active window. The code I have only does this if I manually go into macro's and run that particular macro each time I select a cell. What I would like it to do is to be able to select a cell with the comment and have it automatically display in the centre of the active window without having to manually run the macro.

 

[AlphaFrog]

Again, do exacty this and you will get a macro that automatically runs when you select a cell that has a comment.

Make sure the code is placed in the proper location. The code goes in a worksheet module. Not a standard module.

Right-click on the sheet tab that you want this to work on and select View Code from the pop-up menu. Then paste this code in the VBA edit window.

Then back in Excel on that same sheet, select a cell that has a comment.

Use this exact code below. Do not change it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'www.contextures.com/xlcomments03.html
Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim sh As Shape

Application.DisplayCommentIndicator _
= xlCommentIndicatorOnly

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

This is an event macro that will automatically run when you select a cell. It has to be located in the worksheet module.

 

[ccameron]

Thanks AlfaFrog and Jaafar,
I finnaly got it to work thanks to you guys, sorry for being a pain but I am still fairly new to macros and VBA and get a little confused at times.

Thanks again I really do appreciate the help.

ccameron