Select a Cell - Move Row to the Top of the screen

If I click on a cell can I make that row come to the top of the screen?

There are a set of of rows controled together. Is only part of them are
showing, I'd like to click on a cell at the top of the group to make that set
of rows to come to the top of the screen.

Use the selection change event to detect you have clicked there. Then use
the

ActiveWindow.ScrollRow to put it at the top of the window.

--
Regards,
Tom Ogilvy

"CRayF" <[email protected]> wrote in message
news:[email protected]...
> If I click on a cell can I make that row come to the top of the screen?
>
> There are a set of of rows controled together. Is only part of them are
> showing, I'd like to click on a cell at the top of the group to make that
set
> of rows to come to the top of the screen.
>

I'm getting a compile error. I've included the section directly below my
comments of the code I added to the module. I'm hoping that when Cell A83 is
selected it moves the screen so that row 83 is on top. (Note that I commented
out the looped Target.Address until I get one working.)
I get a compile error when I selected it.

'<---------------- start

'-------------------------------------------
' [ AC11 - To Scroll The selected Race to the top of the screen
'-------------------------------------------
' If Target.Address = "$AC$" & j Then ActiveWindow.ScrollRow
If Target.Address = "$A83" Then
ActiveWindow.ScrollRow
End If

'<---------------- end

Below is the entire code in case it makes it easier to understand how I'm
using it.

---------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'------- Main File Names used for this WorkBOOK ---------------
'--*** Use Program Input Worksheet as a Master for changes ***
'--------------------------------------------------------------
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")
'--------------------------------------------------------------
'------- Set Variables to Workbook Names ---------------
'--*** Use Program Input Worksheet as a Master for changes ***
'--------------------------------------------------------------

Dim raceParkPrefix As Variant
Dim raceParkName As Variant

raceParkPrefix = Left(srcProgramDataInputWs.Range("H3").Value, 3)
raceParkName = Mid(srcProgramDataInputWs.Range("H3").Value, 3)

' MsgBox " ? " & raceParkPrefix ' Debug <-----/////--------\\\\\

'------------------------------------------------------------------------
' [ AA1 ] Switch Worksheets to the "Program Summary"
'------------------------------------------------------------------------
If Target.Address = "$AC$1" Then
Range("C2").Select
srcProgramSummaryWs.Activate
End If

'-------------------------------------------
' [Q7] Static Cell Selection for DD Race Worksheet
'-------------------------------------------
If Target.Address = "$Q$7" Then
If UCase(Range("R6")) = "TRUE" Then
Range("R6") = "FALSE"
Range("A16").Select
Else
Range("R6") = "TRUE"
Range("A16").Select
End If
End If


'-*******************************************
' [S4] Lock All and - Buttons for [Sn] starting with S11 and where n =
every 12 rows.
' [Sn] Lock Race (looped) starting with S11 and where n = every 12 rows.
' [Qn] Race Posted (looped) starting with Q13 and where n = every 12 rows.
'-*******************************************
' [ LOOP (12 Rows)] Loop each set of 12 Rows x Number of Races on the
"Program Data Input" Worksheet
'-*******************************************
src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 11
Do Until src = ""

'-------------------------------------------
' [ S4 - LOCK **ALL** - ] Loop to set the Locked Button
'-------------------------------------------
If Target.Address = "$S$4" And ActiveSheet.Name <> _
srcBettingTemplateWs.Name Then

srcBettingTemplateWs.Unprotect
If Application.CountA(Range("S" & j + 5 & ":S" & j + 8)) <> _
Range("S" & j + 5 & ":S" & j + 8).Count Then
'At least one of your racers are missing.
'code here
Else
'No racers are missing.
ActiveSheet.Unprotect
ActiveSheet.Range("A" & j + 1).Value = ActiveSheet.Range("A" & j
+ 1).Value
ActiveSheet.Range("A" & j + 2).Value = ActiveSheet.Range("A" & j
+ 2).Value
ActiveSheet.Range("K" & j & ":M" & j).Value =
ActiveSheet.Range("K" & j & ":M" & j).Value
ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value =
ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value
ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value =
ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value
ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value =
ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value
Range("S" & j).Value = ChrW(&H25BA)
Range("T" & j).Value = "Locked"
Range("S" & j + 5).Select
ActiveSheet.Protect
srcBettingTemplateWs.Protect
End If
End If

'-------------------------------------------
' [ S11 - LOCK - ] Loop to set the Locked Button
'-------------------------------------------
If Target.Address = "$S$" & j And ActiveSheet.Name <> _
srcBettingTemplateWs.Name Then
If Range("T" & j).Value = "Locked" Then
MsgBox "Race already locked"
Else
'Check to see if any racers are left blank before locking
If Application.CountA(Range("S" & j + 5 & ":S" & j + 8)) <> _
Range("S" & j + 5 & ":S" & j + 8).Count Then
'At least one of your racers are missing.
MsgBox "The Program RACE INFORMATION currently loaded is
" & Range("Z1").Value & " ** At least one of your racers is missing**. You
should not LOCK Race " & Range("A" & j + 2).Value & " before recording all
your ' Y O U R P E R S O N A L P I C K S ' from the Program Summary
Worksheet? If you want to do so anyway, simply add a non-blank character as
the missing racer."

