VBA statement "scroll active cell to center"???

  • Thread starter Thread starter Fred Holmes
  • Start date Start date
F

Fred Holmes

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
 
Hello.
I realize this is an ancient thread, but I'm desperate, so, here I go:
Excel 2011 on a Mac:
I used the "center_it" function in an Excel project that I'm writing and it has become the central point of my project.
I have a "control worksheet" where the user will select items from a combobox by scrolling through the items. And as items are highlighted, ranges in two other sheets become selected and are brought into view. This is a great way for my user to get a quick view of all the information she needs.
The problem:
as the items are highlighted and "center_it" is called to bring ranges into view, the userform that the combobox is on loses focus! As a result, the proceeding keystrokes (down arrows) are captured by the second worksheet that was activated and the user has to click back on the combobox to view the next item. Obviously, this is not acceptable for my user.
I have reproduced the problem in a simple set of workbooks here:
Link:
https://www.dropbox.com/s/vaxmh59yhoms7fs/FocusProblem.zip?dl=0

The "ControlFocusProblem.xls" opens up the two workbooks (first and second) and allows you to access the userform by a hotkey.
I have tried this on two other Macs with the same results.
If someone can help me find a solution to this, I'd be grateful.
Thank you.
P.S. Strangely, this does not happen when the sheets are activated from the enter event of a textbox!
 
Back
Top