Stop Scrolling a select row

[EBoudreau]

I've searched this topic and all I can find is how to freeze panes which I already have in use but I need a solution if one is out there to selecting a specific row to stop scrolling at the top of the sheet right under my top 7 frozen rows.

I have rows 1-7 frozen at the top of the sheet and columns A-D frozen on the left of the sheet.
I would like to be able to select row 57 to be able to scroll up but stop at the top of the page right below 1-7 so it remains visible as I continue to scroll down though the sheet.
Then when I scroll back up to the top of the page, when row 57 needs to scroll back down with the rest of the sheet it can.

Is that something that is doable? Or does it strictly have to be a "Frozen Pane"?

 

[offthelip]

I am not clear from your description of exactly what you want but you can do something like this to control which rows are selectable.
This limits the selection to between rows 7 and 57.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
rowno = Target.Row
colno = Target.Column
If rowno < 7 Then
 Application.EnableEvents = False
  Cells(7, colno).Select
 Application.EnableEvents = True
End If
If rowno > 57 Then
 Application.EnableEvents = False
  Cells(57, colno).Select
 Application.EnableEvents = True
End If

End Sub

 

[EBoudreau]

I am not clear from your description of exactly what you want but you can do something like this to control which rows are selectable.
This limits the selection to between rows 7 and 57.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
rowno = Target.Row
colno = Target.Column
If rowno < 7 Then
Application.EnableEvents = False
  Cells(7, colno).Select
Application.EnableEvents = True
End If
If rowno > 57 Then
Application.EnableEvents = False
  Cells(57, colno).Select
Application.EnableEvents = True
End If

End Sub

I’ll try it when I get back to work later this evening.
Essentially, I want rows 1-7 to stay as frozen panes but also want row 57 to stop scrolling when it get to top of the page and remain in view.
I’ll give it a shot and report back tonight.

 

[EBoudreau]

I’ll try it when I get back to work later this evening.
Essentially, I want rows 1-7 to stay as frozen panes but also want row 57 to stop scrolling when it get to top of the page and remain in view.
I’ll give it a shot and report back tonight.

So that didn't work out. I tried adding it into existing code that's already in the sheet. Here's what I have in there now:
Did I insert incorrectly?

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim Rng As Range
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="WellingtonFrac"
    
    Set Rng = Intersect(Target, Range("A9:A20, A22:A34, A59:A61, E9:DA56, E59:DA82"))
    If Not Rng Is Nothing Then Call Capitalise(Rng)
        
    Set Rng = Intersect(Target, Range("D53:D1583"))
    If Not Rng Is Nothing Then Call ConvertToTime(Rng)
    
'    I assume this is the range you want to automatically trigger the recorded macro. If not, change accordingly
'    If Not Intersect(Target, Range("C9:C32")) Is Nothing Then
'        Call CopyWellColors
'    End If
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="WellingtonFrac"
    Application.EnableEvents = True

    Sheets("Frac Report").EnableSelection = xlNoRestrictions
    Sheets("Stage Times").EnableSelection = xlNoRestrictions

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range, RngRow As Range, RngCol As Range
Set Rng = Intersect(Target, Range("E9:DA56"))
If Not Rng Is Nothing Then
    Set RngRow = Range("E7:DA7")
    'Set RngCol = Range("C9:C56")
    RngRow.Interior.ColorIndex = 37 'xlColorIndexNone
    'RngCol.Interior.ColorIndex = 46 'xlColorIndexNone
    RngRow(Target.Column - 4).Interior.ColorIndex = 46
    'RngCol(Target.Row - 8).Interior.ColorIndex = 37
End If
Set Rng = Intersect(Target, Range("E59:DA106"))
If Not Rng Is Nothing Then
    Set RngRow = Range("E57:DA57")
    'Set RngCol = Range("C59:C106")
    RngRow.Interior.ColorIndex = 37 'xlColorIndexNone
    'RngCol.Interior.ColorIndex = 46 'xlColorIndexNone
    RngRow(Target.Column - 4).Interior.ColorIndex = 46
    'RngCol(Target.Row - 58).Interior.ColorIndex = 37
