Format(Listbox1.List,( ),"0.00") to be Aligned correctly

N

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
684
Hello

I am getting values displayed in col 8 as

100.00
4500.00
30.00
in col 7 of listbox1 How can i displays as
000100.00
004500.00
000030.00

kindly ignore the zeros in above column at left before the values

Any correct syntax for Format(listBox1.List(iCount, 6), "0.00") so that all the numerical values are displayed right aligned

VBA Code: 
Private Sub cmdBtn_Click()

Dim myItem As Collection, blnSelected As Boolean
Dim i As Long, sItemsCount As Long
blnSelected = False
Set myItem = New Collection

For iCount = 0 To listBox1.ListCount - 1
     If listBox1.Selected(iCount) Then
         blnSelected = True
            
myItem.Add Array(listBox1.List(iCount, 0), listBox1.List(iCount, 1), _
                listBox1.List(iCount, 2), listBox1.List(iCount, 3), listBox1.List(iCount, 4), _
                listBox1.List(iCount, 5), Format(listBox1.List(iCount, 6), "0.00")
        sItemsCount = sItemsCount + 1
     End If

Next iCount
End Sub
NimishK
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
NimishK said:
Any correct syntax for Format(listBox1.List(iCount, 6), "0.00") so that all the numerical values are displayed right aligned
Click to expand...
Try this...

Format(Format(listBox1.List(iCount, 6), "0.00"), "@@@@@@@@@")
 
Upvote 0
Thanks Rick . Always prompt for syntax issues.
Format(Format(listBox1.List(iCount, 6), "0.00"), "@@@@@@@@@")
Click to expand...
? I tired it and result was Left Aligned
so Result was
100.00
4500.00
30.00
instead of right Aligned
NimishK
 
Upvote 0
After much exploration
found the coding of aligning column in listbox from Align Columns Differently in VBA UserForm ListBox - wellsr.com

ClassModule
VBA Code: 
Public Sub Center(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 0)

    Dim labSizer As MSForms.Label
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    Dim vntColWidths As Variant
  
    Set labSizer = m_GetSizer(LBox.Parent)
    If labSizer Is Nothing Then Exit Sub
  
    ReDim sngWidth(LBox.ColumnCount) As Single
    If Len(LBox.ColumnWidths) > 0 Then
        vntColWidths = Split(LBox.ColumnWidths, ";")
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = Val(vntColWidths(intColumn - 1)) - 5
        Next
    Else
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If

    With labSizer
        With .Font
            .Name = LBox.Font.Name
            .Size = LBox.Font.Size
            .Bold = LBox.Font.Bold
        .Italic = LBox.Font.Italic
        End With
        .WordWrap = False
    End With
  
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then

            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                labSizer.Width = LBox.Width
                labSizer.Caption = Trim(LBox.List(lngIndex, intColumn - 1))
                labSizer.AutoSize = True
                Do While labSizer.Width < sngWidth(intColumn)
                    labSizer.Caption = " " & labSizer.Caption & " "
                Loop
                LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
  
    LBox.Parent.Controls.Remove labSizer.Name
    Set labSizer = Nothing

End Sub

Public Sub Left(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 0)

    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    Dim vntColWidths As Variant
    ReDim sngWidth(LBox.ColumnCount) As Single
  
    If Len(LBox.ColumnWidths) > 0 Then

        vntColWidths = Split(LBox.ColumnWidths, ";")
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = Val(vntColWidths(1)) - 5
        Next
    Else

        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If
  
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then

            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                LBox.List(lngIndex, intColumn - 1) = Trim(LBox.List(lngIndex, intColumn - 1))
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
  
End Sub

Public Sub Right(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 1)

    Dim labSizer As MSForms.Label
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    Dim vntColWidths As Variant

    Set labSizer = m_GetSizer(LBox.Parent)
    If labSizer Is Nothing Then Exit Sub
  
    ReDim sngWidth(LBox.ColumnCount) As Single
    If Len(LBox.ColumnWidths) > 0 Then

        vntColWidths = Split(LBox.ColumnWidths, ";")

        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = Val(vntColWidths(1)) - 5
        Next
    Else

        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If
  
    With labSizer
        With .Font
            .Name = LBox.Font.Name
            .Size = LBox.Font.Size
            .Bold = LBox.Font.Bold
        .Italic = LBox.Font.Italic
        End With
        .WordWrap = False
    End With
  
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then

            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                labSizer.Width = LBox.Width
                labSizer.Caption = Trim(LBox.List(lngIndex, intColumn - 1))
                labSizer.AutoSize = True
                Do While labSizer.Width < sngWidth(intColumn)
                    labSizer.Caption = " " & labSizer.Caption
                Loop
                LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    LBox.Parent.Controls.Remove labSizer.Name
    Set labSizer = Nothing

End Sub

Private Property Get m_GetSizer(Base As MSForms.UserForm) As MSForms.Label
    Set m_GetSizer = Base.Controls.Add("Forms.Label.1", "labSizer", True)
End Property
in userform
VBA Code: 
Private Sub UserForm_Initialize()
    Dim lngRow As Long
    Dim lngIndex As Long
    Set MyListBoxClass = New CListboxAlign 'declare the class
'Below quoted from the link  
[Quote]
    [B][I]'This is just a sample where I add data to a listbox.[/I][/B]
[I][B]    'You'll want to use your own data.
    '-----------------------------------------------------------------------
   ' ListBox1.ColumnCount = 3
   ' With Me.ListBox1
   '     .AddItem
   '     .AddItem
   '     .AddItem
   '     .List(0, 0) = "First Product"
   '     .List(1, 0) = "Second Product"
   '     .List(2, 0) = "Third Product"
   '     .List(0, 1) = "Basic"
   '     .List(1, 1) = "Standard"
   '     .List(2, 1) = "Ultimate"
   '     .List(0, 2) = "$45.00"
   '     .List(1, 2) = "$79.00"
   '     .List(2, 2) = "$100.00"
   ' End With
    '-----------------------------------------------------------------------[/B][/I]
[B][I]    'This is how you left, center and right align a ListBox.[/I][/B]
[I][B]   ' MyListBoxClass.Left Me.ListBox1, 1
   ' MyListBoxClass.Center Me.ListBox1, 2
   ' MyListBoxClass.Right Me.ListBox1, 3[/B][/I]
[/Quote]

Dim rngSource As Variant, lstRow As Long
lstRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
rngSource = Worksheets("sheet1").Range("B3:J" & lstRow).Value     'i832

MyListBoxClass.Right Me.ListBox1, 6


With Me.ListBox1
     .ColumnCount = 10
     .MultiSelect = fmMultiSelectMulti
     .ColumnWidths = "80,55,80,80,80,110,80,50,35,55"
     .List = rngSource
  End With
End Sub

Private Sub UserForm_Terminate()
    Set MyListBoxClass = Nothing
End Sub

I changed in uf_init for what i wanted
somehow my listbox with column 7 could not get the desire result of Right aligning numerical values . So how can i use the code correctly from the above link
NimishK
 
Upvote 0
Just realised my mistake in column number
instead of
MyListBoxClass.Right Me.ListBox1, 6
changed to
MyListBoxClass.Right Me.ListBox1, 7
the above column was right aligned in listbox and as per class module i had to substract 1 column

But now unable to get its format using
Format(ListBox1.List(icount, 6), "0.00")

MyListBoxClass.Right Format((Me.ListBox1),"0.00"), 7)
?Error Object required

