Move/Scroll Specific Cell(Row) into the centre of the Screen ?

[Corey]

I am having Application.ScreenUpdating=TRUE used so i can SEE the DATA
behind a Userform.
But i am currently Selecting a specific Cell in Column A and Offseting it to
Offset(25,0) to try to get it to Be in the Center of the Screen.

But it does not seen to work real well according to where the rows were LAST
positioned.
Is there a code to Position a Row/Cell in the Centre of the Screen.
I need the user to be able to SEE the values in Column C in relation to the
Value in Column A used as the Offset Cell.

Corey....

 

[Ken Johnson]

I am having Application.ScreenUpdating=TRUE used so i can SEE the DATA
behind a Userform.
But i am currently Selecting a specific Cell in Column A and Offseting it to
Offset(25,0) to try to get it to Be in the Center of the Screen.

But it does not seen to work real well according to where the rows were LAST
positioned.
Is there a code to Position a Row/Cell in the Centre of the Screen.
I need the user to be able to SEE the values in Column C in relation to the
Value in Column A used as the Offset Cell.

Corey....

Hi Corey,

I control a cell's position by manipulating the active window's
scrollrow and scrollcolumn properties.

Try out this little bit of code, which might give some clues for
solving your problem...

Public Sub CentreActiveCell()
On Error Resume Next
With ActiveWindow
..ScrollRow = ActiveCell.Row - _
Int(.VisibleRange.Rows.Count / 2)
..ScrollColumn = ActiveCell.Column - _
Int((.VisibleRange.Columns.Count - 1) / 2)
End With
On Error GoTo 0
End Sub

If the window has A1 in the top left corner and the active cell is in
the top-left screen quandrant, then it is not possible to centre the
active cell so the code does nothing, hence the On Error Resume Next.
Also, under the same condition, if the active cell is in the
bottom-right screen quadrant then the code can only raise the active
cell to centre it, it can't move it sideways towards the centre. And,
of course, again with A1 in the top-left corner of the window, an
active cell in the top-right screen quadrant can only me moved sideways
towards the centre but not down.

Ken Johnson

 

[Ken Johnson]

Hi Corey,

I'm still stuffing things up!

Also, under the same condition, if the active cell is in the
bottom-right screen quadrant then the code can only raise the active
cell to centre it, it can't move it sideways towards the centre.

Should have said...

Also, under the same condition, if the active cell is in the
bottom-LEFT screen quadrant then the code can only raise the active
cell to centre it, it can't move it sideways towards the centre.

Ken Johnson

 

[Corey]

Absolutely PEFECT Ken.

Thank You


Corey....

Hi Corey,

I control a cell's position by manipulating the active window's
scrollrow and scrollcolumn properties.

Try out this little bit of code, which might give some clues for
solving your problem...

Public Sub CentreActiveCell()
On Error Resume Next
With ActiveWindow
.ScrollRow = ActiveCell.Row - _
Int(.VisibleRange.Rows.Count / 2)
.ScrollColumn = ActiveCell.Column - _
Int((.VisibleRange.Columns.Count - 1) / 2)
End With
On Error GoTo 0
End Sub

If the window has A1 in the top left corner and the active cell is in
the top-left screen quandrant, then it is not possible to centre the
active cell so the code does nothing, hence the On Error Resume Next.
Also, under the same condition, if the active cell is in the
bottom-right screen quadrant then the code can only raise the active
cell to centre it, it can't move it sideways towards the centre. And,
of course, again with A1 in the top-left corner of the window, an
active cell in the top-right screen quadrant can only me moved sideways
towards the centre but not down.

Ken Johnson

 

[Ken Johnson]

You're welcome Corey.
I'm glad to be of assistance to such an industrious VBA worker.

Ken Johnson