Freeze Panes VBA Without Select

  • Re: Freeze Panes Without .select


    How can freeze panes work without a reference (i.e. a cell on a worksheet)? How else would Excel "know" where you want to freeze panes?


    What exactly are you trying to accomplish?



    AAE

  • Re: Freeze Panes Without .select


    This is what I'm trying to accomplish. I have a calendar in a spreadsheet and some VBA subroutines to update it, taking data from other spreadsheets. I have the calendar freeze framed at a specific point (10cells down, 6 cells from the left), so that I can scroll down and sroll to the right, leaving me with fixed margins for titles at the top of my sheet and on the left hand side.
    In the process of updating this sheet (which I do from another sheet), the freeze freaming is removed and I want to re-establish it programatically at a specific point in the spreadsheet.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]OK, this really is impossible to do. Freezepanes is a member of the windows collection. The best I can do is something like this set the screen updating temporarily to false and then quickly nip into the worksheet, select the cell I want to freeze the worksheet at, and use the freezepanes method of the windows collection.

  • Re: Freeze Panes Without .select


    Unless I am misunderstanding . .


    Put your code in the worksheet activate event module so that when the worksheet is activated your freeze panes are automatically applied.



    AAE

  • Re: Freeze Panes Without .select


    Quote

    Auto Merged Post;


    OK, this really is impossible to do. Freezepanes is a member of the windows collection. The best I can do is something like this set the screen updating temporarily to false and then quickly nip into the worksheet, select the cell I want to freeze the worksheet at, and use the freezepanes method of the windows collection.


    Well you have me confused. If the calendar worksheet you're updating (done from another worksheet) is not active why the need to even apply freeze panes, which only has value when you are actively viewing the worksheet?


    AAE

  • Re: Freeze Panes Without .select


    Quote from DonMS

    sure.. that's another way of doing it.. but I don't ned to run it every time I actuivate the worksheet as the sheet remembers its freeze state.

    Right click on the sheet name tab, choose View Code and use;

    Code
    1. Private Sub Worksheet_Activate()
    2. Application.Goto Range("A2"), True
    3. ActiveWindow.FreezePanes = True
    4. End Sub
  • Re: Freeze Panes VBA Without Select


    I think everybody in this thread have temporarily lost their ability to read.


    The OP's question was simple: Whether it is necessary to select a cell or cells (you know, using Range.Select or Application.Goto or whatever else has been suggested in this thread) before applying freezepanes.


    The OP then proceeded to acknowledge the correct answer, which is that because FreezePanes is a method of an window object, the sheet on which you want to apply it needs to be the activesheet, and it is always applied to the top and left of the selected cells.


    The OP didn't ask to freeze panes every time the worksheet is activated.


    But to actually contibute, this is how I'm doing it now:



    I use the rngPrevSelection to store the previous selection and then switch it back, because that's just good practice. Please note however that if the selection is not a range (a chart, for instance) this will return a Type Mismatch error. However, if I remember correctly if you try to turn FreezePanes = False and you have a chart selected, it will fail anyway.

  • I don't see any posts, other than mine, where GoTo is used? Besides, your code uses Select AND activate a few times and is quite unreliable.


    Quote

    Anyone know if it is possible, in VBA, to freeze the panes of a worksheet at a specific cell without selecting that worksheet or the cell. thanks..

    The question is based on a false premise (and most knew this, hence the replies) as FreezePanes is purely visual. That is, the ONLY time it matters is when the Worksheet IS active.

  • Re: Freeze Panes VBA Without Select


    Dave,


    I know my code uses select and activate, because that's the only way to do it. The code I posted was simply showing how I've chosen to work with the problem. If there is a better way, I would be very glad to learn, becaused I have a couple of apps out there that are using this method, and I don't feel very confident in it.


    The question however is completely sensible. You start programming in VBA, and you learn that you don't need to select cells to do things, and suddenly you can't figure out how the heck to freeze panes at a certain point without selecting. No false premises there, IMO.


    Hooking the freezepanes into the worksheet_activate event, while relatively reliable, would be _really_ annoying, because the screen would twich every time you activate a sheet. In the best case scenario, where you would record the previous selection, scroll status, and then toggle them back, the screen would still twitch every time you activate a sheet. With a lot of data, and especially with some shapes (such as charts) mixed in, this can look really bad, as the redrawing time can be perceivable. Or if the user has selected the entire sheet or other large amount of data, and changes sheets temporarily and goes back. On my computer selecting large filled ranges can sometimes freeze Excel for 3-10 seconds.


    Or if the user doesn't like the freezepanes and turns them off, then goes to check another sheet and coming back, bam! The old settings are back. Not to mention the fact that it is completely unnecessary to redo this at every sheet activation, once you freeze the panes while you are running other code anyway, like OP, you don't need to mess with it again until the next time you turn them off.


    The event handler I tried to test this looks like this:



    Peruse it at your will, but you should know that it needs some failguards for if the selection is not a range.

  • Re: Freeze Panes VBA Without Select


    I showed what I believe to be a "better way" and certainly more reliable.


    Quote

    The question however is completely sensible. You start programming in VBA, and you learn that you don't need to select cells to do things, and suddenly you can't figure out how the heck to freeze panes at a certain point without selecting. No false premises there, IMO.

    I didn't say, or allude to say, the question wasn't "sensible". IMO, when a question a based doing something that is impossible or even needed, it's based on a false premise.


    Eg How can I fly like a bird by jumping off a cliff? In the case of the OP, the question is based on the premise that FreezePanes matters on a non active Worksheet. It simply doesn't matter.


    All that scrolling is simply not needed. One can use

    Code
    1. ActiveWindow.VisibleRange

    Or, goto as I suggested.

  • Re: Freeze Panes VBA Without Select


    Dave,


    Could you please post a small example how you would, using VisibleRange and GoTo, achieve the following, when used in conjuction with worksheet_activate event:


    • Freeze panes on a certain cell, that may or may not be in view when the sheet is activated
    • Ensure that the scroll position where the user left it when deactivating the sheet is preserved
    • Ensure that the cells that user had selected before deactivating the sheet are kept selected
    • Do this without a displaying a distracting twitch


    I think the list above is a reasonable set of demands in good UI design.


    Also, could you please try to explain why is it better to set the window's freezepanes status to the same point every time the user navigates to the sheet, when you could only do it once when you are running other code (whatever OP is running).


    I would really appreciate it, because I just don't get why your way is better than mine.

  • Re: Freeze Panes VBA Without Select


    Fen,


    Quote

    Freeze panes on a certain cell, that may or may not be in view when the sheet is activated

    My 1st code showed that.

    Quote

    Ensure that the scroll position where the user left it when deactivating the sheet is preserved

    It won't change and will always show the VisibleRange at which is was left.

    Quote

    Ensure that the cells that user had selected before deactivating the sheet are kept selected

    Same as above, it won't change unless code is used to change it.

    Quote

    Do this without a displaying a distracting twitch

    All the above do exactly that.


    Quote

    Also, could you please try to explain why is it better to set the window's freezepanes status to the same point every time the user navigates to the sheet, when you could only do it once when you are running other code (whatever OP is running).

    I never said it was and it is totally dependent on the users needs.

  • Re: Freeze Panes VBA Without Select


    Short answer to the OP: Yes, you can. Contrary to pretty much everything I've seen on this subject around the web, amusingly enough. For the quick $0.20 answer to a $2.00 question, here's how:


    Code
    1. With Windows("My Workbook Name.xlsx")
    2. .SplitColumn = 1
    3. .SplitRow = 1
    4. .FreezePanes = True
    5. End With


    The reference you want is the Windows("My Workbook Name.xlsx"). That's the relevant part. The SplitColumn and SplitRow are the just the references to the cells you want to freeze. i.e. SplitColumn 1 is Column "A". SplitRow 3 would be Row 3. Hmmm. What do you think SplitColumn 5 would be? (A,B,C,D,E) Yeah, "E". You get the idea. But it's the Windows("My Workbook Name.xlsx") that you're really asking about here.


    Now that I've given you the proverbial fish, I want to at least show the fishing, as it were, though mostly because I really just want to address some of things that have been passed around in this thread (and really, all over the web about this subject).


    First thing, the whole "why would you want to?" question.


    Short answer: it doesn't matter. I feel that asking that question turns the purpose of the discussion away from "how do you do it?" and towards "can you provide me with an acceptable rationale to try?" It's like I'm on trial for wanting to do something, and it's frustrating to me when it seems like someone is more concerned about convincing someone else that figuring it out isn't worth it. It's like I have to justify my desire to do something before you'll be willing to help me. I don't want to have to justify my desire. I just want to know how to do it. Please don't make me argue my reason for wanting it. Please just say, "Here's how" or "I don't know." (Yeah, perhaps, "Well, depending on what you want to do, there might be a better way" is legitimate, but it almost *never* comes across as helpful.)


    Long answer: I'm running code. I like to be able to stop in and troubleshoot a code with the visual aspects of a spreadsheet intact like I did it all manually. That way, it saves me time if I ever have to go back and change/fix/add/alter/whatever something at a later date. I'm constantly learning, and my ability to write code is constantly evolving. If I wrote something a year ago, it's probably with a slightly different mindset on code than I have now. I want to be able to go back and step-through a year-old code, and be able follow exactly what's happening both on the screen and in the code. In order to do that, I want to be able to see it like I would have manually done it. In this case, that involves Freezing Panes at the position I would have if I were doing it manually. No I don't really care all that much whether or not you like my rationale, or if you agree with my reason, or whether you think the color pink looks good on the towels in the bathroom or not. Regardless though, I still want to know how to FreezePanes on a sheet that's not active. Ironically in this case, it's not even on a worksheet that ever gets saved or is even seen by the user. I create the tab in the code on the fly, temporarily use it to parse data, record the data, and delete the tab. But I still want to freeze the panes so I can step-through it later if need be. :)


    Okay, second thing, really the more fun one, in my opinion, Freeze Panes is Visual, and therefore impossible on an inactive window.


    Nah. Everything is binary. There are no "eyes", only "i's", as in "index." Think about it. From what most of us who deal in VBA either already know or are quickly learning, everything either has or is part of an index. Workbooks(Index) - Sheets(Index) - Range (Index) - UserForm.Controls(Index) - Shapes(Index) You get the idea. You can reference the index by either number or name. VBA doesn't really care. It just needs to be pointed to the index associated with the object, and the netherworld of VBA becomes your proverbial oyster.


    Now, most of us are familiar not only with the "index" aspect of pretty much every object under the sun in VBA, but also with the "cheating" shortcuts of ThisObject and ActiveObject i.e.: ThisWorkbook and ActiveWorkbook, etc. Really, that's all the "ActiveWindow" is. It's a cheat shortcut to an Indexed Object without referencing the index behind it (or is that "over" it?). Anyway, once I realized that the "ActiveWindow" command was just the cheat shortcut around the direct index catalogue of the object, I figured that the "Windows" object must be indexed just like every other object. So, a little tinkering, and I found that I could reference that Windows index through the name I gave the "Windows" object, just like I do with every other object in VBA.


    And voila! I don't need to have ActiveWindow, I can reference Windows(Index). In my current project, I've got a bunch of windows open, and I'm passing information back and forth between them (huge, massive indexed databases with 100's of 1,000's of records with dozens of fields in each record, manipulated through dozens of UserForms, etc.). Like, really big. 100 mb spreadsheets, etc. Not to impress you, just to impress *upon* you that memory is, well, precious at times! So I don't do anything active if I can get away with it. And I pretty much can. ;) So then I just


    Code
    1. MsgBox Windows(1).Index


    to figure out what index I needed to reference, and it returned the index number. Hmmm. Well that's pretty simple! So I plugged in


    Code
    1. MsgBox Windows(MyWorkbook.Name).Index


    and it returned the index number! Woo-Hoo! Now I'm getting excited, because I'm getting somewhere! :) So I played a little more and came up with


    Code
    1. MsgBox Windows(MyWorkBook.Name).Caption


    and it returned my Workbook Name! (Shocking! I know! That's like asking "What's John's name?" Right? ;) From there, plugging it in was pretty easy. I set my Workbook variable .Name in the Windows(Index) feature, and I have my


    Code
    1. Windows(MyWorkbook.Name).FreezePanes = True


    Ah, fascinating! :)


    From there, I just referenced the SplitRow and SplitColumn index (again, everything's indexed) to tell the VBA what cells to freeze, and suddenly the impossible again returned to I.M.Possible. Pretty much everything is. Whether I have rationale for it that you like or not. ;)


    Lastly, thirdly?, nextly?, (whatever) . . . there's also the UnFreeze To ReFreeze requirement. :roll: ??? Nah. The only reason we have to UnFreeze to ReFreeze manually is because it's a toggle button. It's an on/off switch, but that's just the interface, not the function. Just call it off. If it was off before, it's still off now. Or, just call it on. If it was on before, it's still on now. No reason to toggle it back and forth once you're underneath the GUI (graphic user interface) toggle. Just call it what it is. VBA understands. It's just like setting the .Left, .Top, .Height, or .Width of a UserForm (or any other object for that matter). You don't need to hide it to redefine it. You don't need to unload it. You just . . . redefine it. Same principle.


    For what it's worth (or not), there's my rant. There's my solution. There's my two cents on a long-dead, but here-to-fore unsolved thread. Hope it helps someone. Somewhere. Sometime. Somehow. In someway. Eh, probably not, but at least it's fun to fantasize about.


    James


    (In hindsight, I guess this turned into more of a blog than a post. Would that be a "plog", maybe?) And of course, now I'm playing around with trying to figure out how to freeze panes on a Worksheet that's not even visible. Why do I want do that? I don't. I just started thinking about it while writing all this, and it made me want to know how! ;)

  • My apologies for ressurecting an old thread. However found this very helpful, in working with VBA and freezing certain cells. However what is not clear to me, how does one point to the correct sheet they want to freeze with this method? If you have multiple sheets in a workbook.


    Right now I am having to format anywhere from 2 to 40 sheets, when I generate my new workbook from a macro. Having to go through and actively select them (via VBA of course) to freeze panes is certainly not desirable.
    Thanks!


    Code
    1. With Windows("My Workbook Name.xlsx")
    2. .SplitColumn = 1
    3. .SplitRow = 1
    4. .FreezePanes = True
    5. End With