+ Reply to Thread
Results 1 to 13 of 13

ListBox Column Width - Auto Resize according to text length that we get from the cell ?

  1. #1
    Registered User
    Join Date
    08-05-2016
    Location
    izmir
    MS-Off Ver
    2007
    Posts
    5

    ListBox Column Width - Auto Resize according to text length that we get from the cell ?

    Hello,
    i am using Search textbox (searching data all cells in all workbook) and get datas to in Listbox.Some cells have long definitions and doesn't fit in column in listBox.
    Can we do auto fit in a simple way ?

    the file u can find easily on the web : Find All VBA Form - All Worksheets.xls

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    You might use something like this to Auto-Size the listbox.
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    08-05-2016
    Location
    izmir
    MS-Off Ver
    2007
    Posts
    5

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    Thanks for reply, but these codes are not proper for the what i am looking for.At least for ColumnWidth for Listbox.
    Please check the file that i mentioned.it doesnt has a simple code to call spesific range to list in listbox.

  4. #4
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    I'm not sure what you mean by "not proper". Also the code I posted doesn't do anything with ColumnWidth. It is for a single column List box.
    All one has to do is add the Declaration of SizeBox, add the code creating SizeBox and the two routines MatchToListBox and AutoSizeListbox.
    Then call AutosizeListbox when ever you need it.

    A modification would make it applicable for a multi-column list box. The basic notion is to use a hidden text box to get the width needed to fit the Listbox contents.
    This does one single column, but it could be adapted for each column of a multi-column box.

    How many columns does your listbox have?

  5. #5
    Registered User
    Join Date
    08-05-2016
    Location
    izmir
    MS-Off Ver
    2007
    Posts
    5

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    Maybe 6 or 7 columns.In the file you can see there is 2 column.But even 2 columns,when cell has a 99 characters of text it doesnt fit the column.I dont want to give a spesific size to the columns from the properties panel.Because all cells are have different lengths of texts.

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    6 or 7 columns of 99 characters makes for a pretty wide listbox.

    After work, I'll put something together.

  7. #7
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    This will resize the column widths of a multi-column list box (or single column) to match the contents.
    AutoColumnWidths's first argument is the ListBox whose column is to be resized.
    It will set the column widths to fit the contents. (Hidden columns will not be unhidden.)
    If the ResizeListBox argument is True, the Listbox will be expanded or contracted to fit the width of the visible column.s
    If ReszeListBox is false, the column widths will be resized, but the listbox will remain the same size. Its possible that a horizontal scroll bar will show.
    If you want the sub to resize only some columns, the columns to be resized can be listed

    ResizeColumnWidths ListBox1, True, 0, 1, 3, 4, 8

    Will fit columns 0, 1, 3, 4 and 8 of ListBox1 to match their contents and then resize the Listbox to fit .


    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-05-2016
    Location
    izmir
    MS-Off Ver
    2007
    Posts
    5

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    Thanks mikerickson,I appreciate your work and time.But i dont have great knowledge as you.I couldnt apply these codes to my document.Can you check it what can we do ?
    Attached Files Attached Files

  9. #9
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    The user form in the attachment doesn't have any controls.
    The code in the user form looks like the form should have controls, TextBox_Find, Label_ClearFind, ListBox_Results and CommandButton_Close.

    But no controls.

  10. #10
    Registered User
    Join Date
    08-05-2016
    Location
    izmir
    MS-Off Ver
    2007
    Posts
    5

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    is it possible the apply these codes to my file ? Do you have any file with working example with your codes ?

  11. #11
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    Sure, here is a the workbook I used for testing the routine.

    Each sub in the Userform code module starts with a comment saying whether it is a needed routine or for demo purposes.
    You can select a column number with the option buttons and select a row from the listbox. The text box and command button will put that value in the list in the indicated cells. This allows the addition or removal of "Very long strings for testing and demo purposes".

    There is also a check box that determines if the resize effects the whole listbox or just the column widths.

    There are two command buttons at the bottom "Resize all columns" and "resize columns 1 & 3". The code they call shows how to resize only specific columns.
    Attached Files Attached Files
    Last edited by mikerickson; 08-12-2016 at 09:57 AM.

  12. #12
    Registered User
    Join Date
    06-14-2016
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    1

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    Hello, in this code would it be possible to include the columnhead in the measure for the autosize?
    Ex.:
    NumberOfPages | (as columnhead)
    16 | (as values)

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: ListBox Column Width - Auto Resize according to text length that we get from the cell

    Quote Originally Posted by diquintino View Post
    Hello, in this code would it be possible to include the columnhead in the measure for the autosize?
    Ex.:
    NumberOfPages | (as columnhead)
    16 | (as values)
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Listbox auto fit column width
    By sthiru in forum Excel General
    Replies: 13
    Last Post: 07-21-2021, 03:31 AM
  2. how to resize column width in the worksheets
    By aaaaa34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2014, 07:55 PM
  3. [SOLVED] Resize column width based on page setup settings?
    By Joel Mills in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2012, 03:13 PM
  4. Auto-resize Column Width
    By dk54 in forum Excel General
    Replies: 3
    Last Post: 03-29-2011, 10:55 PM
  5. Not showing column width during resize
    By branco in forum Excel General
    Replies: 2
    Last Post: 01-08-2010, 07:52 AM
  6. [SOLVED] Resize individual cells w/o changing the entire column width
    By Mary Ann in forum Excel General
    Replies: 3
    Last Post: 04-28-2006, 11:45 AM
  7. auto resize text without resizing cell in excel
    By Ferd in forum Excel General
    Replies: 1
    Last Post: 05-24-2005, 07:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1