Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Center Comment Over Selected Cell
I found this useful bit of code on
http://www.contextures.com/xlcomments03.html With this code, comments are displayed in the center of the active window's visible range. I have tried to modify this code to center the comments over the selected cell. I am not very good with VB in Excel, and I have not been able to make the necessary modification. Can someone please give me the code that would allow me to do what I want. 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 = 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Center Comment Over Selected Cell
If you change the range reference to the active cell, it should centre
the comment over that cell: Set rng = ActiveCell inthepickle wrote: I found this useful bit of code on http://www.contextures.com/xlcomments03.html With this code, comments are displayed in the center of the active window's visible range. I have tried to modify this code to center the comments over the selected cell. I am not very good with VB in Excel, and I have not been able to make the necessary modification. Can someone please give me the code that would allow me to do what I want. 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 = 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Center Comment Over Selected Cell
Thanks Debra. I knew it would be something really simple.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Center Comment Over Selected Cell
You're welcome!
inthepickle wrote: Thanks Debra. I knew it would be something really simple. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Center Comments on Center of Screen | Excel Discussion (Misc queries) | |||
Merge and Center and Center Accross Selection | New Users to Excel | |||
displaying a comment only when the cell is selected | Excel Discussion (Misc queries) | |||
displaying a comment only when the cell is selected | Excel Discussion (Misc queries) | |||
Center Across Selection Vertically Help. I am trying to center te. | Excel Discussion (Misc queries) |