Solved

Listbox Column Width

Posted on 2006-11-02
15
551 Views
Last Modified: 2008-02-01
How do I, in VB, automatically change the column width in a list box going on what the maximum Len count for each column.  


Please refer to question Q_22046000 in the 5th posting. Thanks KRIS
0
Comment
Question by:goingolfn99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17862449
hello Kris, I dont know about resizing dynamically to length of text. I am not aware of any AutoResize type function

I guess u know how to do fixed widths though
e.g.


lstBucket.ColumnWidths = "2.54cm;3cm;1cm"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17862485
try this

    Dim rs As DAO.Recordset
    Dim strCol1 As String
    Dim strCol2 As String
    Dim strCol3 As String
    Dim dblWidth As Double

    'There are 1440 twips to and inch, 567 twips to a centimeter
    dblWidth = 125
    'Len to return the maximum number of characters in each of the three columns
    Set rs = CurrentDb.OpenRecordset("Select max( len(Field1)) as lenX From TableX")
    strCol1 = (rs!lenX * dblWidth)
    Set rs = CurrentDb.OpenRecordset("Select max( len(Field2)) as lenX From TableX")
    strCol2 = (rs!lenX * dblWidth)
    Set rs = CurrentDb.OpenRecordset("Select max( len(Field3)) as lenX From TableX")
    strCol3 = (rs!lenX * dblWidth)
    Set rs = CurrentDb.OpenRecordset("Select max( len(Field4)) as lenX From TableX")
    strCol4 = (rs!lenX * dblWidth)

    Me.listboxname.ColumnWidths = strCol1 & ";" & strCol2 & ";" & strCol3 & ";" & strCol4
    Me.Repaint



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17862493
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 65

Expert Comment

by:rockiroads
ID: 17862500
ah, it seems link I posted is what cap has posted
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17862528
ok, well I took the info from that link and adapted it to your needs
I think this should do it


public sub SetColWidths()

    Dim i As Integer
    Dim j As Integer
'3 column array - this field holds max width
    Dim iMaxWidth(2) As Double
   
'Initialise to zero
    For j = 0 To 2
        iMaxWidth(j) = 0
    Next j
   
'Go thru each item in listbox and find max size
    For i = 0 To lstBucket.ListCount - 1
        For j = 0 To 2
            If iMaxWidth(j) < (Len(lstBucket.Column(j, i)) * 125) Then
                iMaxWidth(j) = (Len(lstBucket.Column(j, i)) * 125)
            End If
        Next j
    Next i

'Now display columnwidths based on max size    
    lstBucket.ColumnWidths = iMaxWidth(0) & ";" & iMaxWidth(1) & ";" & iMaxWidth(2) & ""

End Sub


I guess u put this code after setting lstBucket. Just call this func

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17862538
Well there u are. I didnt know how to do it before but now I know. Good question kris!!!
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17862613
Again Rock!! Very cool
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17862667
cheers Jeff, but I dont know if it works yet, I gave it a test, it seems ok. Wait for feedback from Kris
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17862861
The usual issue with doing such as this is that
iiiiiiiiiiiiiiiiiiii
is a very different width to
wwwwwwwwwwwwwwwwwwww
but they contain the same number of characters.

Stephen Lebans has a solution (shock horror) which can be ammended to work for a Listbox with a bit of fudging.
http://www.lebans.com/autosize_textbox.htm
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17865411
So it was enough then?
(Not trying to debunk Rocki's good work or anything - they don't call me Scully - and am always happy for him to have the points/credit in a question... as one day he might buy me a beer... yeah right! :-S)
But I'd have thought the limitation I mentioned would mean the result would be, at best, an average - an approximation of what the width needs to be.

Stephen Lebans' (he doesn't pay me to say this stuff) excellent work allows you to exactly predict such things - using a clever little virtual Device Context (which Access can't provide predictably enough for the API calls).
It's been around since like forever dude. (Well, used it in the native download 97 a while back anyway)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17865734
Leigh, I hadnt done col resizing before so 1st attempt. It seems to work okay, no problems with i's and w's
It may not be the greatest attempt though. And certainly what u have suggested, I aint seen that either.

And if your ever in the midlands, I'd be happy to buy u a beer, after you buy me 3 of course!!!

So is Dana your middle name then?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17866913
I'm a bit taller than her - and not as pretty.
Besides that we're like twins!..
(:-S)

OK - call me Fox instead.
Other's can debunk my work. :-)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17867474
Actually I envisaged you more like Skinner!
Not sure how much hair u got on top though
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17868016
Less than I had a couple of years ago!  
(In more ways then one... Maiden fan remember? ;-)

Skinner??
Assistant Director or Principal? :-)
Neither particularly flattering.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17868687
Well we were talking about X-Files but if u liken yourself to Simpsons :)

Well Brucie had a haircut once!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question