Auto Resize List Box Based On Text Size

[GC2010]

Is there a way in Access 2016 on my form to have my listbox auto-expand to the width of the text that is being populated in the list box?

Essentially for text that is only 50 characters in length the width of the list box is fine as is, but for anything longer than 50 characters can the list box auto-size to "fit" or to "show" the text?

 

[isladogs]

Welcome to AWF

There are various methods you can use.
Try the first item in the similar threads below.
Or look at the various links and ideas in this thread https://www.access-programmers.co.uk/forums/showthread.php?t=305276
Or use a zoom box. Either use the built in zoom box by pressing Shift+F2 whilst the textbox is selected...or create your own e.g. See http://www.mendipdatasystems.co.uk/move-forms-controls/4594549378

Hopefully one of those methods will be suitable

 

[GC2010]

Thanks for the quick reply and assistance! Using the link, I really like how this VBA code will auto set the height, but is there something similar that can set the width based off the text?

Private Function GetListboxRowHeight()
   'Wizhook converts row height perfectly
   WizHook.Key = 51488399
   Dim lx As Long, ly As Long ‘width & height of character string
   Dim LBRH As Long  ‘LBRH = listbox row height in twips
 
   With Me.lstContacts ‘listbox name
       If WizHook.TwipsFromFont(.FontName, .FontSize, .FontWeight, .FontItalic, .FontUnderline, 0, _
                "ABCghj", 0, lx, ly) = True Then
                LBRH = ly + 15         'font height +15 twips (1px space between rows)
       End If
   End With
     
End Function

 

[isladogs]

Hi

Ignore the listbox part of that example which was written for another purpose where width wasn't relevant.
Instead look at the zoom box in forms 1-3
Sorry if I wasn't clear before.

However do look at the thread I mentioned in the earlier link as well. There are two examples by Stephen Lebans written 20 years ago. One of those may be appropriate

 

[GC2010]

It looks like the boombox requires the user to either hover over it, or to click/double click it and a second box will display.

I was hoping for a way of the form to auto-resize the listbox based on the text length.

I'll go through the other link you provided and see if that provides anything specific for what I'm looking for. I see Post 10 by @Gasman converts the Database sample to a newer version but it seems to work for a textbox. Will it also work for a multi-select listbox?

 

[isladogs]

Sorry but we've been slightly at cross purposes. My fault.
Despite the fact that it says listbox in the title, I had until now been thinking about a textbox rather than listbox.
When you say resize depending on text length, I believe you are referring to changing the control's width and not increasing the height depending on the number of records in the list.
If so, would you want to determine the width based on the maximum list item size?
It would I think be tricky to code and in my view could look a mess.
If you had one or more very long record, the listbox could take up much of the form width and overlap other controls.

What's wrong with using the scrollbar?

 

[GC2010]

- no worries. I appreciate the assistance.

A scrollbar would be okay for height, but for width (if possible) I would like to have the listbox expand to show all the text. (i.e. no scrollbar)

 

[isladogs]

Not really sure why you think a vertical scrollbar is OK but not a horizontal one.
The listbox in the screenshot has both
If the listbox expanded it would obscure other controls on the form

 

[GC2010]

If I must use a horizontal scroll bar that is okay. I just would prefer to have the list box expand the full width. I am back tracking coming from a C# .NET background which is easy to do in that language, which is what led me to inquire if possible in Access & VBA.

 

[isladogs]

As i said earlier, its certainly possible to do if its important enough to you.
You would need code to calculate the characters in the longest listbox entry then modify the width in line with the space needed.
Of course that calculation would depend significantly on both the font size and the font itself with Arial Narrow and Wide Latin being at opposite extremes
If the listbox contained a large number of records I think there would be a delay in processing that information
If the user could modify the contents of the listbox then it would need to be updated each time. For example in my screenshot, the listbox shows all addresses for a selected UK postcode. With a different postcode the width would need to change - possibly by a lot.
I still think it would be messy

Another possibility is to enlarge or reduce the font size depending on the number of characters. However in my opinion, that also can look a mess.

However, its your project & your decision ....

 

[GC2010]

Thank you so much for your valuable insight. I think I will set the default text size to 8, and if the text is still longer than the width of the list box then the user will be forced to use the horizontal scroll bar.

 

[isladogs]

You're welcome.
Not sure what screen resolution you use but 8 pt would be very small for many screens.
I use 7 pt with automatic form resizing code which equates to about 11 pt after resizing on a typical monitor.