How to scroll/position current view to certain spot?

[KC Cat]

Hello to all,

I am trying to learn how to control how the screen scrolls, or, is scrolled.

I have a workbook that has a worksheet that has several different custom views that I have created. These different custom views hide/unhide columns based on the view. I have form buttons with simple macros that reset the view according to which button is clicked. In each view, the Window / Freeze Panes function has been used to anchor the top 3 rows and left 7 columns. These 7 columns are visible in each view - the only columns that get hidden/unhidden are to the right of this.

The trouble I'm experiencing is that when the user clicks a view button, it is resetting the screen so that row 4 is shown at the top, even if they were looking at row 253, for example. I know why this is happening. It's because that's how the viewed looked when I saved the custom view. My code is selecting the correct row (or cell actually), but it's always off the screen to the bottom (assuming the active cell was scrolled to off the bottom prior to clicking on of the buttons).

What I'd like it to do is apply the custom view they choose, but not have the rows on the screen change (not move, up or down). Or, at least appear as though it had not changed. In other words, they would not notice any change to how the view looks, except for the hiding/unhiding of columns based on the view they choose.

I hope I'm making sense. Here's the code from a typical button macro:

Sub View_Show_Cost_Summary()

Application.ScreenUpdating = False

Dim rowVar As Integer
rowVar = ActiveCell.Row

ActiveWorkbook.CustomViews("Unit / Cost Summary").Show
Range("CurrentViewStatus") = "View = Unit / Cost Summary"

Cells(rowVar, 1).Select

Application.ScreenUpdating = True

End Sub


Thanks in advance for considering my question!

 

[sijpie]

Well to start off:

Application.ScreenUpdating = True
Cells(rowVar, 1).Select

will help - you are selecting the cell to go to while screen updating is false, so the screen won't move to where you want it.

But to get it completely correct your code would need to determin the rownumber of the first visible row in the scrolling area, and put that row back there.

I am not sure how to achieve that.

But atleast the above minor change will get the selected cell back inot the user's view.
(Mind you - if the user had just scolled down without selecting a cell in the scrolled down view, his previous selection would become visible)