Else 'No racers are missing.
If MsgBox("You are about to PERMANENTLY store your 'Your
Picks' and 'Race Program Picks' below. Are you sure you want to do this?", _
vbYesNo) = vbYes Then
ActiveSheet.Unprotect
ActiveSheet.Range("A" & j + 1).Value =
ActiveSheet.Range("A" & j + 1).Value
ActiveSheet.Range("A" & j + 2).Value =
ActiveSheet.Range("A" & j + 2).Value
ActiveSheet.Range("K" & j & ":M" & j).Value =
ActiveSheet.Range("K" & j & ":M" & j).Value
ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value =
ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value
ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value =
ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value
ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value =
ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value
Range("S" & j).Value = ChrW(&H25BA)
Range("T" & j).Value = "Locked"
ActiveSheet.Protect
End If
End If
End If
Range("S" & j + 5).Select 'Move curser to First PICK choice
End If


'-------------------------------------------
' [ Q12 - POST Button Loop to set the POST Button
'-------------------------------------------
If Target.Address = "$Q$" & j + 1 Then
If UCase(Range("R" & j)) = "TRUE" Then
Range("R" & j) = "FALSE"
Else
Range("R" & j) = "TRUE"
End If
Range("A" & j + 5).Select
End If

'<---------------- start

'-------------------------------------------
' [ AC11 - To Scroll The selected Race to the top of the screen
'-------------------------------------------
' If Target.Address = "$AC$" & j Then ActiveWindow.ScrollRow
If Target.Address = "$A83" Then
ActiveWindow.ScrollRow
End If

'<---------------- end

i = i + 12 'add for next set of (12) rows
j = j + 12 'add for next set of (12) rows

'Look for the existence of a Race number (no race number will end loop)
src = srcProgramDataInputWs.Cells(i, 2).Value

Loop

Cancel = True
End Sub

Try:

ActiveWindow.ScrollRow = Target.Row

Regards
Rowan

