- 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
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
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
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.