Determine if text string will fit into label or text box area

[James2000]

Hi All,

Is there any way in VBA to check if a text string will fit into a Label or Text Box area?

For example, I have an Access form which contains a Label. The Label is 5.7 cm wide by 1 cm high, and its font is Arial 10 pt regular. When I run the following line of VBA code, the last word of the string is truncated :-

Label1.Caption = "Victor jagt zwölf Boxkämpfer quer über den großen Sylter Deich"

Is there any way I can calculate the height of a piece of text, if I know the width and the font? Or can I calculate how many times the text will wrap onto another line, if I know the width and the font? Is there a Windows function (in a DLL file) that I can use to do this?

Thanks for your time and help,
Best regards,
James

[JimDantin]

Is there some reason that you can't simply set the text box property "Can Grow", and let Access expand it automatically?

[HiTechCoach]

Is there some reason that you can't simply set the text box property "Can Grow", and let Access expand it automatically?

Because it is on a form not a report.

[JimDantin]

my bad

[jrajul]

Is there any way in VBA to check if a text string will fit into a Label or Text Box area?


For textboxes, yes, due to their selLength property. I do not know if it can be done with labels.



Is there any way I can calculate the height of a piece of text, if I know the width and the font?

This function should answer both of those questions (except for the part about labels). You pass to it the height of the font (measured in twips) and the name of the textbox.

Public Function Line_Count(FontSize As Single, txtbox As Access.TextBox)

If isnull(txtbox) then txtbox.height=fontsize: exit function 'It is pointless and impossible to run this code on empty textboxes.
Dim n As Byte 'used to iterate through the lines
'FontSize variable needs to be in twips, not in inches or pixels!
txtbox.Height = FontSize * 3.2 'this function does not work if the _
height is so low as to only fit one line. So you start by making sure _
it is big enough

With txtbox
.SetFocus
.Text = .Value 'Offsets the Access' tendency to select everything.

Do

'Here we select the text of one line at a time.
'We compare the number of characters in the selection to _
the number of characters in the entire textbox.

n = n + 1
SendKeys "{END}", True 'Move to the end of the first line.
SendKeys "^+{HOME}", True 'Select everything up to the end of the first line.

If (.SelLength = Len(.Value)) Then Exit Do 'Test to see if all text has been selected

'If there is more text beyond the selection (the first line) then do the following.
SendKeys "{HOME}", True

For counter = 1 To n
SendKeys "{DOWN}", True 'Go down one more line than last time
Next counter
Loop

Line_Count = n
txtbox.Height = Line_Count * FontSize
End With
End Function

The code above will only work if activated from a form in form view. It definitely will not work from the VBA editor.