Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
no scrolling after an autofilter off following a custom view
We have a workbook consisting of a large table with special VBA
automation designed to view the table in a number of different ways as the users might wish. Much of the automation relies on autofilters. Many quirks and bug related to the autofilter has revealed themselves over the years. Now we think we have run into another and we are looking for insights into this. Perhaps it is nothing. A user has saved a custom view. Scrolling stops when using this view followed by turning the autofilter off. When this happens the cell address readout shows chaanges to the active cell location but there is no scrolling going on and the cell contents box remains blank. The right hand elevator bar also turns into a thin line as if the scroll range is very large. (Whoever thought up that user interface concept should be sent to hard labor.) Normal scrolling can be restored either by unfreezing panes (the table is normally frozen just below the table header) or by selecting another restoring custom view we have recently setup to handle this condition. The workbook close and open vba macros invoke the restoring custom view as a quick and dirty patch to handle the situation. We are running Excel 2000. There are a number of ways we could deal with this. What we are really looking for is an understanding of what is happening Is this a known bug or are we missing something? We have dug into Microsoft's Excel knowledge base for a while with no results on this particular issue. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
no scrolling after an autofilter off following a custom view
By "scrolling" can I assume you're referring to the mouse wheel? Well,
naturally I cannot reproduce the problem. As to the scrollbars being tiny (and that is a great UI feature, imo), that would seem to indicate you either have a great deal (many rows) of data, or your "active range" is way screwed up because of data or formatting in the nether regions of your sheet. Whether that has any connection to the scrolling issue I don't know. If you want to send me your workbook and the steps to recreate the problem, I can promise you, at a minimum, some sympathy<g and maybe a solution. Btw, make sure you have the latest drivers available for your mouse. If an MS mouse - http://www.microsoft.com/hardware/mouse/download.asp -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
no scrolling after an autofilter off following a custom view
Hi Alan,
You indicate the scroll bar becomes very small as if the worksheet were very large. Implies that the worksheet is not really that large. Use Ctrl+End to find where Excel thinks the last cell is. Last cell is the intersection of the last used row and the last used column. If the Last Cell is not where it should be you can run a macro after selecting the cell you want to become the last cell. Some people have work areas hidden in remote areas which would be deleted so you have to be careful and know what you are working with. http://www.mvps.org/dmcritchie/excel...m#makelastcell Inserting rows makes the worksheet larger but deleting rows may not reduce the worksheet size with Excel remembering its larger size. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jim Rech" wrote in message ... By "scrolling" can I assume you're referring to the mouse wheel? Well, naturally I cannot reproduce the problem. As to the scrollbars being tiny (and that is a great UI feature, imo), that would seem to indicate you either have a great deal (many rows) of data, or your "active range" is way screwed up because of data or formatting in the nether regions of your sheet. Whether that has any connection to the scrolling issue I don't know. If you want to send me your workbook and the steps to recreate the problem, I can promise you, at a minimum, some sympathy<g and maybe a solution. Btw, make sure you have the latest drivers available for your mouse. If an MS mouse - http://www.microsoft.com/hardware/mouse/download.asp -- Jim Rech Excel MVP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
no scrolling after an autofilter off following a custom view
Thanks, but I'm not sure I'm getting my point across. Again, "When this
happens the cell address readout shows changes to the active cell location but there is no scrolling going on and the cell contents box remains blank." By scrolling I mean the worksheet window remains unchanged when navigating by arrow keys. Nothing moves, period. We see only the table header and what rows were visible at the moment Autofilter is turned off (either by macro or by manual control). This worksheet keeps track of the current table boundary and prevents anyone from editing cells in rows beyond. If there was formatting beyond the table boundary would the scroll bar revert to normal size when when all rows are visible? The condition is almost as if Excel is autofiltering for every worksheet row while at the same time is unable to scroll the window, as if the entire window constitutes the freezepane. Regards, Allan Seidel David McRitchie wrote: Hi Alan, You indicate the scroll bar becomes very small as if the worksheet were very large. Implies that the worksheet is not really that large. Use Ctrl+End to find where Excel thinks the last cell is. Last cell is the intersection of the last used row and the last used column. If the Last Cell is not where it should be you can run a macro after selecting the cell you want to become the last cell. Some people have work areas hidden in remote areas which would be deleted so you have to be careful and know what you are working with. http://www.mvps.org/dmcritchie/excel...m#makelastcell Inserting rows makes the worksheet larger but deleting rows may not reduce the worksheet size with Excel remembering its larger size. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jim Rech" wrote in message ... By "scrolling" can I assume you're referring to the mouse wheel? Well, naturally I cannot reproduce the problem. As to the scrollbars being tiny (and that is a great UI feature, imo), that would seem to indicate you either have a great deal (many rows) of data, or your "active range" is way screwed up because of data or formatting in the nether regions of your sheet. Whether that has any connection to the scrolling issue I don't know. If you want to send me your workbook and the steps to recreate the problem, I can promise you, at a minimum, some sympathy<g and maybe a solution. Btw, make sure you have the latest drivers available for your mouse. If an MS mouse - http://www.microsoft.com/hardware/mouse/download.asp -- Jim Rech Excel MVP |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
no scrolling after an autofilter off following a custom view
Hi Allan,
So you can see the scrollbars but can't scroll. The scroll area can be limited but must be reassigned each time the workbook is opened. The following is an Event macro install by right click on the worksheet tab the View Code and insert the following code. You can reassign scroll area as a demonstration. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If ActiveSheet.ScrollArea < "" Then MsgBox "Scroll area was " & ActiveSheet.ScrollArea _ & Char(10) & "now back to normal" ActiveSheet.ScrollArea = "" Else MsgBox "Scroll area was " & ActiveSheet.ScrollArea _ & Chr(10) & "now is limited to $A$1:$D$4" ActiveSheet.ScrollArea = "$A$1:$D$4" End If End Sub use double-click to invoke the macro for the sheet you installed it in. If you go to ThisWorkBook you will probably see code something like this. Private Sub Workbook_Open() Worksheets("Sheet1").ScrollArea = "$A$1:$Z$100" End Sub My buildtoc.htm page has a macro that shows scrollarea for a worksheet. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Allan Seidel" wrote in message ... Thanks, but I'm not sure I'm getting my point across. Again, "When this happens the cell address readout shows changes to the active cell location but there is no scrolling going on and the cell contents box remains blank." By scrolling I mean the worksheet window remains unchanged when navigating by arrow keys. Nothing moves, period. We see only the table header and what rows were visible at the moment Autofilter is turned off (either by macro or by manual control). This worksheet keeps track of the current table boundary and prevents anyone from editing cells in rows beyond. If there was formatting beyond the table boundary would the scroll bar revert to normal size when when all rows are visible? The condition is almost as if Excel is autofiltering for every worksheet row while at the same time is unable to scroll the window, as if the entire window constitutes the freezepane. Regards, Allan Seidel David McRitchie wrote: Hi Alan, You indicate the scroll bar becomes very small as if the worksheet were very large. Implies that the worksheet is not really that large. Use Ctrl+End to find where Excel thinks the last cell is. Last cell is the intersection of the last used row and the last used column. If the Last Cell is not where it should be you can run a macro after selecting the cell you want to become the last cell. Some people have work areas hidden in remote areas which would be deleted so you have to be careful and know what you are working with. http://www.mvps.org/dmcritchie/excel...m#makelastcell Inserting rows makes the worksheet larger but deleting rows may not reduce the worksheet size with Excel remembering its larger size. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jim Rech" wrote in message ... By "scrolling" can I assume you're referring to the mouse wheel? Well, naturally I cannot reproduce the problem. As to the scrollbars being tiny (and that is a great UI feature, imo), that would seem to indicate you either have a great deal (many rows) of data, or your "active range" is way screwed up because of data or formatting in the nether regions of your sheet. Whether that has any connection to the scrolling issue I don't know. If you want to send me your workbook and the steps to recreate the problem, I can promise you, at a minimum, some sympathy<g and maybe a solution. Btw, make sure you have the latest drivers available for your mouse. If an MS mouse - http://www.microsoft.com/hardware/mouse/download.asp -- Jim Rech Excel MVP |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
no scrolling after an autofilter off following a custom view
Hi Alan,
glad you find my site useful. That was literally stepping back and looking at it from a distance. How did you did you ever discover the unusual high number of rows for freeze panes. "Allan Seidel" wrote ... Thanks for your site David. I used it to get me started in Excel VBA and I go there every once and a while. The no scrolling problem turned out to be a normal Excel function, or should I say pitfall. Apparently the user created their Custom view, which was an autofiltered table segment view deep into the table - say at row 120 , after they had reset the freeze pane. Therefore the freeze was at row 119 instead of at row 6. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
picture disappears from view when scrolling. | Excel Discussion (Misc queries) | |||
View Custom View with Sheet Protection | New Users to Excel | |||
How to view a custom view when the worksheet is protected? | Excel Worksheet Functions | |||
???is there a way to keep one row in view while scrolling down??? | Excel Worksheet Functions | |||
View row headers while scrolling | Excel Discussion (Misc queries) |