Can you Automatically resize font of a text box depending on amount of text in the box?

[benJAMin14]

I have a report that has a text box that pulls information from a query. The text box is set to be a specific size and can not change because of the size of the report. There are many times when the text will not fit in the text box because there is too much or there is a big space in the box because there is too little text. Is there a way to code the text box to automatically resize the text and keep the font size somewhere between 6 and 14?

[rpeare]

Why would you want a report where the text size could vary wildly from record to record, that's going to make your report very hard on the eyes? Why not do something like have a 'short description' of the value so it will fit in a standard text box size. If this is a free form text box where a user can type in anything from 1 character to 255 character and it's not a lookup value I would just suggest setting the 'can grow' property of the text box to TRUE or YES and let your database figure out the proper sizing for the text box.

[CJ_London]

I agree could be hard on the eyes - and what if 6 is not small enough or 14 too large?

Take a look at Stephen Lebans website at this function https://www.lebans.com/autosizefont.htm

you might be able to utilise it in the appropriate format event

[benJAMin14]

It is needed because the report is printed out in order for my coworkers to understand how to do the next process in their jobs. So a short description would not work. They need the whole text to be able to understand what to do.

[benJAMin14]

Take a look at Stephen Lebans website at this function https://www.lebans.com/autosizefont.htm

you might be able to utilise it in the appropriate format event

Unfortunately I can't open the database he shows as a template because I have access 2016 and his database was created in an earlier version. Also I am fairly certain 6 to 14 will always work as the amount of text does not very a ton, but is just enough to be annoying.

[Micron]

Then you really need to redesign the report if you cannot limit the data to fit the space you have. Even if you can calculate the max characters AND the font that would work, you'd have to effect the change in design view, save it, then open the report. Worse, there is only one such control regardless of how many instances of it you see, so I'd bet that the calculated font size would apply to EVERY record, not individually.

EDIT - I see you posted while I was composing. I answered for a report, which is what your original post stated. I see that the example given is for a form, which would present a different problem. In a form, you can navigate from single record to single record and affect a property for only that view. Not so for a report, and I dare say, not for a form whose view is continuous or datasheet.

[CJ_London]

thought you could still convert it to .accdb - but I've done that for you here
AutoSizeFontVer21.zip