How to scroll sheet to reveal active cell?

[General Ledger]

Dear All,

Using VBA in Excel 2003, how can I make the sheet scroll to show the active cell on the screen?

For example, I have a form on a sheet. When the user gets to the bottom of the form, the top rows have scolled off the screen. This is fine. Then a macro is run and cell B3 is made active. How can I make B3 active and scroll the screen to display B3?

Thanks,

GL

 

[xenou]

First be sure to activate the sheet in question (you can't activate cells unless the sheet is also active):

Worksheets("Sheet1").Activate

then simply activate the Cell:

Range("B3").Activate

You can play with scroll rows if you like - just turn on the macro recorder and jump around a bit, entering some data here and there. You'll see how the macro recorder handles it. But the above is probably enough for your needs. I suppose Application.Goto XXX is also good (I seem to always forget about that option).

ξ

 

[Legacy 98055]

This also might work though I did not try it out myself...

application.goto activecell, true

 

[General Ledger]

So it seems there are options to bring certain cells into view in the Excel window:

1. This code will make B17 the active cell (places cursor in this cell) and scrolls the screen horizontally (left and right) and vertically (up and down) so B17 is the top left cell of the active window. Column A is scrolled off the left side of the window.

Range("B17").Activate
Application.Goto Activecell, True


2. This code will make B17 the active cell (places cursor in this cell) and scrolls the screen vertically (up and down) so row 5 is the top row. Rows 1 through 4 are scrolled off the top of the window. This does not cause the screen to scroll horizontally (left or right). To do that use ActiveWindow.ScrollColumn

Range("B17").Activate
ActiveWindow.ScrollRow = 5


Thanks,

GL

 

[IvanDIP]

What Tom Schreiner wrote is correct.
but if you want to see the left side of your sheet, you can use this:

Application.Goto ActiveCell, True
Range("A" & (ActiveCell.Row)).Show

Ivan