Userform auto-size

[mqdias]

Hi,

I constructed my program in a screen with a resolution of 1280x1024.
So, my userforms are quite big and when i open the excel file in another pc, the useforms overcome the boundaries. Is there any code that i can use so that the userforms are streched in function of the screen resolution?

Thanks

[Bob Phillips]

Develop your programs in an environment that mimics the worst case user's, and thgen you get no problems.

[herzberg]

I think simply resizing the form does not resolve the issue, as any controls beyond the form display area will be obscured, hence rendering those controls unreachable by the user. Then you would need to resize (and probably redefine the position) of the controls. Imagine writing code to rearrange all those labels, text boxes, command buttons and what-nots.

Like what xld suggested, I think developing stuff based on user's environment would be the way to go. Or maybe you can make the form smaller and use the multipage control to group the controls.

[unmarkedhelicopter]

Altering the size of forms is very difficult, the objects are all drawn in point sizes and thus if you want to halve the size of the form you would have to cycle through every thing in the form and halve their points, font = 12 to font = 6, image dimensions, comboxes, textboxes, lables etc.
You have no idea if the result will be even readable. More of a consideration will be the placement as each element will need moving and this is rarely by a given factor as something that was 82 points from the top 'may' be required to move to say 48 points due to the margin at the top. As Bob says, develop for the worst case, if it doesn't fit there you were going to have problems anyway, but you can always use tabbed or multipage forms and compartmentalise your approach.

[mqdias]

Thank you very much to all of you!

I understood all your answers. Maybe, if i have time, i'll make 2 versions on larger than the other.

Thank you all for the advic e!

[Bob Phillips]

With all respect, that is the WORST solution, a maintenance nightmare. There are so many other, better, ways to do it, it all revolves around design.

[mqdias]

I understand your answer.

The problem is that my knowledge in programing is not so good. Therefore i'll stay for now with the actual size.

But, if i need to resize it in the future, maybe i'll have to ask for help to a professional, because for what i understand by these answers is more complicated to resize again everything.

Thanks once more

[Bob Phillips]

Even if with resizing, you still have to have a form layout that looks good on the worst resolution machoine, so why not just start withg that?

[Andy Pope]

As the other have said, design for the lowest common resolution.

And when you come to adding resizing you may find this helpful.
http://www.andypope.info/vba/Anchor.htm

[mqdias]

Andy,

Thank you also for your answer. But, after i do the userforms in the worst resolution, does this userforms turn too big if i increase too much the screen resolution?

I think you may help me on this thing too:

I'm founding problems to protect my sheet as it's made in this case, that i attached. I don't know how to protect that way, to simply block all cells except the few ones that i want.
Another thing that we can see in this case and that i don't know how to do it too, is the disappearment of the row number and columns names

Thank you soo much and sorry for all this questions...

[Andy Pope]

Worst resolution = lowest resolution = (800 X 600) or even (600 X 480)

But did I miss something? There is no userform in your workbook.

Your workbook contains a sheet called RETScreen which looks like a form.
If this is the form that requires resizing? If so my web page will not help.

What you can do though is set the cells to fill the available area by adjusting the zoom.

And your worksheet is already protected with only the unlocked cells available for selection. Also the row and column headers have been removed.

The macro recorder is the best way to get the code required to do these things.

[mqdias]

sorry sorry sorry

that second part of the text wasn't ment to be posted here i copied to the wrong forum

Sorry, but thank you anyway
By the way, thank you for the link above, it was very helpfull

[Andy Pope]

No problem

[unmarkedhelicopter]

Andy, excellent example of how it can be done, but even this shows the sort of problems already discussed in this thread.
I'm intregued however, as you well know (being of the cognicenti) to design for the worst resolution (and still seeing your efforts on the resizeable form (where I suppose you can 'optimise' for different situations, the size of a text box)) but what did you design this for ?

[mqdias]

This is very complex calculation for heat exchangers, wich needs very space in the screen to put all the input data. First i thought to use MultiPage control, but the intention of this, for professional use, is for the user to see all the values at the same time, to have a wide vision of all that and make a correct adjustment.

But i understood that and i'll make that adjustments suggested!

Thank you once more

[Bob Phillips]

Then why not just use a spreadsheet? Tons more room, even at a low resolution, far easier to build, far easier to control.

[Bob Phillips]

Andy, excellent example of how it can be done, but even this shows the sort of problems already discussed in this thread.
I'm intregued however, as you well know (being of the cognicenti) to design for the worst resolution (and still seeing your efforts on the resizeable form (where I suppose you can 'optimise' for different situations, the size of a text box)) but what did you design this for ?

Tends to be more for resizing certain controls so that more data can be seen in one go, such as a multi-column/multi-row listbox. A good example is the NameManager addin, if you have the space it is enhanced by resizing.

[Andy Pope]

Hi unmarkedhelicopter,

Bob's correct about the real advantage of a resizable form. Only certain controls benefit from resizing most simply need to be re positioned.

The reason for writing the code was related to another of my pages that allows the userform to be resize without APIs. A user pointed out that while this was great you still had to code for the controls on the form changing position and or size. This is itself is not a problem, the user owns the form so can add any code required. But I thought this was a bit to hit and miss so set about writing a class to handle the problem.

I have yet to use the code in any of my userforms though.