Scope and Lifetime confusion.

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.

Glad to hear that Ken. Thanks for the feedback.

Best wishes Harald

<hanjohn@netspace.net.au> skrev i melding
news:1120737495.854874.270880@g43g2000cwa.googlegroups.com...
> 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.
>