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

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

--
Gerd


"AVR" wrote:

> 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
>

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.

"AVR" wrote:

> 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
>

>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.

--
Regards,
Tom Ogilvy


"JLGWhiz" <(E-Mail Removed)> wrote in message
news:FEAE382B-7DCF-425E-9AA7-(E-Mail Removed)...
> 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.
>
> "AVR" wrote:
>
>> 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
>>