Scope and Lifetime confusion.

I am using Excel (98 for Macintosh)as a teachers' markbook with student
names in column A,test results in the other columns and different
classes on different sheets.It is set up so that student's marks for
the first test (in column B) are visible after the sheet is activated
by clicking the sheet tab (each sheet module has "Range("B1").Select"
in the Worksheet_Activate Event Procedure).
I want the option of overriding this behaviour so that clicking a sheet
tab takes you back to the part of the sheet on which you were
previously working i.e; normal between-sheet behaviour.
I am thinking along the lines of a standard Sub Procedure which when
run sets a Boolean variable named StayPut to True. I want StayPut to be
visible to all the Worksheet_Activate Subs and its lifetime to be the
time that the Workbook is open. I've included "If StayPut then (new
line) Exit Sub (new line) End if" at the start of each sheet's
Worksheet_Activate Event Procedure. Nothing I do works.
Is anybody able to help me? Scope and lifetime have me totally confused!

Hi

I'm not really sure what you ask here, but John Walkenbach has code that
syncronize sheets at
http://j-walk.com/ss/excel/tips/tip75.htm

HTH. Best wishes Harald

<hanjohn@netspace.net.au> skrev i melding
news:1120701707.004411.278950@f14g2000cwb.googlegroups.com...
>I am using Excel (98 for Macintosh)as a teachers' markbook with student
> names in column A,test results in the other columns and different
> classes on different sheets.It is set up so that student's marks for
> the first test (in column B) are visible after the sheet is activated
> by clicking the sheet tab (each sheet module has "Range("B1").Select"
> in the Worksheet_Activate Event Procedure).
> I want the option of overriding this behaviour so that clicking a sheet
> tab takes you back to the part of the sheet on which you were
> previously working i.e; normal between-sheet behaviour.
> I am thinking along the lines of a standard Sub Procedure which when
> run sets a Boolean variable named StayPut to True. I want StayPut to be
> visible to all the Worksheet_Activate Subs and its lifetime to be the
> time that the Workbook is open. I've included "If StayPut then (new
> line) Exit Sub (new line) End if" at the start of each sheet's
> Worksheet_Activate Event Procedure. Nothing I do works.
> Is anybody able to help me? Scope and lifetime have me totally confused!
>

Sorry, that's not what I need. My workbook is multisheet. Say I'm
working (entering test marks for example) in a column that has to be
scrolled to because it is so far to the right. If I then click a sheet
tab to view another sheet (for whatever reason) and then back to the
original sheet I was working on, I have to scroll again to get to the
column I was working on. It works that way because of the code I've
placed in each Worksheet_Activate event procedure i.e;
Range("B1").Select.
All I want to do is code in the ability to over-ride the effect if the
code in the Event Procedures so that I don't have to scroll to get back
to an off-screen area when returning to a sheet. I just can't figure
out to set it up so that the user can run a Sub procedure that results
in a variable that is visible to the Worksheet_Activate event procedure
and has a lifetime that is as long as the workbook is open. The value
of that variable determines whether or not B1 is selected when a sheet
is activated.

Ok. Demo, place in the ThisWorkbook module of a new empty workbook:

Option Explicit

Dim BlnAuto As Boolean

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If BlnAuto = False Then
On Error Resume Next
Sh.Range("B2").Select
End If
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
BlnAuto = Not BlnAuto
End Sub

This selects B2 on sheet activation, and you disable-enable this behavior by
doubleclicking a cell.

HTH. Best wishes Harald

<hanjohn@netspace.net.au> skrev i melding
news:1120732526.721444.136740@f14g2000cwb.googlegroups.com...
> Sorry, that's not what I need. My workbook is multisheet. Say I'm
> working (entering test marks for example) in a column that has to be
> scrolled to because it is so far to the right. If I then click a sheet
> tab to view another sheet (for whatever reason) and then back to the
> original sheet I was working on, I have to scroll again to get to the
> column I was working on. It works that way because of the code I've
> placed in each Worksheet_Activate event procedure i.e;
> Range("B1").Select.
> All I want to do is code in the ability to over-ride the effect if the
> code in the Event Procedures so that I don't have to scroll to get back
> to an off-screen area when returning to a sheet. I just can't figure
> out to set it up so that the user can run a Sub procedure that results
> in a variable that is visible to the Worksheet_Activate event procedure
> and has a lifetime that is as long as the workbook is open. The value
> of that variable determines whether or not B1 is selected when a sheet
> is activated.
>

Thanks Harald, I'll try that out.

Great work Harald! It works beautifully. Also, I'm impressed that I
only have to use the This Workbook module instead of all of the
Worksheet modules and a standard Sub procedure.
Thanks again.
Ken Johnson.