0

I'm looking for a way to do exactly what the title of this post states: using a userform to simulate a pane. This is for Ms Excel, and should be compatible from the versions 2010 forward.

Removing the userform's border so it has a flat like pane appearance I already know how. My main problem is the size of the userform being what a pane size is: always ajusted to the available excel window screen for this kinda things.

This being: a pane will always have the correct size to ajust to having or not a statusbar visible, having or not the ribbon visible, and so on. This is afected by the displayed items and, not sure on this one, screen resolution.

The sizing of my custom userform to truly simulate a pane is my problem, taking to account what I stated earlier, and that I don't know how to solve, since I can't find documentation that states how to find these mesures. The main one is, no doubt, the height to use.

Thanks!

JDF
  • 129
  • 1
  • 12
  • Since your userform will always cover (part of) the grid, how are you going to control scrolling? I expect your problem is easier to solve by using a .NET project with a custom task pane. – jkpieterse Jan 20 '16 at 10:10
  • The .NET pane I thought of that first. At first I will not need to scroll content. What I need will fit in a "normal" size area available from EXCEL. Of course if that's not the case and the visible/available area to size the userform is too short, I'll have to use the forms scrollbar to manage that. The need for this pane simulation is that I need for the "pane" to be available to anyone that uses this file. With your suggestion I cannot do that, unless I install the custom pane everywhere that the file can be used. Not practical, or even impossible at some point. Thanks. – JDF Jan 20 '16 at 13:11
  • Given the fact that you can embed controls in a worksheet and do all sorts of things to control how the worksheet looks and acts, why have a userform at all rather than a normally hidden worksheet which is made visible and active by whatever triggers the userform? – John Coleman Jan 20 '16 at 14:22
  • John Coleman, I might not understood your conclusion correctly, so my comment can seem off. First off, this is to have a more visual look, besides the function that I want to have. I could easily just have a normal userform to do that and the matter was over. That's not my aim, if I have alternatives. Second, it's a very heavy worksheet, and it's supposed to work like an entry data table (almost like a DB). So, for options, I don't want to overload this worksheet, and what's entry data goes to the table, what's not, goes to something that is not a worksheet, for clear differentiation. – JDF Jan 20 '16 at 15:14
  • Just curious. There are usually more than one way to do things. Sometimes I think VBA developers underestimate just how customizable a worksheet is. I am essentially lazy so if I wanted to make a userform look like a pane in a worksheet my inclination would be to make a pane in the worksheet have the desired functionality of the userform. since I suspect it might be less total work. – John Coleman Jan 20 '16 at 15:31
  • John Coleman: one of the things I would have to deal with objects in a worksheet to be managed as a pane or userform is scrolling of the sheet. This "set" would always have to be visible, and could not ever get hidden by other objects from the sheet. A real pane forces Excel to set a new avaliable area so it can fit. With a userform, I can scroll the sheet behind the form (not a true new available area, yes!), and never have to worry about keeping every item not movable, or getting resized, or something else that would spoil the "pane" appearance, or working with the sheet. Thanks. – JDF Jan 20 '16 at 15:38
  • You could probably do that with freeze panes, but then the idea becomes about as complex as what you are trying to do hence probably not worth it. – John Coleman Jan 20 '16 at 15:48
  • Have a look at this little example file I created: http://www.jkp-ads.com/downloadscript.asp?filename=PositionFormOnSheet.zip – jkpieterse Jan 20 '16 at 13:54
  • Holy Moly! First off, it is a very good base to work on. If I hide the ribbon, the maximizing actually ocupies the worksheet grid area. On a default environment (visible ribbon, statusbar, formulabar, scrollbars), the userform goes a bit over the bottom where you have scrollbar and the selectable list of sheets. The second part goes to the holy moly, as to the amount of code what's needed on this example...I never would thought to go WINAPI functions, at least not in the direction your example has. But good base to work on! Thanks! – JDF Jan 20 '16 at 15:27
  • jkpieterse: have you considered using unit converting between what Excel uses to mesure rows (or collumns) height (or width) and what vba uses for sizing mesure, and the property range.visiblerange and thus getting some actual height (or width) reading to size the form with? I don't know if it's possible, and I could be saying a lot of nonsence. I'll give it a go, when possible! Thanks! – JDF Jan 20 '16 at 15:42
  • Yes that is precisely what I did. But this isn't easy as the positions are off a bit due to (I think) window edge widths. – jkpieterse Jan 20 '16 at 15:49
  • Another problem is that the visiblerange is actually slightly larger than the visible cells because it includes the last row/column even if these are only partly visible. – jkpieterse Jan 20 '16 at 15:56
  • On you last comment, you are right. Not that I have tested it, but the principle is no doubt correct. But the comment about the window edge widths, that one I didn't get. In this new perspective with ranges and their sizes would only be the parcial visible hence giving an incorrect values to go by. So whats this problem with window edge widths you speak of? Could another possibility be setting the form as child of excel window and get mesures from that relation (child to parent)? What a cr@p! I'm probably giving up on this, cause I just don't have the time. Thanks all for your help! – JDF Jan 20 '16 at 21:03
  • The small drag border around any window is what I am talking about. For a userform it is the difference between the innerwidth and the width of the form. Somehow taking that into account makes the userform fit the width of the excel window better. The InnerHeight has a larger difference to the height due to the title bar. So you should subtract the difference between innerwidth and width from both width and height to be most accurate. – jkpieterse Jan 21 '16 at 05:48

0 Answers0