• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to reposition cell comments?

Target

Target

New Member
I have a worksheet with half a dozen cells containing comments. Unfortunately they are all in the bottom left hand corner (of the visible window) and the majority of the comments display off screen.

I've been hunting for a means of forcing them to display on screen without success. I did find an on-change event routine, but I'm trying to do this without having to select a cell...

any idea's?
 
Correct me if I'm wrong but wouldn't that event only fire once (when the worksheet is activated)?

The quoted code is for the worksheet.change event, ie it's triggered by a cell selection

It works a treat when you select a cell, but on mouse over the default action fires and the comment is displayed off screen...
 
as requested, dummy data, but the comments are in the right place...

Monitor size will have an impact (I'm using a 22" display)
 

Attachments

  • comments_test.xlsm
    13.9 KB · Views: 10
on further investigation it appears this may not be possible, ie it seems you can't trap the default 'mouse-over' event that displays cell comments, and/or that you can only adjust the position of the comment box when its displayed permanently (using show/hide)

I'm wondering now if there's a means of identifying a given shapes status, eg visible = true/false and operating on it that way? Identifying the relevant shape would be the next challenge (my simple worksheet reported >2000 shapes in the collection)
 
yes, but see the screenshot.

The solution you posted works when you select a cell, but when you mouseover (see the screenshot) the cell comment defaults to the right and below the cell (and in this case, off the screen)
 

Attachments

  • Screenshot - 14_08_2014 , 15_13_37.png
    Screenshot - 14_08_2014 , 15_13_37.png
    250.2 KB · Views: 5
Hi ,

That is because there is no mouseover event for a worksheet cell.

Jordan Goldmeier has discovered a rollover technique ( http://optionexplicitvba.blogspot.co.nz/2011/04/rollover-b8-ov1.html ) , but this is based on the fact that the cell can contain a customized formula ; without this formula , the rollover technique will not work.

If you are using the commented cells for any other purpose , then I do not know of any way to do what you want.

Narayan
 
sorry for not coming back sooner, work got in the way...

how about a routine that scans for a visible shape?

if you can't detect/trap a given event you should be able to detect an objects status (ie visible= true/false).

I've tried but clearly I'm missing something 'cos it basically locked up excel (by consuming all available resources?)
 
Hi ,

Let me make myself clear.

You want that the comment should appear in a position where it is fully visible. This much is clear.

The issue is when / how ?

Do you want this to happen when you mouseover the cell ?

Do you want this to happen when you select the cell ?

Do you want this to happen when you activate the sheet tab ?

Other than the above 3 events , I cannot think of any other ? Can you ?

Once we have the complete list of possible situations , we can see which ones can be trapped so that a routine runs in that situation.

The last possibility is that you manually run the routine to do what ever is required ; in this case , the routine can prompt you to either select the relevant cell by using the cursor movement keys / the mouse , or it can prompt you to enter the cell address. Once this input has been given , the routine can properly position the comment associated with the desired cell in the active window.

Narayan
 
Thank you for your time and patience, and apologies if I haven't made myself clear with this one (I'm sure you appreciate that it's difficult to know how much detail to include)

the situation I have is this - I have a very simple workbook that I send to a client on a monthly basis (lets call it a dashboard for want of a better term).

It contains a handful of tables and a single graph, all of which fits comfortably within (my) 21" screen. Given that there is no content outside of whats on screen I would like to be able to freeze the pane so that the user cannot navigate away from the relevant area. The problem here is that there are 6 cells close to the bottom of the screen that include multiline cell comments, and because of the length of the comments (it's a small list) when they display (via the default 'mouse-over event') they are partly off screen.

Now I could resolve this by not freezing the pane, but the user would still need to scroll down/across to display the comment in full. Or I could give the client instructions on how to show/hide the comments.

unfortunately neither of these options seems terribly neat or efficient for what seems (or seemed!!) like a fairly simple requirement. When doing this stuff I always try to cater for the lowest common denominator, so simple and intuitive is the name of the game

Ideally what I'm looking for is a means of identifying when a given comment is visible and moving it to a predefined position. FWIW the event that makes it visible is probably irrelevant for the sake of the exercise, so long as the end user can see the comment in it's entirety.

I can identify the shape name, but I don't know how to monitor the status of the shape without hamstringing excel. As you have already pointed out we can't trap the 'mouse-over' event, but we should be able to 'see' the result, and if we can see the result that should (in my mind :)) be sufficient to act on.

Does that help?
 
Can you manually move all the comments now, so that they would be in top-left corner? Or do your comments keep moving?

Another idea would be to use the SelectionChange event to cause a shape/bubble/pop-up to appear with your intended comments. See attached, and select one of cells in Q30:V30 to see what I mean.
 

Attachments

  • Shape Test.xlsm
    23.1 KB · Views: 4
if you 'force' the comments to show they can be manipulated no problem, but that kind of defeats the purpose (by obscuring the worksheet). Moving the comments in this state is fine, but if you hide them again the default 'mouse-over' event fires and displays the comments in the 'default' location.
 
Hmm. Could use a Workbook_Open or Worksheet_Select event to call a comment mover macro, making sure they get reset each time. For example, here's a macro I use to reset my comments when they get screwed up due to freezing panes/filtering/etc.
Code: 
Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
   cmt.Shape.Top = cmt.Parent.Top + 5
   cmt.Shape.Left = _
      cmt.Parent.Offset(0, 1).Left + 5
Next
End Sub

Positions each comment slightly above and to the right of the cell. We could do:
Code: 
Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
   cmt.Shape.Top = Range("B2").Top
   cmt.Shape.Left = Range("B2").Left
Next
End Sub

Forces every comment to appear at cell B2. So, several options to consider...
 
apologies to all that offered assistance with this one for the delay in responding with my final solution

I ended up using a form and listbox instead of the comments. A little clumsy perhaps but I can position it anywhere I like, and give the user the opportunity to print a hard copy.

once again, many thanks for your time and patience!!
 
You must log in or register to reply here.
Back
Top