Comments & Scrolling

[Jeff M]

I have an excel workbook with comments on all items in column A.
I have a screen split between column A & B so that I can scroll through the
columns keeping column A visible.
Unfortunately when I scroll too far to the right the comments no longer are
visible.
(ie When I scroll to column N I can still see column A, but even with the
mouse on column A the comments are not visible, until I scroll back to the
left)

I am looking for a VBA macro that I can use to fix this problem.
I suspect it will use the .scrollcolumn property but frankly I am stuck.

 

[Duncan]

Jeff,

Just in case a monkey-fix might do,...you can drag the comments box to
wherever you want, just bear in mind that ALL comments will pop up in
that place afterwards

Duncan

 

[Jeff M]

Thanks Duncan,
Unfortunately there is no single place on the spreadsheet where I can drag
the comments to make them visible to all the columns. The spreadsheet is
close to 100 columns wide.

Jeff

 

[NickHK]

Jeff,
maybe you can tailor this to your needs:
With Range("A1")
.Comment.Shape.Left = .Left + .Width
.Comment.Visible = True
End With

NickHK

 

[Jeff M]

Thanks Nick,
unfortunately this does not appear to fix my problem.
I have discovered that if I programatically place the comments where I want
them and make them all visible they appear where I want them, except then
when I mouse over the cells that the comments are tied to the cells snap back
to the default position. (Which is not where I need them)

Jeff

 

[NickHK]

Jeff,
I have had similar problems with comments when there is insufficient space
to show the comment in the same pane as the cell.
A possible solution is to included a blank column in the same pane a column
A.

Otherwise, if this is important to you, you'll have to resort to checking
where the mouse is and manually control comments.
Unfortunately, there no mouse coordinates exposed on the worksheet by Excel,
but you can fake it with something based on :
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21908962.html
These calculations seem a little off, as sometimes the wrong cell is
returned.
As for dealing with the comments, seems to work, somewhat:

Public mTimerActive As Boolean
Public Sub CheckTimer()
Dim Line As Shape
Dim ResetRequired As Boolean
Dim CurrentCell As Range
Dim CommentLeft As Single

Set CurrentCell = GetCellUnderCursor

If Not mLastActiveCell Is Nothing Then
If CurrentCell.Address <> mLastActiveCell.Address Then
'Hide the comment old comment
With mLastActiveCell
If Not .Comment Is Nothing Then .Comment.Visible = False
End With
End If
'Need to update in 2 cases:
'1 - New cell
'2 - Same cell but pane has scrolled
With CurrentCell
If Not .Comment Is Nothing Then
'Should determine which is the Pane to the right of your
comments panes,
'rather than hard code a number
CommentLeft = ActiveWindow.Panes(2).VisibleRange.Columns(1).Left
.Comment.Shape.Left = CommentLeft
.Comment.Visible = True
End If
End With

End If

Set mLastActiveCell = CurrentCell

mNextTimerTime = Now + TimeSerial(0, 0, 1)
Application.OnTime mNextTimerTime, "CheckTimer", , mTimerActive
End Sub

With all the above code in a .bas.
And in a worksheet module:
Private Sub CommandButton1_Click()
mTimerActive = Not mTimerActive
Call CheckTimer
End Sub

The could change the .OnTime for a class based timer, but either way need to
add error handler for situation when timer code is interupted etc.
Not perfect by any means, but it may help you in the right direction.

NickHK

 

[Jeff M]

This code looks like it will work with some tweaking. Thanks for the help it
is greatly appreciated!

Jeff

 

[MNTye]

Nick,
It looks good except for the "GetCellUnderCursor". Is that limited to a
newer version(s) of Excel?

 

[NickHK]

Mike,
"GetCellUnderCursor" is not part of Excel. It is a custom function show on
that link provided, about 2/3 the way down.

NickHK