excelvbafreezepane

VBA Script to freeze two diferent rows (panes) in excel during scroll


I have a spreadsheet (Example Spreadsheet ) and I want to scroll down and freeze two certain separate rows when the spreadsheet gets there while scrolling down. I want it to freeze rows 2-4 when row 1 gets "scrolled" up and keep scrolling the rest of the rows (below the already freezed rows 2~4) up until it gets to line 62, where it then will freeze rows 62-64 (besides the already frozen rows 2-4) and keep scrolling until the end of the spreadsheet.

For example: I have a worksheet that has two "areas". The top area shows the monthly money stuff and the lower the annual money stuff. It has nothing on Line #1, and the months header (Dec-19, Jan 20, etc) are on line #4. In my example spreadsheet here, I have line #1 scrolled up (hidden-scrolled) and the rows 2-4 freezed. I then scroll down until I get to row 62, where my yearly money stuff header comences, and where it will then freeze rows 62-64 and continue scrolling the remaining rows.

Is such thing possible using some sort of coding?


Solution

  • Option 1: The compromise

    This won't be an easy task, so you might want to consider simply splitting the two sections in 2 different sheets and simply use the Freeze Panes method in each sheet like this:

    enter image description here

    For row 1, if it bothers you, you could simply scroll past if before applying the freeze.

    You could even add a button at the bottom/top of the page that would make the user go to the other sheet when clicked for a better user experience.

    Option 2: Going all in

    First, there is an article on Chip Pearson's blog that shows how to detect a window scroll with VBA using classes and the Windows API. This is a bit advanced but this is essential if you want to achieve the result you have in mind.

    Second, you need to use the ActiveWindow.FreezePanes property to toggle the freezing of the panes (true or false). The only issue here is that the way the panes will be frozen will be influenced by which cell is selected so your code will have to select the appropriate cell (the leftmost cell right below the region you don't want to scroll like the in image above). And it would also have to make sure that the data above was scrolled past.

    So, you could have a condition based on the top-left cell's row provided in C. Pearson's approach and if the row is bigger than a certain threshold it would 1) unfreeze the pane; 2) scroll to the right row and column; 3) select the appropriate cell; 4) reapply the freeze like this:

    ActiveWindow.FreezePanes = False
    
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollColumn = 1
    
    ActiveSheet.Range("A65").Select
    
    ActiveWindow.FreezePanes = True
    

    And similarly for when the user scrolls back up.