I’m back from our version of summer vacation with the family. We spent most of our time around by the pool. The kids insisted on being in the pool and Mrs. Pig doesn’t want them to die, so apparently, this means I have to sit in the sun and watch them do suicidal aquatic tricks. Needless to say, I’m cooked like a piece of well-done bacon. It’s nice to be back in an environment where the temperature is less than the boiling point of water.
Going through my post-vacation email exercise, I noticed an email from Vaughn. He writes to ask:
“Is there a way I can force the active cell to always show up in the up left hand corner?”
Ok Vaughn, I’m not sure why you would do this, but I could use some blog topics.
I assume that Vaughn wants to mimic the Scroll Lock feature in Excel, but always keep the cursor visible in the upper-left most cell.
The regular Scroll Lock locks your cursor in place, moving only the spreadsheet. But this feature often leaves your cursor in a place where you can’t see it. Vaughn wants the same Scroll Lock behavior, but wants the active cell to remain in the upper-left-hand corner at all times.
This is a fairly easy task. Simply paste this code into the SelectionChange event of the target sheet:
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row
There you go Vaughn. Good luck in all your mysterious Excel processes.
Maybe Vaughn meant he wants to “see” the active cell in the upper left corner, not necessarily scroll to the upper left corner? ‘Cause, yeah, why would you want that?
Just looking at it from a different mindset…
I also think that it would be very distracting when it was happeneing all the time.
However, in a Macro with a Shortcut Key (say, Ctrl+T) it would be very useful.
Then I thought about including your code in a FollowHyperlink event. Wow! What a difference THAT will make to some of my workbooks.
Thanks for the post Mike!
Mike, you can do the same thing with just a single line of code…
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Goto ActiveCell, True
End Sub
Rick: Very nice!