Yeah, I thought about that and that's going to be tricky because if you're using a variable font size or even font type, for example, you would have to change the calculations for the height and width. So, the only way to really get a good size for the box would be to use a standardized monospace font like Courier New and a specified font size like 16. At those things changes, you can adjust up and down, but a lot of it would be trial and error. I've added it to my list, and I'll take a look at it for a future video if I can come up with something halfway decent.

I was just tackling this issue in my own project.  The methodology I came up with:

STEP 1: Create a textbox that is, say, 3.25 inches wide and 3 inches tall (Figure 1 below).

STEP 2: Fill the textbox completely with characters.  With Calibri size 14, I can fill 12 lines of text comprising of 358 characters of ASCII code 33 to 126, including word wraps caused by the minus sign.  See Figure 1 below.  

STEP 3: Calculate approx. number of characters per line in the textbox.  From the above info, it is 358 / 12 = 29.8333.

STEP 4: Count the number of line breaks (vbCrLf) in the text.  Each line break adds a line in the textbox.  Richard has a video on finding how many substrings occur within a string.

STEP 5: Calculate the number of lines needed to be shown in the textbox to accommodate the message.  This equals to the length (in chars) of the message, divided by chars per line (Step 3), plus 1 (making sure the minimum is 1), plus the number of line breaks (Step 4).  You need to round down to the nearest integer.  E.g. 9.7537 lines = 9 lines.

STEP 6: Here is my special sauce.  For every 100 characters (an arbitrary number), I deduct the number of lines by 1.  This is to compensate for the inexact nature of this methodology -- the same way we compensate leap year and leap century with one extra day, because each year is not exactly 365 days, but 365.2437... something.

STEP 7: Convert the number of lines to "twips", the unit of height used by Access in VBA.  One inch equals 1440 twips (which stands for "twentieth of an inch point").  The textbox is 3" tall, so it is 3 x 1440 = 4320 twips in height.  The textbox has 12 lines, so each line is 4320 / 12 = 360 twips in height.  Multiply the number of lines (Step 5) with 360 to get the number of twips that should be the textbox's height.

The main challenge is really the math.   The resulting VBA code is only a few lines.

Could WizHook help here?

I love your algorithm, and yeah, that's about something along the lines of what I would do.

Hi Alex, I've tried using WizHook, but it can only adjust the width of a textbox that contains only single-line text.  It doesn't adjust the height to auto-fit multi-line text.  The line breaks caused by word-wrapping can be unpredictable, as shown in my picture ("-" and space, as well as justification can affect where word-wraps occur).  The WizHook function can figure out the widths of the alphabet of varying font styles and sizes, but apparently not where the line breaks are (which is the key), both soft and hard.