Determine whether textbox contains more than 1 line of text

[jbenson74]

I’m using Excel 2002

How can I determine in code whether a textbox on a form contains more than one line of text?

I have a form with a number of textboxes. Each textbox is 13 points high and therefore displays the first line of text for the field. If I click in the textbox its contents are displayed below in a larger textbox where I can view the entire entry and edit it.

I want the text in the smaller textboxes to change color if they are not displaying all the available text (ie if they have more than one line of text).
I’m currently doing this by searching for Chr(10) as below:

Private Sub UserForm_Initialize()
For Each MyControl In UserForm1.Controls
If Left(MyControl.Name, 4) = "Text" Then ‘If this control is a textbox
ColorLongOnes (MyControl.Name) ‘change the text color if it’s a long entry
End If
Next MyControl
…..

Private Sub ColorLongOnes(ThisTextBox)
If InStr(Controls(ThisTextBox).Text, Chr(10)) Then ‘If the text contains a carriage return
Controls(ThisTextBox).ForeColor = &HFF& ‘ Make the text red
End If
End Sub

This works fine most of the time however there are some long entries in the database for which this won’t work. (i.e – where the entry is just one long paragraph)

I thought about using
If Len(Textbox.Text) > X Then
but I don’t want to use a standard width font in the textboxes (where all the letters are the same width) so there’s no way to determine what X should be.

Can anyone help? I’ve searched the forum to no avail

 

[scifibum]

Hi James,

Access Reports have a "textWidth" method that is supposed to return the width of text in pixels. So, if you're able to:

1. Get the font properties of your text box
2. Instantiate an Access application + database + report
3. Assign the font properties to your report
4. use the textwidth method to find out the width of your string
5. compare the width of your string to the width of your original textbox

...then you'll pretty much be in the neighborhood of accomplishing what you need.

Unfortunately, Excel doesn't have a 'textwidth' method.

I started trying to code an example but i got bogged down in step 2 above...so I haven't had time to work it out. But I thought I would mention what I thought might work, and you can attempt it or maybe someone else has some more useful advice.

Good luck.

 

[scifibum]

Here's a thread about how you can determine the size of text (and presumably compare it to the size of a text box):

http://www.mrexcel.com/archive2/17500/19912.htm