Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
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 wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
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 "Duncan" wrote: 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 wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
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" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
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/Appl..._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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
This code looks like it will work with some tweaking. Thanks for the help it
is greatly appreciated! Jeff "NickHK" wrote: 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/Appl..._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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
Nick,
It looks good except for the "GetCellUnderCursor". Is that limited to a newer version(s) of Excel? -- Mike "NickHK" wrote: 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/Appl..._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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments & Scrolling
Mike,
"GetCellUnderCursor" is not part of Excel. It is a custom function show on that link provided, about 2/3 the way down. NickHK "MNTye" wrote in message ... Nick, It looks good except for the "GetCellUnderCursor". Is that limited to a newer version(s) of Excel? -- Mike "NickHK" wrote: 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/Appl..._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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2000 how to format the comments font all comments | Excel Discussion (Misc queries) | |||
Excel- smooth scrolling (instead of 'snap' scrolling) | Setting up and Configuration of Excel | |||
Vertical scrolling...jumps rather than smooth scrolling | Excel Discussion (Misc queries) | |||
Live Scrolling/Real-Time /Smooth Scrolling doesn't work for me in Excel, even 2007 beta | Excel Discussion (Misc queries) | |||
Live Scrolling/Real-Time /Smooth Scrolling doesn't work for me in Excel 2003 | Excel Discussion (Misc queries) |