Posts by BartH

    Re: Reset Freeze Panes to default (find current Freeze Panes cell)


    By the way, Ger,


    Freeze Panes is different from Splitting the window...


    As Berry Katcher nicely explained:[INDENT]Freeze Pane is meant to be used to freeze the topmost row(s) and/or the leftmost column(s) so that the column headers and/or row headers are always visible when the spreadsheet is larger than the viewing area on the monitor.
    Ex: On a sheet with 31 columns for each day of the month, you'll always be able to see which day it is, no matter how far down you go on the sheet.


    Splitting the Window, on the other hand, allows you to see two or four sections of the spreadsheet simultaneously, and enables you to maneuver around each independent of the others.
    Ex. If you want to see how inputting a figure in cell A1 affects a cell linked to it, say cell AZ1000, you split the sheet and maneuver to AZ1000, click on A1 and do your input, and you can see the result without having to scroll all the way from A1 to AZ1000.[/INDENT]


    Grtz Bart


    Quote from Ger Plante;772536

    Thanks Bart! Welcome to the forum.


    Would there be a specific advantage of using your code over the VBA methods?
    e.g.


    Cheers
    Ger

    Re: Reset Freeze Panes to default (find current Freeze Panes cell)


    Hi Ger,


    The main difference is that I have an option to hide the first two columns:


    Code
    ' these two lines only if you want to hide the first (two) columns '
            Range("A" & Range(myRange).Row).Select 
            ActiveWindow.SmallScroll ToRight:=2 
             ' end hide columns ----------------------------------------------- '


    Grtz Bart

    I often have found the Freeze Panes option on shared workbooks having been changed to a setting I don't like.
    To correct this I wrote the code below wich could probably also help others.
    It uses the getHomeCellAddress() function which finds the cell on which the Freeze Panes option is currently set.


    This code works on the active sheet and resets the Freeze Panes top left cell to D5, with the first two columns hidden (ajust accordingly)



    You could eventually call the resetFreezePanes procedure from the sheet's Activate event:


    Code
    Private Sub Worksheet_Activate()
        resetFreezePanes "D5"
    End Sub


    or call the code from a separate procedure (f.i. to use on a ribbon button):

    Code
    Sub resetFP_D5()
        resetFreezePanes "D5"
    End Sub


    I hope this will be useful to others, you may use it freely.
    Bart Hoeksel,
    Nedcom IT