MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

ActiveWindow and ActiveCell

  Asked By: John    Date: Nov 26    Category: MS Office    Views: 1512

How to automatically scroll the window such that the activecell is
in the middle of the screen?



1 Answer Found

Answer #1    Answered By: Helene Stewart     Answered On: Nov 26

There might be an official way to do this without VBA but I don't
know it, but something along these lines in the worksheet code area

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MiddleRow = 22
MiddleColumn = 9
TopRow = ActiveCell.Row - MiddleRow
If TopRow < 1 Then TopRow = 1
Leftcolumn = ActiveCell.Column - MiddleColumn
If Leftcolumn < 1 Then Leftcolumn = 1
ActiveWindow.ScrollColumn = Leftcolumn
ActiveWindow.ScrollRow = TopRow
End Sub

Adjust values of MiddleRow to be the number of rows between the top
and middle  rows on your sheet, likewise with MiddleColumn being the
number of columns from the left to the middle.
This gets cocked up when you change column widths and row heights or
if these vary significantly within the sheet(I fancy one could write
a macro to take account of row heights and column widths).
Of course you can't have cell A1 in the middle of the screen  (can

Didn't find what you were looking for? Find more on ActiveWindow and ActiveCell Or get search suggestion and latest updates.