Setting Row to be = to another Worksheet?
 

Assuming:
Dim srcProgramSummaryWs As Worksheet
Set srcProgramSummaryWs = Sheets("ProgramSummary")

In the Private Sub Worksheet_Activate() can you immediately set the
ActiveWindow.ScrollRow to be the same as that of the srcProgramSummaryWs (+8
rows)?

Setting Row to be = to another Worksheet?
 

I put this in a general module:

Option Explicit
Public ProgramSummaryRowNumber As Long

I put this behind the programsummary worksheet:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ProgramSummaryRowNumber = ActiveWindow.ScrollRow
End Sub

I put this behind the unnamed sheet:
Option Explicit
Private Sub Worksheet_Activate()
If ProgramSummaryRowNumber 0 Then
ActiveWindow.ScrollRow = ProgramSummaryRowNumber + 8
End If
End Sub

If I went to the programsummary worksheet first, selected a different cell, then
went to the other sheet, it worked fine.


CRayF wrote:

Assuming:
Dim srcProgramSummaryWs As Worksheet
Set srcProgramSummaryWs = Sheets("ProgramSummary")

In the Private Sub Worksheet_Activate() can you immediately set the
ActiveWindow.ScrollRow to be the same as that of the srcProgramSummaryWs (+8
rows)?

--

Dave Peterson

Setting Row to be = to another Worksheet?
 

I receive "Variable Not Defined"...
I guess I'm still not quite got down where global variables go...

In the "Modules" folder (right below "Forms") I placed the 2 lines below in
a Module of it's own (Module1):
---------------
Option Explicit
Public ProgramSummaryRowNumber As Long
----------------

And In the "ProgramSummary Worksheet" code I placed above any SUB() statement:
Option Explicit
And added within the existing "Sub Worksheet_SelectionChange" I added:
ProgramSummaryRowNumber = ActiveWindow.ScrollRow

--------------------------------------------
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False

ProgramSummaryRowNumber = ActiveWindow.ScrollRow 'Set Current Row Number

'------- Set Variables to Workbook Names ---------------
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
...more...
-----------------------------------

Setting Row to be = to another Worksheet?
 

Sorry... User Error... (Syntax)
Works fine... thanks

Setting Row to be = to another Worksheet?
 

OK, Now that I have the Syntax right, here are the symptoms...

This all works but I have to click on a cell to have it record... If I
simply scroll the "Program Summary Worksheet" where I want, and then go to
the corresponding Worksheet, I hope to end up on the matching row number of
the "Program Summary Worksheet" I just left.

Setting Row to be = to another Worksheet?
 

You could put the same code into the worksheet_activate routine:

This is behind the programsummary sheet:

Option Explicit
Private Sub Worksheet_Activate()
ProgramSummaryRowNumber = ActiveWindow.ScrollRow
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ProgramSummaryRowNumber = ActiveWindow.ScrollRow
End Sub




CRayF wrote:

OK, Now that I have the Syntax right, here are the symptoms...

This all works but I have to click on a cell to have it record... If I
simply scroll the "Program Summary Worksheet" where I want, and then go to
the corresponding Worksheet, I hope to end up on the matching row number of
the "Program Summary Worksheet" I just left.

--

Dave Peterson

Setting Row to be = to another Worksheet?
 

That doesn't seem to work either.

I go to the "Program Summary" Ws, (I've set up a few Target.Address = "$c$r"
's to move aound). I use these Target.Address's to scoll down a few races...
and when I go to the Betting Workseet, it does not move up or down.

Setting Row to be = to another Worksheet?
 

I don't understand what you mean by target.address to scroll down.

But it did work for me in my tests.

CRayF wrote:

That doesn't seem to work either.

I go to the "Program Summary" Ws, (I've set up a few Target.Address = "$c$r"
's to move aound). I use these Target.Address's to scoll down a few races...
and when I go to the Betting Workseet, it does not move up or down.

--

Dave Peterson

Setting Row to be = to another Worksheet?
 

I have 2 Worksheets. €œProgram Summary Worksheet€� & €œBetting Worksheet€�. Each
of them hold race data. Each race is 12 rows long.

When I am on the €œProgram Summary Ws€� and say I move from Race 2 down to
Race 8€¦ (Row 87) and then switch back to the €œBetting Ws€� I am wanting to
have the €œBetting Ws€� automatically scroll down to the same row number (+8)
as the €œProgram Summary.

The code you provided above did this. So I think that much was understood.
The problem is that if I move to the €œProgram Summary Ws€� and use the mouse
to scroll down to say Race 10 (Row 111) the variable is not updated because a
€œchange€� does not appear to have occurred.

My including of the €œtarget.address€� code from the €œProgram Summary Ws€� was
to show that I had also set up where I can click on a cell and it brings that
cell to the top of the screen. That also does not update the variable.

The only time the variable gets updates is if I then click on something from
the €œProgram Summary Ws€�.

I was hoping to acquire the top Row number of the €œProgram Summary€� even if
I use the mouse to scroll down. (I know Excel remembers this €“ as my default,
it returns to where I left off when returning back to the Ws.

Does thing help?

Setting Row to be = to another Worksheet?
 

Just using the scrollbars doesn't fire anything within excel. So there's no
macro that can run.

I guess you could use the worksheet_activate event to go back to your first
worksheet, check the top row number, then come back to the second worksheet and
use that.

This goes behind the "betting ws" worksheet


Option Explicit
Private Sub Worksheet_Activate()

Dim myTopRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Worksheets("Program Summary").Activate
myTopRow = ActiveWindow.ScrollRow

Me.Activate
ActiveWindow.ScrollRow = myTopRow

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

=======
And you can dump that other code.


CRayF wrote:

I have 2 Worksheets. €œProgram Summary Worksheet€� & €œBetting Worksheet€�. Each
of them hold race data. Each race is 12 rows long.

When I am on the €œProgram Summary Ws€� and say I move from Race 2 down to
Race 8€¦ (Row 87) and then switch back to the €œBetting Ws€� I am wanting to
have the €œBetting Ws€� automatically scroll down to the same row number (+8)
as the €œProgram Summary.

The code you provided above did this. So I think that much was understood.
The problem is that if I move to the €œProgram Summary Ws€� and use the mouse
to scroll down to say Race 10 (Row 111) the variable is not updated because a
€œchange€� does not appear to have occurred.

My including of the €œtarget.address€� code from the €œProgram Summary Ws€� was
to show that I had also set up where I can click on a cell and it brings that
cell to the top of the screen. That also does not update the variable.

The only time the variable gets updates is if I then click on something from
the €œProgram Summary Ws€�.

I was hoping to acquire the top Row number of the €œProgram Summary€� even if
I use the mouse to scroll down. (I know Excel remembers this €“ as my default,
it returns to where I left off when returning back to the Ws.

Does thing help?

--

Dave Peterson

Setting Row to be = to another Worksheet?
 

Youre a genius!
That works perfect!

You used Me.Active
Whats the short version of what "Me." translates to?

Setting Row to be = to another Worksheet?
 

I used me.activate

Me is the thing that owns the code. In this case it was that worksheet "betting
ws".



CRayF wrote:

Youre a genius!
That works perfect!

You used Me.Active
Whats the short version of what "Me." translates to?

--

Dave Peterson