Stop Scrolling a select row

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
137
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"?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,818
Office Version
  1. 2010
Platform
  1. Windows
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.
VBA Code:
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

Board Regular
Joined
Aug 21, 2015
Messages
137
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.
VBA Code:
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

Board Regular
Joined
Aug 21, 2015
Messages
137
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?

VBA Code:
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

Board Regular
Joined
Aug 21, 2015
Messages
137

ADVERTISEMENT

PREVIOUS POST HAS INCORRECT CODE:
CORRECT CODE BELOW:

VBA Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,818
Office Version
  1. 2010
Platform
  1. Windows
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
 
Last edited:

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
137

ADVERTISEMENT

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

Well-known Member
Joined
Dec 23, 2017
Messages
1,818
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Aug 21, 2015
Messages
137
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

Board Regular
Joined
Aug 21, 2015
Messages
137
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top