Expand text box width to fit text 1

[nikademous]

Hello, I have a bound textbox named txtItemText and I need to have this textbox expand to the right to fit the text that's displayed. Is this possible and if so how?

 

[strongm]

Several ways of doing this. Do you want an API solution or a pure Access VBA solution?

 

[Andrzejek]

If you just want to 'display' the text, I would use a label instead.
But if you do want to use a text box, are you going to allow changing the text / typing a new text in it?
If so, what about if you want to display 'ABC' in this text box, but you want to replace the 'ABC' with a lot longer text? Do you want to expand the text box while you type?


---- Andy

There is a great need for a sarcasm font.

 

[nikademous]

Its a bound textbox and when the record changes the text changes in the textbox. I wanted the width to fit the textboxs text each time it changed...

 

[Andrzejek]

You did not answer any question... :-(


---- Andy

There is a great need for a sarcasm font.

 

[nikademous]

Turn that frown upside down....Here is some answers

If you just want to 'display' the text, I would use a label instead.

I cant because its a bound textbox...

But if you do want to use a text box, are you going to allow changing the text / typing a new text in it?

I have to use a text box because its bound and I don't believe you can show data from a table in a label. I will not be typing data into this text box it only displays data that has previously been entered elsewhere.

If so, what about if you want to display 'ABC' in this text box, but you want to replace the 'ABC' with a lot longer text? Do you want to expand the text box while you type?

I'm not typing into the textbox its going to display data already entered elsewhere so as the records change new data will be displayed and the text box will have to be expanded each time.

@stromgm, I would rather use a pure Access VBA solution


Hope this helps.... Thanks!

 

[strongm]

In that case - and assuming you really want to do this (there are reasons why MS removed an autoSize capability from bound textboxes):

[blue]Private Sub Text0_Change()
     Text0.Width = WidthFromFontWiz(Text0.Text, Text0.FontName, Text0.FontSize)

    [COLOR=green]' We need to add the padding[/color]
    Text0.Width = Text0.Width + Text0.LeftPadding + Text0.RightPadding
End Sub

Public Function WidthFromFontWiz(ByVal Caption As String, ByVal FontName As String, ByVal Size As Long, Optional ByVal Weight As Long = 400, Optional Italic As Boolean = False, Optional Underline As Boolean = False, Optional Cch As Long = 0, Optional MaxWidthCch As Long = 0) As Double
    Dim dx As Long
    Dim dy As Long
    
    [COLOR=green]' Activate Access WizHook object[/color] 
    WizHook.Key = 51488399
    
    WizHook.TwipsFromFont FontName, Size, Weight, Italic, Underline, Cch, Caption, MaxWidthCch, dx, dy
    WidthFromFontWiz = dx + 15 [COLOR=green]'1 pixel short ...[/color]

End Function[/blue]

 

[nikademous]

Strongm, that works great thank you but one issue. The textbox doesn't extend long enough to show the full text, it cuts off half of the last letter. How do I extend it wider?

 

[strongm]

That'll almost certainly be because you have a border around the textbox which hasn't been calculated into the new width (my example only allowed for the internal padding in the control). So you should just need to change

[tt]Text0.Width = Text0.Width + Text0.LeftPadding + Text0.RightPadding
[/tt]
to

[tt]Text0.Width = Text0.Width + Text0.LeftPadding + Text0.RightPadding + Text0.BorderWidth * 2 * 15[/tt]

 

[nikademous]

Strongm, I added it and its still not showing the full text, its cutting off half of the last word. I search the web for WizHook because I never heard of that and first thing was the code below so I tried it and It worked but I changed the 40 to 130 because it cut off the last bit also... Thanks!

Link I Found

Public Sub AutoFit(ctl As Control)
    Dim lngWidth As Long
    lngWidth = GetTextLength(ctl, ctl.Value)
    ctl.Width = lngWidth + 130 '40
End Sub

Public Function GetTextLength(pCtrl As Control, ByVal str As String, _
        Optional ByVal Height As Boolean = False)
    Dim lx As Long, ly As Long
    ' Initialize WizHook
    WizHook.Key = 51488399
    ' Populate the variables lx and ly with the width and height of the
    ' string in twips, according to the font settings of the control
    WizHook.TwipsFromFont pCtrl.FontName, pCtrl.FontSize, pCtrl.FontWeight, _
                          pCtrl.FontItalic, pCtrl.FontUnderline, 0, _
                          str, 0, lx, ly
    If Not Height Then
        GetTextLength = lx
    Else
        GetTextLength = ly
    End If
End Function

 

[strongm]

All they are doing is using a magic number instead of being explicit which my code is.

For example, with default padding (30 twips) and a border width of 2 pixels

[tt]Text0.LeftPadding + Text0.RightPadding + Text0.BorderWidth * 2 * 15[/tt]

sums to 120, so

[tt]Text0.Width = Text0.Width + Text0.LeftPadding + Text0.RightPadding + Text0.BorderWidth * 2 * 15[/tt]

would be be equivalent to

[tt]Text0.Width = Text0.Width + 120[/tt]

The fact that you got it working with a magic number rather than my code (which should provide a pretty much the same result) suggests that you probably modified my code in such a way that

[tt]Text0.Width = Text0.Width + Text0.LeftPadding + Text0.RightPadding + Text0.BorderWidth * 2 * 15
[/tt]
didn't actually get run ...

 

[nikademous]

Actually I had no idea what WizHook was and still don't, that's how I found the code. I also had no idea what the 2 * 15 was for now I do and can modify the numbers a little to get it working. I entered exactly what you had given me as the example with the + Text0.BorderWidth * 2 * 15 modification and still received the same result. I'm going to go back and try adjusting the numbers a little and see what it does...

I have a second question and have no idea going about it. I am trying to create a switchboard menu that I can use for future databases and am having trouble with toggling the footer so that it shows only when certain selections are made and hides if they don't using a table with a Yes/No for each selection that I want to Collapse/Expand it. Should I start a new thread or pose the copy here and would you be able to help?

Thanks,

 

[strongm]

New thread for a new question

 

[sxschech]

Couple of other ideas, instead of changing the size of the textbox, could you change the point size of the font, something along the lines of:
[tt]If number of characters > 30 then point size = 6 else point size = 8[/tt]

strongm's code is more flexible, but if you don't mind hard coding some values, this is from an application where I had a data sheet form and used a formula to adjust the size of the col widths based on character width, of course it was predefined, so may not be of much use, but perhaps could be adapted? Below was based on 8 point Calibri

Const TWIPSTOINCHES = 1440
'TWIPS times (number of inches for 1 character at 8 point Calibri)
Const TWIPSTOCHARWIDTH = TWIPSTOINCHES * 0.06    
Me.txtFamilyTree.ColumnWidth = TWIPSTOCHARWIDTH * 5 'desired character width of 5 characters
Me.txtSearchTerm.ColumnWidth = TWIPSTOCHARWIDTH * 25

 

[nikademous]

Strongm, I tried yours again and got it working by using the below:

' We need to add the padding
txtItemText.Width = txtItemText.Width + txtItemText.LeftPadding + txtItemText.RightPadding + txtItemText.BorderWidth * 2 * 15 + 50

Can you explain why? Thanks!!

 

[strongm]

There can be a number of reasons - for example, are you using a bold font, which would have wider characters? If so, you need to tell the function. My example was exactly that - an example of what you can do, not a complete solution, and thus did the bare minimum. But is set up to do more.

So in my code, we'd deal with this by changing

[tt]Text0.Width = WidthFromFontWiz(Text0.Text, Text0.FontName, Text0.FontSize)[/tt]

to

[tt]Text0.Width = WidthFromFontWiz(Text0.Text, Text0.FontName, Text0.FontSize, Text0.FontWeight, Text0.FontItalic, Text0.FontUnderline)[/tt]

 

[strongm]

>based on character width

based on average character width ... In these days of proportional fonts this is less useful than it might be

 

[strongm]

>Can you explain why? Thanks!!

And now that I have seen your project - yes, I can

You have a right margin set on the textbox. So the resizing needs to allow for that as well... your code would become

[tt]txtItemText.Width = txtItemText.Width + txtItemText.LeftPadding + txtItemText.RightPadding + txtItemText.BorderWidth * 2 * 15 + txtItemText.RightMargin + txtItemText.LeftMargin[/tt]