Changing view as well as selection from code.

[Guest]

Using something like the following code, I was able to change the selection
in sheets other than the active sheet, transparent to the user. However,
this code does not change the view upon entering the sheets. Is there a way
to shift what will be displayed, comparable to hitting the "Home" key? I.e.,
the selected cell in the top left corner of the view upon entering the sheet.

Sub SetActiveCell()
application.ScreenUpdating = False
set sh = Activesheet
With Worksheets("Sheet B")
.Select
.Range("A1").Select
End with
sh.Activate
Application.ScreenUpdating = True
End sub

 

[Guest]

Hi AVR,

You can use Application.Goto to select and position to the destination cell,
but it also activates the sheet. Just switch back to the current sheet
afterwards.

e.g.
sn = Activesheet.Name
Application.Goto Reference:=Worksheets("Sheet B").Range("A1"), scroll:=True
Sheets(sn).Activate

 

[Guest]

set sh = Activesheet 'If you do this

sh.Activate 'Then this is meaningless

If you want to set Range A1 in the top left corner
of your screen when you change sheets, Then
Sheets(2).Activate
Range("A1").Activate
Sheets(3).Activate
Range("A1").Activate

Or to make it so that it will open on that view with code
or manually you can use the SheetActivate event in the
Worksheet code module with this in each worksheet.

Private Sub Workbook_SheetActivate( )
Range("A1").Activate
End Sub

To enter the code snippet, just right click the sheet tab,
then click "View Code" and paste the snippet into the
module and save.

 

[Tom Ogilvy]

set sh = Activesheet 'If you do this

sh.Activate 'Then this is meaningless

Try reading the code again. In between, another sheet is selected. Thus
the sh.Activate returns to the original sheet.

If you want to set Range A1 in the top left corner
of your screen when you change sheets, Then

His code already does that, except select insures only one cell is selected.
Activate on a range doesn't insure that.