VBA Code to Freeze / Unfreeze Panes

[DavidWF]

I have been trying, unsuccessfully, to automatically freeze panes on my worksheet as I scroll the cursor up and down the sheet. Is the following achievable?

The worksheet currently contains three years of data; each year has two header rows, 46 rows of data and then two blanks rows, so each year occupies 50 rows. Col A is a header column, col B to AK contain data. My screen displays 37 rows; I don't want to reduce the font size any further as the data becomes too hard to read.

Currently, in order to work on a particular year of data, I manually freeze panes by selecting either cell B3, or B53, or B103, depending on whether I want to look at 2018, or 2019, or 2020 data. The problem comes when I need to work on several years of data as I'm then having to freeze or unfreeze the panes as I scroll up and down the sheet. By way of example, I initially have the panes frozen based on cell B3. Rows 1 & 2 contain the 2018 header detail, locked in place by having frozen the panes. Rows 3 to 37 show 2018 data. As I scroll down, rows 51 & 52 (the 2019 header rows) come into view. As I scroll down those header rows gradually move up the screen and eventually move off the top of the screen, leaving me with the original 2018 headers (rows 1 & 2) and with 2019 data (rows 53 to 87) on screen - and with the potential for error as a result of the header rows showing a year that doesn't relate to the data.

What I want to achieve is that once the cursor moves out of a given year of data into the next, or the previous, year's data block I want VBA to automatically unfreeze the panes, scroll the sheet so that the required header rows are displayed as the two top rows on screen, then refreeze the panes. So - for clarity - let's say I've been working on 2020 data. Rows 101 & 102 are at the top of the screen, locked in place by having frozen the panes at B103. As soon as I try to drag the cursor above row 101 the panes should unfreeze, rows 51 & 52 are placed at the top of the display, cell B53 is made the active cell and the panes are frozen. If I then continue to drag the cursor upwards to the 2018 data, as soon as I try to drag the cursor above row 51 the panes should unfreeze, rows 1 & 2 are placed at the top of the display, cell B3 is made the active cell and the panes are frozen.

Likewise, if I'm initially working on 2018 data and then move downwards to 2020 data (or beyond), rows 1 & 2 will be at the top of the screen, locked in place by having frozen the panes at B3. As soon as I drag the cursor below row 50 the panes should unfreeze, rows 51 & 52 are placed at the top of the display, cell B53 is made the active cell and the panes are frozen. If I then continue to drag the cursor downwards to the 2020 data, as soon as I try to drag the cursor below row 100 the panes should unfreeze, rows 101 & 102 are placed at the top of the display, cell B103 is made the active cell and the panes are frozen.

Can this be achieved with VBA or am I asking for the impossible? As a last resort I'll create separate sheets for each year but my preference is to keep it as a single worksheet and learn something new about VBA. And as a cautionary note, future years data may exceed 50 rows, so any solution should not be based on 50-row blocks of annual data.

 

[rondeondo]

HI David

Do you have any control of the layout of the sheet?
if you can, I'd suggest adding a column to indicate the year then use auto filter to filter the data by the year you're looking at.
When you are switching from one year to the next/previous is it a period comparison you are doing? if so do you have period/date data in your data? if you do you can filter the data to show ranges of dates across multiple years to allow the comparison.
Also if you add year/period/month another option would be a pivot on another sheet, this would then allow slicers and filters to look at specific parts of the data and compare it to other years/periods

But in direct answer to the question you pose, yes, within excel everything you do as far as changing cell focus and entering data triggers an event, the events can be used to perform actions. you will be able to add code under one of the events to unfreeze panes based on which cell currently has the focus then maybe look for the nearest header row above the current cell to choose which rows to freeze. See below. there are worksheet level events and also workbook level events, so you can have code run when you open a file or save it etc.
For your case it would be the selectionChange event. There is a variable automatically passed to the event to provide the cell reference that has been selected. Then it's just a matter of building your logic around the event and cell ref.

 

[DavidWF]

Thanks Rondeondo - that's encouraging news.

Yes, I do have total control of the layout of the sheet. The sheet is very much a work in progress and I'm building it in both directions - forward in time and backward in time. Yes, I am doing period comparisons, but only in the sense that I'm generating month-by-month summaries and then, ultimately, plotting all the month-by-month data on a graph to present a long-term trend, so in this instance I don't have the need to slice or filter the data for analytical purposes.

I've spent a lot of time today thinking about your comments, especially the last couple lines, and will definitely pursue that option. Another idea I had today is to create a button for each year I want to look at, with coding that puts the header rows for that year at the top of the sheet and freezes the panes, and then put the buttons on the Quick Access Toolbar so that they're always visible. I already do this for one touch printing to my printer or to a .pdf file. This worksheet will be in regular use for years, so it would justify having buttons on the toolbar.

A lot to still think about, and your input was very much appreciated.