• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Static header VBA

lucasfrediani

New Member
I have a different challenge and I can't even imagine where to start ... but I believe that with macro VBA I can do something like this.
I have a spreadsheet in excel that I have the following situation ...
I would like the first 12 lines to have no bearing effect, either horizontal or vertical ...
But I didn't want it to be done by "freezing panels". Besides, I will activate this function with the cell "B15" activated, so that my worksheet works the right way.
an example spreadsheet is attached.
 

Attachments

AlanSidman

Well-Known Member
A file with dummy data showing a before and after scenario will help to explain what you want. Right now, I don't have a clue as to what you want.
 

lucasfrediani

New Member
A file with dummy data showing a before and after scenario will help to explain what you want. Right now, I don't have a clue as to what you want.
I put in this file "folder 01" the example ... I can't put the after because I can't do it .... but it will be basically how this file "folder 01" is configured so that with the first 12 lines without moving in any from directions ... as if I had frozen them, when I go down they do not decay, and when I move aside, they also do not move ....
As if I had used the "freeze panel" tool with the M13 active (and that would cause cell A1 to L12 not to move in any way ...)
and then freeze panels again with the B15 activated and that from that time works on that tool in the usual way, but I would ignore the existence of the first 12 lines ...
Not if I explained myself well, but the file shows more or less what I mean ...
 

Attachments

lucasfrediani

New Member
A new window then Arrange All Horizontally?:
View attachment 73003
It doesn't work ... it needed both to happen on the same spreadsheet ... when I use it it creates another spreadsheet ... I wanted to use these top phones as a menu, which will be on the screen all the time, but it needs to be part of from that spreadsheet. I believe that I can only achieve this through a macro.
 

lucasfrediani

New Member
I understand your placement ... but my goal is for everything to be visually pleasing. like that:

73019

and not with that cut, appearing to be two windows, you know ?!
 

lucasfrediani

New Member
What I wanted to do was make the 1:12 lines non-scrollable, and that's not for "freezing panels" because I'm going to use this function in another cell, but in the same spreadsheet, and I didn't even want it to affect the lines that I made non-scrollable ... and so that it appears to be a single spreadsheet and a single window ... Is that possible?
 

p45cal

Well-Known Member
I wanted to use these top phones as a menu
In your picture in msg#7, what's there? Is it some form controls? Perhaps I'm seeing a multipage control?
If so you could put this on a floating modeless userform.

(Also, have you considered creating your own tab on the ribbon?)
 
Last edited:

lucasfrediani

New Member
In your picture in msg#7, what's there? Is it some form controls? Perhaps I'm seeing a multipage control?
If so you could put this on a floating modeless userform.

(Also, have you considered creating your own tab on the ribbon?)
What is shown in message # 7 is the header that I want to always be on the screen.
It would be something like the floating non-modal user form but something that is inside the cells ... So I established a number of lines so that they would not suffer the effect of scrool, because that way my head would stay in them ...
I defined that until line twelve ... the cells will not be frozen at all ... neither side nor down ... then I would have the expected result.
 

p45cal

Well-Known Member
It would be something like the floating non-modal user form but something that is inside the cells ...
I dare you; attach desensitised workbook with what's appearing in your pictuer in msg#7, because I can't make out what's there (ie. what comprises your 'header').
 
Last edited:

lucasfrediani

New Member
I dare you; attach desensitised workbook with what's appearing in your pictuer in msg#7, because I can't make out what's there (ie. what comprises your 'header').
It would be like I used the freeze panels function twice ... the first with the K13 cell active ... and that would make the cell (A1: J12, do not move in any direction ... and then .. would use the same tool for the second time with the B15 activated ... and this time it would only have an effect on the cells below line 12, and it would work normally, in the example spreadsheet I believe that you can understand it better
 

Attachments

p45cal

Well-Known Member
I doubt very much a macro can use freeze (or split) twice on the same sheet/window.
So I'm looking for a way to give you the same effect (or functionality) in a tidy way.
My thoughts are leaning towards creating your own ribbon tab(s), which can appear/disappear according to which sheet you're on. If that's not a runner then perhaps using an ActiveX multipage on a sheet (textboxes can be linked to specific cells, selecting tabs can initiate actions, buttons too, etc.), or using shapes as you have done, but making sure they follow wherever you are on the sheet (not a favourite of mine), and then maybe a floating userform without a title bar.
You say you want to use this as a menu -
1. what sort of things are you wanting to happen when you use this menu (eg. getting data from elsewhere/making a pivot/changingto another sheet/re-arranging data/creating a chart/making a complex calculation)?
2. roughly how many menu items will you be wanting (just to get an idea of scale)?
 
Last edited:
Top