Solved How to grow and shrink name horizontally

[SuperSonicSam]

Looking for a way to grow and shrink horizontally the name (together with the associated boarder and identifiers) to accommodate longer and shorter name lengths. The movement would need to occur only on the left hand side and the right hand side needs to remain fixed in place. BTW name on report is fictitious.

 

[theDBguy]

Sorry, I'm not following. Can you provide some more examples to help clarify what you mean?

 

[SuperSonicSam]

No problem .....so if the name on the report was say Frederick Samdombestonsen (I.e. a much longer name), then this wouldn't fit within the existing formating. In order to accommodate this longer name the txt box would need to expand significantly in the left direction. Just let me know if still not clear (I am a beginner)

 

[theDBguy]

No problem .....so if the name on the report was say Frederick Samdombestonsen (I.e. a much longer name), then this wouldn't fit within the existing formating. In order to accommodate this longer name the txt box would need to expand significantly in the left direction. Just let me know if still not clear (I am a beginner)

So, are you saying the size of the box should stay the same and only the font size for the name should change to fit within the box?

 

[SuperSonicSam]

No, I'd like the size of the box to increase (to the left) and the font size to stay the same

 

[The_Doc_Man]

You can manually resize the box (assuming you have room for it) by just computing the desired width in twips (1440/inch) and then reloading the box's .Width property (which is in twips). However, you can easily compute the size only with non-proportional fonts e.g. Times New Roman as the most common example, or Lucida Console as popular alternative. With non-proportional fonts, the string length in characters times the width of a character in that font gives you the answer, though I don't recall that two different non-proportional fonts are uniform in width for the same font size. Even the difference between normal and bold or normal and italic can complicate the works.

Many people use proportional fonts like Arial or Bookman Old Style as a matter of aesthetics, and there the sizing issue becomes much trickier. Individual character widths are not uniform so you cannot just multiply characters times font width. Routines exist in Excel that can determine the visible length of a string in a particular proportional font (face & size). Native Access doesn't have such a function. You would need to bring in the Excel library among your references to use the appropriate function in Access.

Any way to get visual width of character/string from VBA?

I understand any solution will be very complicated, but here's hopin'... I have a need to sort a column of text strings (basic ASCII characters only) by their printed width in a given font. Opt 1: Range.Autofit works but is not very accurate. Opt 2: ??? Opt 3: It looks...

www.mrexcel.com

The problem is when you deal with proportional fonts, the pixel width isn't easy to determine, particularly if kerning is in force, since in that case you need to know if it has occurred, and if so, how many times. Kerning, if you weren't sure, is the ability to slightly overlap letters when their serifs don't touch, so they take up less space than you might have expected.

Now if you don't care about a little non-uniformity of your text box when using proportional fonts, you COULD just center the text in the box and then compute it as number of characters times the width of the widest character in that font (which you might have to look up or measure.) In cases with lots of L and I (like Williams, e.g.), the name might have a little extra blank space at the ends vs. other names with the same number of characters, and I won't swear that the centering will be perfect either if the kerning was concentrated at one end or the other.

 

[isladogs]

See if you can make use of the information in this article

Autofit Text to Form Control

This article describes 3 different methods of ensuring all text is visible in a form control

isladogs.co.uk

 

[SuperSonicSam]

Many thanks The_Doc_Man & Isladogs! Definitely not as straightforward as I was hoping but lots of information for me to look into. Thanks again.

 

[isladogs]

The simplest solution is to use a Zoom box

 

[MarkK]

You can also check out the Report.TextWidth method.

 

[The_Doc_Man]

You can also check out the Report.TextWidth method.

Works on reports. Doesn't work on forms quite so well.

 

[KitaYama]

Works on reports. Doesn't work on forms quite so well.

the attached image in #1 is a report and the post is in reports sub forum.

 

[The_Doc_Man]

the attached image in #1 is a report and the post is in reports sub forum.

Darn. I missed that point. Thanks for reminding me. So in a report, TextWidth is another way to do this.

 

[Cotswold]

Why not adjust the size in OnLoad()?
Only problem is that you'll need to do the calcs in Twips, not pixels. As an example on one of my screens at 1920x1080 on the Access report a field 14cm long is about 8000 Twips.

So you could do a Len() on the name and decide the size in Twips for your screen. Len(Me!Field) multiplied by a number to give you the size you need, then Me!FieldNameOnReport.Width = 8000 or whatever. Maybe if you used a set font like Consolas it would be easier than with TTF?
Possible you'll need to swot up on Twips and how may to a cm or inch on your screen?

I shift buttons and other Objects about on a form in OnLoad() all the time, and it seems to work on a Report. ( I just did a little test and changed a Field's position and Width on a report )

 

[The_Doc_Man]

Why not adjust the size in OnLoad()?

That is one place where you can diddle with controls. OnLoad means the controls have been loaded even though they might not yet have data in the bound ones. I used to do my rearrangement in OnCurrent because I had some control buttons that were dynamically altered to represent the things you could do to that record based on its current status, which changed with each navigation. Controls aren't available until AFTER OnOpen because there, you have just opened up the form. Think of it like unpacking a suitcase. You can't hang up the clothes until you open the suitcase. You can't see the clothes to put them on until you have hung them up. If it weren't that the Load and Unload are reversed for the suitcase analogy, it would be perfect.

 

[Cotswold]

Adjusting Objet positions in OnCurrent() I haven't attempted.
I once mentioned on the Forum that when a form opens I have a function called from OnLoad() that can set eight standard buttons to four different positions on a form, where they remain. The function will shift them from being alligned horizontally along the bottom of a form, or to be on the right and vertically alligned. Ditto to the top or left. I was critisised and told in no uncertain terms that moving objects using code to change their design locations was something that shouldn't be done under any circumstances.

Obviously I gave the remarks the amount of attention and consideration they deserved.........zero!

 

[The_Doc_Man]

I was critisised and told in no uncertain terms that moving objects using code to change their design locations was something that shouldn't be done under any circumstances.

Obviously I gave the remarks the amount of attention and consideration they deserved.........zero!

I concur whole-heartedly. In my case it was command buttons that depended on information in the bound record to determine whether the business rules allowed certain actions. I only showed the commands that were allowed AND I moved the buttons to avoid having gaps in the set of remaining commands. For instance you did not have any visible controls to close a dirty form. You could never see CLOSE and SAVE at the same time. Nor could you see CANCEL and CLOSE together. Things like that reduced the things that people could do wrong.