Sometimes when you are using Excel VBA you need the screen to always display at the top, left of the sheet. Here is how you do it.
When Excel saves, it saves where you were on the sheet at the time of the save.
When you are building or using a model, this may not be the area you want the user to see when they open the sheet. See the note at the bottom of the post as well regarding the active cell and sheet protection.
You can use the two lines of code below to move what the user sees to the top, left corner of the sheet.
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
These two commands work on the active sheet.
If you have worked with Excel VBA events you could put the two lines inside the Worksheet_Activate event.
Private Sub Worksheet_Activate()
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub
Note: These commands don’t change the active cell. You will still need to set the active cell to a cell in the top, left of the screen using code like. This will also move what the user sees to the top left.
Range(“A1”).Select
Sheet Protection
In a recent project I worked on the sheets were protected and you couldn’t select a cell in the top left, hence the use of the ActiveWindow commands.
You do not need the two ActiveWindow lines of code you posted… executing just the single line of code Range(“A1”).Select (or even Range(“A1”).Activate) will automatically select the cell AND move what the user sees to the top, left corner.
Hi Rick
Yes, that’s right. But if you can’t select/activate a cell because the sheet is protected, then using ActiveWindow works.
I will add a few more sentences on that to the post.
Regards
Neale
Ah, I see why you wrote that now. Yes, a couple more sentences would help. Since I don’t use sheet protection (I’ve been retired for 20+ years now, so no need to do that), I did not think of that restriction.