ListBox Numbers Column Alignment

  • Hi All,



    Having searched through internet and almost all excel resources, I couldn't find any practical solution for numbers' column alignment in ListBox. A learner-developer friend has sent this code, which he found on excel forums (for convenience, I have attached the relevant workbook also)




    Userform Code:
    Code:


    Code
    1. Option ExplicitPrivate m_clsLBoxAlign As CListboxAlignPrivate Sub OptionButton1_Click() m_clsLBoxAlign.Left Me.ListBox1, ListBox2.ListIndex - 1End SubPrivate Sub OptionButton2_Click() m_clsLBoxAlign.Center ListBox1, ListBox2.ListIndex - 1End SubPrivate Sub OptionButton3_Click() m_clsLBoxAlign.Right ListBox1, ListBox2.ListIndex - 1End SubPrivate Sub UserForm_Initialize() Dim lngRow As Long Dim lngIndex As Long ListBox1.ColumnCount = 2 With Range("Sheet1!A1") Do While .Offset(lngRow, 0) <> "" ListBox1.AddItem .Offset(lngRow, 0).Text For lngIndex = 1 To ListBox1.ColumnCount ListBox1.List(lngRow, lngIndex) = .Offset(lngRow, 0).Text Next lngRow = lngRow + 1 Loop End With Set m_clsLBoxAlign = New CListboxAlign With ListBox2 .AddItem "All Columns" .AddItem "----Select Column---" .AddItem "Column 1" .AddItem "Column 2" End With End SubPrivate Sub UserForm_Terminate() Set m_clsLBoxAlign = NothingEnd Sub



    Now, I do know what class modules are but I don't know the coding structure at all.


    There is a listbox (listBox1) on the userform (Userform1), which shows Sheet 1 Column A data in 2 columns on ListBox (same data in 2 columns). We can select the column from ListBox 2 radio button, and then select radio buttons in Frame one, the that particular column on listbox (Column 1 or Column 2) will align according to the selected option.


    This seems to be a very advanced question but nevertheless I am taking my chance.


    Can an expert kindly help in how to change the row source for ListBox1 and include multiple columns and change the alignment of multiple columns individually with radio buttons ?


    Your help will be highly appreciated.



    Thank you for your kind support.





    **********************************************************************************
    NOTE: This question has been posted on another forum too, here's the link:
    http://www.mrexcel.com/forum/e…ers-column-alignment.html
    **********************************************************************************

  • Re: ListBox Numbers Column Alignment


    Hi Cytop,


    Thank you for your reply. Yes, the data is loaded during initialization of the UF. This is my question exactly:

    (1)
    how we can have dynamic row source (for example, A1 to E10), with multiple some text columns and some numbers columns


    Example: I have tried this:


    Code
    1. Me.ListBox1.RowSource = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Address(, , , True)


    But the UF shows only 3 columns, then when press any radio button for alignment, it gives Permission Denied error.



    (2) how we can change the alignment of numbers column (TextAlignRight) and text (TextAlignLeft)


    The code in OP is showing Column A twice in ListBox and the radio button gives option for only 2 columnns alignment in ListBow.


    Thank you for your support.


    Profound Regards,
    Shawn

  • Re: ListBox Numbers Column Alignment


    Your question was

    Quote

    help in how to change the row source for ListBox1


    I was just correcting slightly as Row Source (or more correctly 'RowSource') is a property of a ListBox and just wanted to make sure there was no misunderstanding.


    Quote

    and include multiple columns


    I don't understand why you ask this question - the original code already includes multiple columns.


    So to load from the range A1 to E10 you simply change the code that loads the listbox to load from that range rather than looping until a blank cell in found in Col A


    Setting a column right aligned is simply a matter of calling the class method passing the column index to change.



    Quote

    showing Column A twice


    The issue here is in this line - An Offset of 0 from Col A gives you ... the value in Col A...

    Code
    1. ListBox1.List(lngRow, lngIndex) = .Offset(lngRow, 0).Text


    Really would suggest you read the page on debugging - it would be a time saver for you.


    Add

    Code
    1. ListBox1.ColumnCount = 2
    2. '// New line
    3. Debug Assert False
    4. With Range("Sheet1!A1")


    And use F8 to step the code line by line querying the values of the various variables (see the linked page) for details.

  • Re: ListBox Numbers Column Alignment


    I tried Debug.Assert False but nothing happens, the loop continues.


    I tried hard-coding the RowSource. The UF shows the required number of columns but clicking on the radio buttons gives Permission Denied.


    I guess I'll have to hire help :)


    Anyways, thank you for your help.


    May God Bless You and Your Family and Give You the Desire of Your Heart and Fulfil all Your Dreams :thumbcoo:

  • Re: ListBox Numbers Column Alignment


    Do not use the .RowSource of the list box, that code demands that you fill the listbox other ways.


    Fortunatly, the replacement is simple


    Rather than

    Code
    1. Me.ListBox1.RowSource = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Address(, , , True)


    use

    Code
    1. Me.ListBox1.List = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Value


    I consider RowSource to be one of those "sounded like a good idea at the time" ideas that Microsoft occasionally has, somewhat like merged cells.

  • Re: ListBox Numbers Column Alignment


    One more question Mike, Is there anyway we can make the column alignment default. With the above codes, the user has to manually choose each column's desired alignment each time the UserForm opens and closes?


    Thank you

  • Re: ListBox Numbers Column Alignment


    Column Alignment is not a feature of Excel's listbox.
    It is a constructed feature of the custom Object described in the class module.


    Excel cannot be set to default to a custom object. (i.e. the ToolBox cannot be set to insert this custom object rather than Excel's built in ListBox object)

  • Re: ListBox Numbers Column Alignment


    Hi Mike,


    I do understand that issue now.


    However, given the above Class Module, I am not able to change my ListBox Column Width, if I do that, everything column is kind of squished up to left. What needs to be changed in the code above in order to have required column width?


    Thank you for your help.


    Regards

  • Re: ListBox Numbers Column Alignment


    I'm sorry it took me so long to reply.
    I had a lot of trouble making your "pad with spaces" approach work, that I devised my own solution.
    I'm finishing up the demo and I'll post it by tomorrow at the latest.
    Its a custom AlignListbox object that has many of the same properties as a ListBox. I hope its straight forward for you to use.
    Leter.


    BTW, I'm sure that you frequently use a ListBox's Change and Click events, are there any other ListBox events that you use frequently. (don't say Enter, Exit, BeforeUpdate or AfterUpdate :) )


    ONE MORE QUESTION: My Mac doesn't support RowSource, but it sounds like you are familiar with it. From what I can gather, RowSource is a string, the address of cells where the Listbox gets its values. One thing I'm not clear on is whether it establishes a two way connection. If the RowSource = "A1:A10" and one changes the ListBox1.List(2) with code, is that change reflected in the worksheet as well as in the list box?

  • Re: ListBox Numbers Column Alignment


    Hi Mike,


    Thank you for your reply. You are one of the greatest developers (Number 1 in my Top 5 :) )


    Basically, I have a UF (userform) and 2 controls: LB (listbox) and TB (textbox). My basic coding and developing strategy is to use RowSource property for LB when the UF initializes (the original RowSource)


    Then there is a TB, which I use to search. When I type in TB, Advanced filter macro runs and then I use those results as RowSource for the same LB. So effectively I reset the original RowSource and use the results of Advanced Filter as new source to display in the same LB. So there will be no change required in the worksheet.


    I am not sure about List property and its use given the straight forward nature of data display. The other thing is to have number's formatting (with commas and 2 zeros after the decimal.


    Thank you for your kind support.

  • Re: ListBox Numbers Column Alignment


    Thats very different that the impression I got from the OP.


    The OP lead me to believe that your issue was controling the alignment of the strings in a Listbox's columns. (And I've had a good time developing a custom control that does that.)


    Now it sound like there is also the (additional) desire for a TextBox that accepts number formatting.


    I'll get back to you on both of them.

  • Re: ListBox Numbers Column Alignment


    In the attached, there is code for clsAlignListBox. As well as two demonstration user forms.


    To utilize this object, the developer should make a userform and put a normal ListBox in the appropriate location and set the defaults for that ListBox (.Font, .BackColor, .ColumnCount, .ColumnWidth, etc.) as they desire.


    Then, in the userform's code module, they should declare a public variable, with events as clsAlignListBox. Set the AlignListBox.ListBox property to the ListBox. Thereafter, they ignore the ListBox and deal only with the AlignListBox.
    The properties and methods used for clsAlignListbox are almost identical for using a MSForms.Listbox



    I hope this helps you

  • Re: ListBox Numbers Column Alignment


    Great Mike ! This works like a charm. Thank you very much for your efforts for Developers Community and all other excel users. This was missing from the scene and now we can use the ListBox with column alignment and formatting.


    Please give me some time, I'll thoroughly test it for various data sets and come back to you.


    Many many thank :)

  • Re: ListBox Numbers Column Alignment


    Let me know. Once I get the column widths issues resolve and get this thing robust, this technique is lots of possibilities. (differential column formatting, visible grid lines, variable row heights, long list items that wrap rather than disappear off the side)