Excel 2000

Is there any VBA statement or simple code to scroll the active cell to
the center of the window? (the active cell remains the same address)
(or even a toolbar button to do this?)

There are a number of VBA scrolling commands in the help, but they
seem to be of the sort that, e.g., "take Row #n and make it the
top/first row visible in the window." That doesn't seem to be very
useful directly, and one would seem to have to do a lot of
"calculating" to make such a command perform the desired function.

With rows, setting n = current row - 5 or so would probably be "good
enough," but for columns, because of their highly variable width, such
an approach would likely fail miserably much of the time.

Thanks in advance.

Fred Holmes

Hi Fred:

Sub center_it()
Application.Goto reference:=ActiveCell, scroll:=True
With ActiveWindow
i = .VisibleRange.Rows.Count / 2
j = .VisibleRange.Columns.Count / 2
..SmallScroll Up:=i, ToLeft:=j
End With
End Sub
--
Gary''s Student - gsnu2007a


"Fred Holmes" wrote:

Excel 2000

Is there any VBA statement or simple code to scroll the active cell to
the center of the window? (the active cell remains the same address)
(or even a toolbar button to do this?)

There are a number of VBA scrolling commands in the help, but they
seem to be of the sort that, e.g., "take Row #n and make it the
top/first row visible in the window." That doesn't seem to be very
useful directly, and one would seem to have to do a lot of
"calculating" to make such a command perform the desired function.

With rows, setting n = current row - 5 or so would probably be "good
enough," but for columns, because of their highly variable width, such
an approach would likely fail miserably much of the time.

Thanks in advance.

Fred Holmes

Works like a charm! Many thanks. Fred Holmes

On Fri, 16 Nov 2007 13:17:01 -0800, Gary''s Student
wrote:

Hi Fred:

Sub center_it()
Application.Goto reference:=ActiveCell, scroll:=True
With ActiveWindow
i = .VisibleRange.Rows.Count / 2
j = .VisibleRange.Columns.Count / 2
.SmallScroll Up:=i, ToLeft:=j
End With
End Sub