End If

End Sub

 

[EBoudreau]

PREVIOUS POST HAS INCORRECT CODE:
CORRECT CODE BELOW:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim Rng As Range
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="WellingtonFrac"
    
    Set Rng = Intersect(Target, Range("A9:A20, A22:A34, A59:A61, E9:DA56, E59:DA82"))
    If Not Rng Is Nothing Then Call Capitalise(Rng)
        
    Set Rng = Intersect(Target, Range("D53:D1583"))
    If Not Rng Is Nothing Then Call ConvertToTime(Rng)
    
'    I assume this is the range you want to automatically trigger the recorded macro. If not, change accordingly
'    If Not Intersect(Target, Range("C9:C32")) Is Nothing Then
'        Call CopyWellColors
'    End If
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="WellingtonFrac"
    Application.EnableEvents = True

    Sheets("Frac Report").EnableSelection = xlNoRestrictions
    Sheets("Stage Times").EnableSelection = xlNoRestrictions

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range, RngRow As Range, RngCol As Range
Set Rng = Intersect(Target, Range("E9:DA56"))
If Not Rng Is Nothing Then
    Set RngRow = Range("E7:DA7")
    Set RngCol = Range("D9:D56")
    RngRow.Interior.ColorIndex = 37 'xlColorIndexNone
    RngCol.Interior.ColorIndex = 37 'xlColorIndexNone
    RngRow(Target.Column - 4).Interior.ColorIndex = 46
    RngCol(Target.Row - 8).Interior.ColorIndex = 46
End If
Set Rng = Intersect(Target, Range("E59:DA106"))
If Not Rng Is Nothing Then
    Set RngRow = Range("E57:DA57")
    Set RngCol = Range("D59:D106")
    RngRow.Interior.ColorIndex = 37 'xlColorIndexNone
    RngCol.Interior.ColorIndex = 37 'xlColorIndexNone
    RngRow(Target.Column - 4).Interior.ColorIndex = 46
    RngCol(Target.Row - 58).Interior.ColorIndex = 46
End If
End Sub

 

[offthelip]

Yes you have made a mistake, you have not got the essential part of my code. I suggest you start with a new blank workbook and copy my code exactly as it is into sheet1 and then see what it does. Note rowno and colno are NOT RANGES in my code, declare them as long if you feel you need to

 

[EBoudreau]

Yes you have made a mistake, you have not got the essential part of my code. I suggest you start with a new blank workbook and copy my code exactly as it is into sheet1 and then see what it does. Note rowno and colno are NOT RANGES in my code, declare them as long if you feel you need to

I forgot to include it apparently lol. Oops my applogies.
Everything I posted previously needs to be in the same sheet I want the row scrolling issue to be resolved in.
How would your code be incorporated into what I posted?
Or will that not work?

 

[offthelip]

I don't really understand what you are trying to do with your existing code, but you should be able to add my code at the top before your code and it will work in exactly the same way as it does stand alone. Have you tried it?? just try selecting a cell above row 7 or below row 57

 

[EBoudreau]

The existing code needs to be there for auto capitalization and convering time formats, as well as highlighting certain cells in a row and column based on which cell is selected in a couple ranges.

Unfortunately its not stopping row 57 from scrolling past the top of the screen. When selecting above 7 or below 57, it puts row 57 in the middle of the screen but I just need row 57 to stop scrolling up higher out of view at the top of the sheet when I scroll down past row 57.

 

[EBoudreau]

I tried playin around with the row selection code and I'm struggling to make it work. It's not stopping row 57 from scrolling up out of view past the frozen panes 1-7 as I scroll down the page. I need 1-7 to remain frozen because they have buttons and header information I would like to stay in view.

I don't really understand what you are trying to do with your existing code, but you should be able to add my code at the top before your code and it will work in exactly the same way as it does stand alone. Have you tried it?? just try selecting a cell above row 7 or below row 57