Setting Row to be = to another Worksheet?

[CRayF]

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]

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]

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

 

[CRayF]

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

 

[CRayF]

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]

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]

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]

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

But it did work for me in my tests.

 

[CRayF]

While in the "Program Summary Worksheet, it is possible for the screen to me
moved by the following code: (or by using the scoll bar on the right...)

'-*******************************************
' [ LOOP (12 Rows)] Loop each set of 12 Rows x Number of Races on the
"Program Data Input" Worksheet
'-*******************************************
src = srcProgramSummaryWs.Range("B3").Value 'Get first Race Number
i = 3 'start row in "Input Data Ws" to test for Race Nunber
j = 3 'row number in "Betting Ws"
Do Until src = "" 'loop until no more Races detected
'-------------------------------------------
' When "Personal Picks" are Clicked
' [N], [O], [P], or [Q] Top Right of Race
' Scroll This Race to Top of Screen
'-------------------------------------------
'When the "Personal Picks" of any race is Clicked(in Rows N, O, P, or Q),
'then Scoll that race to the Top
If Not Intersect(Target, Range("N" & j & ":Q" & j)) Is Nothing Then _
ActiveWindow.ScrollRow = Target.Row
j = j + 12 'add for next set of (12) rows and loop
'Look for the existence of a Race number (no race number will end loop)
src = srcProgramSummaryWs.Cells(i, 2).Value
Loop
'-*******************************************
' [ END LOOP (12 Rows)]
'-*******************************************

 

[Dave Peterson]

I don't understand what you want to do.

It looks like this might be under a worksheet_selectionchange or
worksheet_change and if it is, I don't see what you want to do.

While in the "Program Summary Worksheet, it is possible for the screen to me
moved by the following code: (or by using the scoll bar on the right...)

'-*******************************************
' [ LOOP (12 Rows)] Loop each set of 12 Rows x Number of Races on the
"Program Data Input" Worksheet
'-*******************************************
src = srcProgramSummaryWs.Range("B3").Value 'Get first Race Number
i = 3 'start row in "Input Data Ws" to test for Race Nunber
j = 3 'row number in "Betting Ws"
Do Until src = "" 'loop until no more Races detected
'-------------------------------------------
' When "Personal Picks" are Clicked
' [N], [O], [P], or [Q] Top Right of Race
' Scroll This Race to Top of Screen
'-------------------------------------------
'When the "Personal Picks" of any race is Clicked(in Rows N, O, P, or Q),
'then Scoll that race to the Top
If Not Intersect(Target, Range("N" & j & ":Q" & j)) Is Nothing Then _
ActiveWindow.ScrollRow = Target.Row
j = j + 12 'add for next set of (12) rows and loop
'Look for the existence of a Race number (no race number will end loop)
src = srcProgramSummaryWs.Cells(i, 2).Value
Loop
'-*******************************************
' [ END LOOP (12 Rows)]
'-*******************************************

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

But it did work for me in my tests.

 

[CRayF]

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]

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]

You’re a genius!
That works perfect!

You used Me.Active
What’s the short version of what "Me." translates to?

 

[Dave Peterson]

I used me.activate

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