CRayF wrote:
> I'm getting a compile error. I've included the section directly below my
> comments of the code I added to the module. I'm hoping that when Cell A83 is
> selected it moves the screen so that row 83 is on top. (Note that I commented
> out the looped Target.Address until I get one working.)
> I get a compile error when I selected it.
>
> '<---------------- start
>
> '-------------------------------------------
> ' [ AC11 - To Scroll The selected Race to the top of the screen
> '-------------------------------------------
> ' If Target.Address = "$AC$" & j Then ActiveWindow.ScrollRow
> If Target.Address = "$A83" Then
> ActiveWindow.ScrollRow
> End If
>
> '<---------------- end
>
> Below is the entire code in case it makes it easier to understand how I'm
> using it.
>
> ---------------------------
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> '------- Main File Names used for this WorkBOOK ---------------
> '--*** Use Program Input Worksheet as a Master for changes ***
> '--------------------------------------------------------------
> Dim srcProgramDataInputWs As Worksheet
> Dim srcProgramSummaryTemplateWs As Worksheet
> Dim srcProgramSummaryWs As Worksheet
> Dim srcBettingTemplateWs As Worksheet
> Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
> Set srcProgramSummaryWs = Sheets("ProgramSummary")
> Set srcBettingTemplateWs = Sheets("@TempleteBetting")
> Set srcProgramDataInputWs = Sheets("ProgramDataInput")
> '--------------------------------------------------------------
> '------- Set Variables to Workbook Names ---------------
> '--*** Use Program Input Worksheet as a Master for changes ***
> '--------------------------------------------------------------
>
> Dim raceParkPrefix As Variant
> Dim raceParkName As Variant
>
> raceParkPrefix = Left(srcProgramDataInputWs.Range("H3").Value, 3)
> raceParkName = Mid(srcProgramDataInputWs.Range("H3").Value, 3)
>
> ' MsgBox " ? " & raceParkPrefix ' Debug <-----/////--------\\\\\
>
> '------------------------------------------------------------------------
> ' [ AA1 ] Switch Worksheets to the "Program Summary"
> '------------------------------------------------------------------------
> If Target.Address = "$AC$1" Then
> Range("C2").Select
> srcProgramSummaryWs.Activate
> End If
>
> '-------------------------------------------
> ' [Q7] Static Cell Selection for DD Race Worksheet
> '-------------------------------------------
> If Target.Address = "$Q$7" Then
> If UCase(Range("R6")) = "TRUE" Then
> Range("R6") = "FALSE"
> Range("A16").Select
> Else
> Range("R6") = "TRUE"
> Range("A16").Select
> End If
> End If
>
>
> '-*******************************************
> ' [S4] Lock All and - Buttons for [Sn] starting with S11 and where n =
> every 12 rows.
> ' [Sn] Lock Race (looped) starting with S11 and where n = every 12 rows.
> ' [Qn] Race Posted (looped) starting with Q13 and where n = every 12 rows.
> '-*******************************************
> ' [ LOOP (12 Rows)] Loop each set of 12 Rows x Number of Races on the
> "Program Data Input" Worksheet
> '-*******************************************
> src = srcProgramDataInputWs.Range("B3").Value
> i = 3
> j = 11
> Do Until src = ""
>
> '-------------------------------------------
> ' [ S4 - LOCK **ALL** - ] Loop to set the Locked Button
> '-------------------------------------------
> If Target.Address = "$S$4" And ActiveSheet.Name <> _
> srcBettingTemplateWs.Name Then
>
> srcBettingTemplateWs.Unprotect
> If Application.CountA(Range("S" & j + 5 & ":S" & j + 8)) <> _
> Range("S" & j + 5 & ":S" & j + 8).Count Then
> 'At least one of your racers are missing.
> 'code here
> Else
> 'No racers are missing.
> ActiveSheet.Unprotect
> ActiveSheet.Range("A" & j + 1).Value = ActiveSheet.Range("A" & j
> + 1).Value
> ActiveSheet.Range("A" & j + 2).Value = ActiveSheet.Range("A" & j
> + 2).Value
> ActiveSheet.Range("K" & j & ":M" & j).Value =
> ActiveSheet.Range("K" & j & ":M" & j).Value
> ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value =
> ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value
> ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value =
> ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value
> ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value =
> ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value
> Range("S" & j).Value = ChrW(&H25BA)
> Range("T" & j).Value = "Locked"
> Range("S" & j + 5).Select
> ActiveSheet.Protect
> srcBettingTemplateWs.Protect
> End If
> End If
>
> '-------------------------------------------
> ' [ S11 - LOCK - ] Loop to set the Locked Button
> '-------------------------------------------
> If Target.Address = "$S$" & j And ActiveSheet.Name <> _
> srcBettingTemplateWs.Name Then
> If Range("T" & j).Value = "Locked" Then
> MsgBox "Race already locked"
> Else
> 'Check to see if any racers are left blank before locking
> If Application.CountA(Range("S" & j + 5 & ":S" & j + 8)) <> _
> Range("S" & j + 5 & ":S" & j + 8).Count Then
> 'At least one of your racers are missing.
> MsgBox "The Program RACE INFORMATION currently loaded is
> " & Range("Z1").Value & " ** At least one of your racers is missing**. You
> should not LOCK Race " & Range("A" & j + 2).Value & " before recording all
> your ' Y O U R P E R S O N A L P I C K S ' from the Program Summary
> Worksheet? If you want to do so anyway, simply add a non-blank character as
> the missing racer."
>
> Else 'No racers are missing.
> If MsgBox("You are about to PERMANENTLY store your 'Your
> Picks' and 'Race Program Picks' below. Are you sure you want to do this?", _
> vbYesNo) = vbYes Then
> ActiveSheet.Unprotect
> ActiveSheet.Range("A" & j + 1).Value =
> ActiveSheet.Range("A" & j + 1).Value
> ActiveSheet.Range("A" & j + 2).Value =
> ActiveSheet.Range("A" & j + 2).Value
> ActiveSheet.Range("K" & j & ":M" & j).Value =
> ActiveSheet.Range("K" & j & ":M" & j).Value
> ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value =
> ActiveSheet.Range("Q" & j + 5 & ":Q" & j + 8).Value
> ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value =
> ActiveSheet.Range("S" & j + 5 & ":S" & j + 8).Value
> ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value =
> ActiveSheet.Range("U" & j + 5 & ":U" & j + 8).Value
> Range("S" & j).Value = ChrW(&H25BA)
> Range("T" & j).Value = "Locked"
> ActiveSheet.Protect
> End If
> End If
> End If
> Range("S" & j + 5).Select 'Move curser to First PICK choice
> End If
>
>
> '-------------------------------------------
> ' [ Q12 - POST Button Loop to set the POST Button
> '-------------------------------------------
> If Target.Address = "$Q$" & j + 1 Then
> If UCase(Range("R" & j)) = "TRUE" Then
> Range("R" & j) = "FALSE"
> Else
> Range("R" & j) = "TRUE"
> End If
> Range("A" & j + 5).Select
> End If
>
> '<---------------- start
>
> '-------------------------------------------
> ' [ AC11 - To Scroll The selected Race to the top of the screen
> '-------------------------------------------
> ' If Target.Address = "$AC$" & j Then ActiveWindow.ScrollRow
> If Target.Address = "$A83" Then
> ActiveWindow.ScrollRow
> End If
>
> '<---------------- end
>
> i = i + 12 'add for next set of (12) rows
> j = j + 12 'add for next set of (12) rows
>
> 'Look for the existence of a Race number (no race number will end loop)
> src = srcProgramDataInputWs.Cells(i, 2).Value
>
> Loop
>
> Cancel = True
> End Sub

thanks