no scrolling after an autofilter off following a custom view

[Allan Seidel]

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

 

[Jim Rech]

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

 

[David McRitchie]

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/lastcell.htm#makelastcell

Inserting rows makes the worksheet larger but deleting rows may not
reduce the worksheet size with Excel remembering its larger size.

 

[Allan Seidel]

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]

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.

 

[David McRitchie]

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.

 

[TimbreWulf]

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

Hi Alan,
Did you get an answer to this as I have just encountered the same problem with a tool that I have written in VBA.