Auto Resizing Font in a Textbox

Just to clarify, are you wanting the letters of the word to stretch to fit the box, or maintain their shape and just grow/shrink to fit?  If you make the box four times taller and keep the same width, should the text become four times taller too?

Mitch,
See Steve Lebans free autosize textbox at his link:
              http://www.lebans.com/autosize_textbox.htm

Satsumo - Thank you for your reply.  I want the text to maintain its shape and just grow/shrink to fit the textbox.  The text should not be distorted from its original shape, so if I make the textbox four times taller the text should not grow four times taller unless it can go wide enough to maintain its original proportions.  Thank you.

Mitch

puppydogbuddy,

Thank you for your reply.  The Steve Lebans link shows how to change the textbox to fit the text size.  But I want to change the text size to fit the textbox which is the reverse of what Steve Lebans proposes.

Mitch

All,

I hope I am not making this sound more difficult than it needs to be.  I simply wish to automatically expand / shrink text I type to fit a textbox of fixed size without distorting the text.

I think both Word and Excel offer a function to fit a textbox to text, but they don't offer a function to fit text to a textbox which is unfortunately what I need.

Any help would be appreciated.

Try another Lebans function code:
               http://www.lebans.com/limittextinput.htm
I know it is not exactly what you want,but should be a lot closer to what you want....something you can build on.

Here is a reference to the clipboard functions you you wanted:
                     http://allenbrowne.com/func-07b.html

Also, try this code I adapted from code written by "Marsh".  His code was written for a textbox on a report.  I am trying to adapt for a textbox on a form. If it doesn't work in the textbox's change event, I will have try somehing else.

Private Sub YourTextbox_Change()
Dim fs As Integer
For fs = 14 To 5 Step -1
If TextWidth(Me.thetextbox) < Me.thetextbox.Width Then
Exit For
End If
Next fs
Me.thetextbox.FontSize = fs
End Sub

I don't know of a function (in Windows) that will tell you what font size will show some text at a specific width.  You can find out how wide a piece of text is with a specific font.  You'd have to do a kind of search for the correct size.

Starting at size x, find the width of your text.  If its too wide, make x smaller, if its too narrow make x bigger.

The text width should scale quite accurately with font size.  If the box is 240 pixels wide and your text is 160  pixels wide at 12 point, then the 18 point version of your text should be nearly 240 pixels wide.  

However, you won't find a specific match of point size and box width very often.  If you want to improve the fit you can justify the text or add spacing to the font.

I could write a loop for this in C (or some kind of pseudocode) using windows API calls, if that would help.  Its a long time since I used VB.

You'd have to do this search whenever the text in the text box changed.  You don't mention whether you need multi-line text or not, that would complicate somewhat.

puppydogbuddy,

Thank you for your reply.  TextWidth is a valid function in VB, but not VBA which is where I will be implementing the code.  Do you know of a workaround for VBA?

satsumo,

Thank you for your reply.  Do you know what function or code to use to get the textwidth?

Lebans has it (freely available source code)!
view see:    http://www.lebans.com/textwidth-height.htm

TextHeightWidth.zip is a replacement for the Report object's TextWidth
and TextHeight methods. It is multiline aware and can work in both
Report and Form views. Includes a sample report to show you how to
autosize individual controls with different formatting on the same line
to simulate RTF style text.

puppydogbuddy,

Thank you for your reply.  This is written for an Access database.  Do you know if it will work for VBA?

it should. For one thing, Access operates within an integrated Development Environment (IDE), which includes VBA.  Secondly,Lebans packages his VBA code in user defined functions (UDF's) and API''s.  You should be able to use ok

puppydogbuddy,

I really appreciate your help.  I tried downloading Lebans package, but since I don't have Access on this computer I can't download it.  Any ideas on how to get this code.  Also, the code needs to run in a VBA.environment

Thanks for your help.

Mitch

I think puppydogbuddy provided an answer in comment #28893901. This just hasn't been confirmed by spinningtop.

Thanks satsumo.  I think we both collaborated to answer spinningtop's questions, and that any points awarded should be split evenly between us.  

Thanks puppydogbuddy.  In the case of a split I would still suggest that you should get more points than me.  Even measured simply by the number of responses your contribution is larger.

Sorry I have been away...

puppydogbuddy - I tried your 587 lines of code, but it does not work right out of the box.  I get all kinds of error messages when I try to run it in Word.

This should be a relatively easy thing to do.  IMHO it should not require 587 lines of code, just to automatically resize text to fit a textbox in VBA

Satsumo -  I'm sorry, but I don't know API and do not have the time to learn it right now.

Understood, I dont know VBA though I did work with VB for a few years.  Generally there is a good correspondance between functions in the API and methods in VB (after all, VB is largely an interface to the API).  If you don't get a workable VBA specific answer I could probably provide some pseudocode.

As stated above, puppydogbuddy has provided an answer in comment #28893901.  The problems the asker has will be differences in syntax.  The method will be the same no matter what language is used.  The code given may be useful to someone else searching for the same answer.

 I also think that some (or all) of the points should go to puppydogbuddy.

I object to the question being deleted, also.  The answers given by me under comment #28893901 and by Satsumo in comment #28828365 are the correct answers in the context of a VBA solution, and therefore, the question should be closed and the points should be split among the 2 experts that provided their input. The syntax of the code provided in comment # 28893901, which includes the required API modules, may need minor changes in syntax and the addition of some VBA library references in order for it to compile in the Word VBA editor, but the asker did not want to bother with API's or with the debugging process.