Any Idea How can i achieve this objective. in excel sheet column 7 is amount with 0.00 format

NimishK
 
Upvote 0
Guys
This is my final Try

I added one more Sub routine in Class Module as numericRight and tried to change the syntax of
LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
To
LBox.List(lngIndex, intColumn - 1) = Format(Format((labSizer.Caption), "0.00"), "@@@@@@@@@")
Result is
###100.00
#4500.00
#### 30.00
so
LBox.List(lngIndex, intColumn - 1) = Format(Format((labSizer.Caption), "0.00"), "@@@@@@@@@")
is not successfull

The lesser the numeric value the digit is moved to left
I dont understand why the digits are moved to left

Do i need to change the columwidth or How ?

ClassModule
VBA Code: 
Public Sub numericRight(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 1)
    Dim labSizer As MSForms.Label
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    Dim vntColWidths As Variant

    Set labSizer = m_GetSizer(LBox.Parent)
    If labSizer Is Nothing Then Exit Sub

    ReDim sngWidth(LBox.ColumnCount) As Single
    If Len(LBox.ColumnWidths) > 0 Then
        vntColWidths = Split(LBox.ColumnWidths, ";")
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = Val(vntColWidths(1)) - 5
        Next
    Else
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If

    With labSizer
        With .Font
            .Name = LBox.Font.Name
            .Size = LBox.Font.Size
            .Bold = LBox.Font.Bold
        .Italic = LBox.Font.Italic
        End With
        .WordWrap = False
    End With

    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then

            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                labSizer.Width = LBox.Width
                labSizer.Caption = Trim(LBox.List(lngIndex, intColumn - 1))
                     
                labSizer.AutoSize = True
                Do While labSizer.Width < sngWidth(intColumn)
                    labSizer.Caption = " " & labSizer.Caption
                Loop
                '''''LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
                LBox.List(lngIndex, intColumn - 1) = Format(Format((labSizer.Caption), "0.00"), "@@@@@@@@@")
                '''''LBox.List(lngIndex, intColumn - 1) = Format(labSizer.Caption, "@@@@@@@@@")
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    LBox.Parent.Controls.Remove labSizer.Name
    Set labSizer = Nothing
End Sub

Userform
VBA Code: 
MyListBoxClass.numericRight Me.ListBox1, 7
NimishK
 
Last edited:
Upvote 0
Finally ?
Ozgrid-Listbox Columns Alignment by MikeRickson

The above linked definately helped me to achieve the Right Alignment of any column in the listBox.
But there are other issues in the Aligned listbox of Class Module
As far as the Topic of the thread is concerned have achieved the objective but with dissatisfaction.
I don't know whether to hilite the issues in this thread or post a new thread all together.
?
NimishK
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Porting an Excel application to Access
Replies
6
Views
231
chazrab
C
A
  • Question
possible calculation in listbox for debit , credit when search based on textbox ?
Replies
0
Views
282
abdelfattah
A
S
  • Solved
Show the largest date and the smallest date on the listbox Userform
Replies
5
Views
124
Domenic
D
P
  • Question
Putting data into ListBox1 from UserForm6 to Userform1 listbox
Replies
5
Views
271
dmt32
D
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
110
Akuini
Akuini

Forum statistics

Threads
1,214,653
Messages
6,120,755
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top