How do I make the Label.SizeToFit Method only adjust the label vertically?

[ohmydatabase]

I am creating labels through VBA and Im running into issues with sizing.

One of my issues is that Label.SizeToFit is altering the width of my form. I have a form which I'd like to be 14" wide. I defined this before I placed any labels on the form. When I put a label on the form that has a caption longer than the defined label width (say 12") and call the .SizeToFit method, Access with automatically choose to adjust the width of the label instead of the height. This causes the label expand (let's say to 15"). This in turn causes my form to grow to 15" and is not the desired result.



I'd like for the label to only expand vertically and have it not change the defined width of the label. Funny thing is when I do this manually, (RightClick-Size-ToFit) it chooses to size the same label vertically.

Anyone have any experience with these.

[aytee111]

There is a property called Can Grow which is used by Access to increase the field vertically.

[June7]

Labels don't have CanShrink and CanGrow properties. So unless your 'Label' is really a textbox, can't have dynamic height.

What do you mean by 'printing labels' - are these adhesive envelope or folder labels?

[Micron]

Labels don't have CanShrink and CanGrow properties. So unless your 'Label' is really a textbox, can't have dynamic height.
What do you mean by 'printing labels' - are these adhesive envelope or folder labels?

Labels and other controls have a SizeToFit method. It's only available in design view, so I don't see the point of it for the OP.

[June7]

I agree, SizeToFit is useless for OP's requirement. They want vertical adjustment to be dynamic. And that also does not make much sense to me for the purpose of a 'Label'.

[ohmydatabase]

Labels don't have CanShrink and CanGrow properties. So unless your 'Label' is really a textbox, can't have dynamic height.

What do you mean by 'printing labels' - are these adhesive envelope or folder labels?

I'm back to a problem you've helped me out with before @June7. I'm attempting to assemble a form (through VBA) in which I add labels to assemble a bulleted list that actually behaves like a ListBox. I incrementally step through a DAO.Recordset to define the headers of each section of the bulleted list and also populate the main information. Ultimately this isn't something that is being physically printed. I'm after a GUI that fits my needs more than the pre-built ListBox or any janky code I come up with using a single TextBox. I essentially make every piece of data it's own control, but this is tangent to the point of the current thread.

Here's what I learned. It's better to shrink labels than it is to grow them. The only time a label grows horizontally using the FitToSize Method, is when there isn't enough space vertically. (When the label control is first created) So, my solution to this is to make a blank label that is HUGE. (But with my desired width) I then set the .Caption to whatever text I need. Lastly I use the .FitToSize Method. This causes the label to shrink vertically, yet holds the width constant.

[ohmydatabase]

Labels and other controls have a SizeToFit method. It's only available in design view, so I don't see the point of it for the OP.

Ultimately, I should have chosen to code my GUI in an application other than Access. I'm not using Access for it's conventional uses. I'm finding that to get the customization I really want, I have to fight Access to behave the way I want. However, Access does provide a nice platform for me to develop on and was a nice introduction to application development and database design. Another route I saw was to use continuous forms. However, my mentor at work isn't the biggest fan of them so I'm going to treat that as a last resort. (This custom form I'm currently cooking up with the all the labels is the clearest path to my desired UI I've come up with)

And I do use the .SizeToFit Method in Design View. I have to be in Design View in order to CreateControls in VBA. I've opted for labels instead of TextBoxes because I was having trouble getting the correct information to display in the TextBox. (Since I was in Design View) Labels proved to be much simpler.