Scope and Lifetime confusion.

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