New to Forms-I would like to auto size a Frame

I know the AutoSize option isn't given for Frames. But I would find such a feature very handy!

Would it be possible to write code that would auto-size a frame? (either at UserForm_Initialize or as as something that can be run on a selected Frame while editing a Form?)

how do you mean editing? Data Entry or designing the form?

I used "editing a Form" to mean "designing the form" (using the GUI to set up the frames, labels etc).

(But, as per post 1, I am also open to a solution that will autosize the frame on the form activate/initialize...)

what's your definition of Autosize? Should the frame fill the userform or should the controls in the frame fill the frame?

Why do you not want to create it the correct size whilst designing it? What changes at run time to want autosize?

Hello Andy,

autosize definition = same parameters as a label autosize. So the frame would shrink to the limits of the controls within that frame (similar to how a label shrinks to the size of the text within that label)

I would prefer to have the ability to autosize a frame while I'm designing. (I only mentioned the "at runtime" as an alternative just in case this solution would be easier to pull off)

I find it difficult to manually set frames as small as possible. A frame autosize feature would be handy

You would need to determine the area occupied by the controls within the frame and then resize.
Height and width are easy. Left and Top will requrie repositioning of all the controls within the frame.

How would I programmatically determine the area? Anyone?

Find the control nearest the top, the one nearest the bottom, the one furthest left and the one furthest right.

Then work out the width and height of an imaginary box that they would fit in, remember to leave a few pixels on each side for a 'margin'.

Thanks Norie +1.

I'm new to working with UserForms. How do I determine the position of controls within a form? Anyone?

Use the Top and Left properties to find the position of a contol within the frame.

Sorry I meant how do I test each control in the frame to find the highest Top & Left?

Could I loop through the controls within the frame? Also I would only have the top & left positions for the frame correct? Because there is no Right or Bottom properties for a control. So how would I determine those X & Y values?

If you want to find which control is nearest the top then look for the one with minimum Top value.

Nearest bottom, max Top.

Furthest left, min Left

Furthest right, min Left.

You know the Top, Left, Height and Width so it's straightforward to find the 'right' and 'bottom'.

Please Login or Register  to view this content.

In the example this happens via a button click so you can see it.

Wow! That is absolutely brilliant.

Only one small issue. To reproduce this:

1. Move the controls into a nice little block near the middle of the form
2. Run the Autosize
3. The Frame is shrunk to fit the controls
4. However the controls (and frame) have moved to the upper left corner of the form

I was imagining the frames top & left border to shift to just outside where the controls were.

To give a practical use for what I had in mind - At design time I move the controls (inside the frame) to where I want them to appear on the form but after I autosize - these controls have now moved from the position where I placed them. The controls should remain wherever the designer had placed them. Only the frame should shrink/move. Or is this bordering on the impossible?

The impossible part is to write code that does what you are imagining, rather than you detailing what you want

You have all the code you need to identify and move the controls around.
But I still don't see what the issue is with laying out the controls as you want at design time.

Yes I should made it more clear what I expected from a frame autosize.

But what you have done so far is brilliant and I will probably be able to work out the rest of it eventually thanks to the massive head start your code will give me. +1

Re your question about laying out controls at design time - I can never get the frames to look consistent. Either the margin between the controls is too big or too small. The ability to autosize the frame would solve this for me.

Just to recap what I'm after and break it down:

1. Determine the top/bottom/left/right of the new size for the frame based on the controls inside the frame (Solved by Andy Pope)
2. Resize the frame to these new dimensions (allowing for a small margin) (Solved by Andy Pope)
3. Move the frame on the form so that it is positioned just outside its controls (Currently unsolved)

Have you looked at the options available for aligning/spacing controls at design time?

Not closely enough. Forms is something I've only recently started learning.