Freeze/scroll/shared/filter problem

[ROGERL2008]

Excel 07. We have a workbook with multiple sheets which we are encountering a problem with. The workbook itself is actually in the small to medium size range, having 5 worksheets. It is a shared workbook and is accessible to many people. The problem we are encountering seems to be related to individuals saving the workbook with filters in place on various columns. When the workbook is opened with no filters on it is as though the screen becomes locked up and though any data can be selected with mouse clicking, the screen can no longer be scrolled, as though the scroll lock is on. When filters are put in place, thereby greatly cutting down on the number of lines now visible, one can again manage to navigate to a greater degree, though still not perfectly freely. Is anyone aware of this problem and/or any patches available from microsoft to correct it?
<!-- / message -->

 

[RoryA]

Shared workbooks are notoriously flaky and prone to corruption and weird behaviour (in all versions), although it sounds to me as though perhaps frozen panes are being put in place after filters have been applied. Then when the filters are changed, the frozen pane actually takes up the whole screen, which is why you can't scroll around, but can still select cells with the keyboard.

 

[krishhi]

Yes, Once we have the same problem. the Problem with the Freeze and the Filters, Just remove everything. It will be fine.

 

[JamesW]

I have the same problem (Excel 2003), so I added this to the open event of the workbook:

    ActiveWindow.FreezePanes = False
    Range("a11").Select
    ActiveWindow.FreezePanes = True

Seems to work so far!

 

[ROGERL2008]

JamesW's macro works great! Thanks, JamesW!

 

[charbama]

I think I found a work around but haven't tested enough to be positive that it works all of the time. When you have the document opened and everything is set the way you want it, go to views, custom view, add. I named mine "z-original" so it would always be at the bottom of the list of views. Hit save and close the workbook, then reopen it (this becomes your personal view). If someone saves and close with filters on or columns hidden and you lose the scroll, you can go to view-custom views-z-original (or whatever you named it) "show" and it will go back to that format. I always get a message that "some settings not applied" but it always works. hit SAVE

Very IMPORTANT, go to back into custom views and delete your personal view, SAVE and EXIT. When you reopen it the "z-original" now becomes your custom view.

Make sure that you are in your "personal view" and not in the "z-original" if you make any changes.

Sorry, I am not an expert by any means but I have been having this issue for years on our budgets and if we unshare it to fix the scroll problem, we lose all the track changes. I am interested to know if this works for others because I am not sure if we have just gotten lucky in our timing of when we save things.

 

[lily liu]

Thanks JamesW, your method is good.