....and I said, here's an easy bit of code.
- The first If statement prevent the code from executing if the Sheet is not a Worksheet, e.g. a ChartSheet
- The second If statement prevents re-setting the first row/column if the Window already has frozen rows/columns set
So I thought about it some more!
Standard Freeze for New Sheets
Copying current Window settings to a New Window
- There are several actions the user takes that trigger the code
- Adding a Window
- Closing a Window
- Changing from one Window to another
- Adding a Worksheet
- Adding a sheet that is not a Worksheet, e.g. a Chart. (There are two others.)
- Changing from one sheet to another.
- Each of the above triggers a series of events wherein data that might be more useful downstream is available only upstream.
- Another user action, one that does not trigger an event but affects the solution, is changing rows/columns that are frozen.
- Provide for creating a new worksheet either based on the most recently used worksheet or using a standard number of frozen rows and columns.
- Data must be shared between event procedures, capturing the data in a preceding procedure then using it in one that follows in the stream.
- The exposed object model does not include ...
- ...a Workbook_NewWindow event, as it does a Workbook_NewSheet. In this case the solution will need to carry the settings from the existing Window to the new one.
- ... a way to see the freeze points of more than one worksheet at a time. Only the ActiveSheet property of the ActiveWindow has these properties.
- When adding a new sheet, capturing then applying the freeze values cannot be done in a single event procedure and the needed data cannot be captured in a preceding procedure to be passed to one that is later in the stream.
- Events are in the following order
- Workbook_NewSheetParameter "sh as Object" refers to the new sheetActiveWindow.ActiveSheet also refers to the new sheet
Parameter "sh as Object" refers to the sheet being deactivated
ActiveWindow.ActiveSheet refers to the new sheet
Parameter "sh as Object" refers to the new sheet
ActiveWindow.ActiveSheet also refers to the new sheet