New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Scroll the Active Cell into view

Free Excel Macros

Scroll the Active Cell into view

Postby Sisyphus » Sun Apr 15, 2012 4:11 am

Whenever you select a cell on a worksheet the general purpose is to let you either read from or write to it. So much nicer it would be if the cell would happen to be in an area of the sheet that is also visible on your screen. Here is a procedure that moves any cell you specify to the topmost left corner of your screen.
Code: Select all
Public Sub ScrollView(Optional ByVal R As Long, _
                      Optional ByVal C As Long = 1, _
                      Optional ByVal Vmargin As Long, _
                      Optional ByVal Hmargin As Long)
    ' scroll the active cell into view
    ' Vmargin = visible rows above R
    ' Hmargin visible columns to the left of C
   
    With ActiveWindow
        With .ActiveCell
            R = IIf(R, R, .Row) - Vmargin
            C = C - Hmargin
            If R < 1 Then R = .Row
            If C < 1 Then C = 1
        End With
        .ScrollRow = R
        .ScrollColumn = C
    End With
End Sub
The procedure has four arguments, all of them optional. If you call it without any parameters the first cell, that is column A, of the row in which the current ActiveCell is located is the one to be moved. ActiveCell is the first cell in the currently selected range.

However, you don't need to select a cell in order to effect its movement. The first two parameters are
R for Row and C for Column. By specifying these two values you can elect any cell to the place of honor without selecting it. For example the following function call will move the cell in the 28th column of row row 420, irrespective of which cell is currently active:
Code: Select all
ScrollView 420, 28
The 28th column is column AB (Z = 26).

Excel automatically adjusts the topmost left corner of the screen to exclude any rows or columns marked by the Freeze Panes instruction. If you have 2 header rows and column A frozen to be always visible this procedure doesn't over-ride that instruction. Excel adjusts the screen so that the frozen areas aren't counted as part of it. Executing ScrollView would move the elected cell to the position of B3 which it would consider the "topmost left" corner of your screen, given the described settings. However, you may prefer to set a larger margin or to set a margin without freezing any panes. For this purpose you may set the other two parameters, Vmargin as Vertical margin and Hmargin as Horizontal margin. These two values, if set, determine how many rows will be visible above and how many columns will be visible to the left of the elected cell.

Parameters must be fed to a function in the sequence of its arguments. In the case of ScrollView the sequence is R, C, Vmargin, Cmargin. Therefore, using the abbreviated syntax, you can't specify the margins without also specifying row and column. If that isn't what you would like to do named arguments must be passed. This function call specifies both margins, but not R and C:
Code: Select all
ScrollView Vmargin:=5, Hmargin:=17
Note that the sequence of named arguments is without significance.

Of course, you can pass calculated values in place of absolute numbers. The procedure will automatically adjust to any errors that may occur in their calculation. If the row number, after adjustment for Vmargin, should be smaller than 1 no vertical movement will be carried out. Similarly, if the calculated column number should be smaller than 1 column A will be scrolled into view. Since you have this functionality in the procedure you need not include safeguards against it in your functions that do the calculating.

By the way, ScrollView works on the ActiveWindow. If your code switches between windows make sure to Activate the correct window before calling the function.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 26 guests