Resize columns in listbox based on columns size in sheet

[abdelfattah]

Hi guys

I have code to search on userform but my problem I cant arrange columns width in listbox some columns are stuck each other and some columns are big space between each other

I don't want tradition way as known like this

.ColumnWidths = "80;80;120;80;60;60;60;60;60;60"

I want the ColumnWidths as the same thing inside the sheet to avoid problem unarranged columns in listbox
here is part of my project

Private Sub LBoxPop()
  Dim i&, j&, x&
  Dim myFormat(1) As String, crit As String
  Dim cmb2 As Long
 
  If ComboBox1.ListIndex = -1 Then Exit Sub
  Set WS = Sheets(ComboBox1.Value)
 
  Data = WS.Cells(1).CurrentRegion.Value
  myFormat(0) = WS.Cells(2, 8).NumberFormatLocal
  myFormat(1) = WS.Cells(2, 9).NumberFormatLocal
 
  ReDim Temp(1 To UBound(Data, 1), 1 To 10)
  x = 1
  For j = 1 To 10
    Temp(x, j) = Data(x, j)
  Next
  
  For i = 2 To UBound(Data)
    If TextBox1.Value = "" Then crit = Data(i, 4) Else crit = TextBox1.Value
    If ComboBox2.Value = "" Then cmb2 = Month(Data(i, 2)) Else cmb2 = Val(ComboBox2.Value)
    
    If Data(i, 4) Like crit & "*" And Month(Data(i, 2)) = cmb2 Then
      x = x + 1
      For j = 1 To 10
        Temp(x, j) = Data(i, j)
        If j = 2 Then Temp(x, 2) = Format(Data(i, 2), "DD/MM/YYYY")
        If j >= 8 Then Temp(x, j) = Format$(Data(i, j), myFormat(1))
      Next j
    End If
  Next i
 
  With UserForm1.ListBox1
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "80;80;120;80;60;60;60;60;60;60"
    .List = Temp
  End With
End Sub

thanks

 

[Domenic]

Try replacing...

  With UserForm1.ListBox1
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "80;80;120;80;60;60;60;60;60;60"
    .List = Temp
  End With

with

    Dim dataRange As Range
    Set dataRange = WS.Cells(1).CurrentRegion
   
    Dim columnIndex As Long
    Dim columnWidths As String
   
    columnWidths = ""
    With dataRange
        For columnIndex = 1 To .Columns.Count
            columnWidths = columnWidths & .Columns(columnIndex).Width & ";"
        Next columnIndex
    End With

    With UserForm1.ListBox1
      .Clear
      .ColumnCount = 10
      .columnWidths = columnWidths
      .List = Temp
    End With

Hope this helps!

 

[abdelfattah]

thanks

seem to work greatly except the last column .

it doesn't show the whole amounts in last column , it will show half of amounts and when try expand list box will create big space between columns 9,10 in listbox ,even that doesn't show
the whole amounts in column10 . I tried expand and contract the listbox but unfortunately the problem is from the last column (doesn't show the whole amounts in column 10). I don't know what's the problem!

 

[Domenic]

I tested the code with data containing 10 columns, and the last column always show no matter how I set the width on the worksheet.

What kind of data does it contain?

 

[abdelfattah]

bad lucky !
in reality when make the right to left is true from windows properties to show data from right to left , then shows this problem but if make the right to left is false then will run well as you said. so there is no way to solve this problem ,?
I should use from right to left to show data from right based on my language , not from left based on English language.

 

[Domenic]

One way might be to loop through the 10th column in Temp() to pad the text string with the appropriate number of spaces to the left of it. Although, it might be little tricky to do so.

Another option might be to use the ListView control that allows you to set the alignment for each column, except the first one, which must be aligned left. Have you consider using the ListView control?

 

[abdelfattah]

One way might be to loop through the 10th column in Temp() to pad the text string with the appropriate number of spaces to the left of it. Although, it might be little tricky to do so.

not really understood .

Another option might be to use the ListView control that allows you to set the alignment for each column, except the first one, which must be aligned left. Have you consider using the ListView control?

I don't used to use listview

 

[Domenic]

Basically, first you would make sure that you use a fixed-width font, such as Courier New. Then determine the total number of characters that your field for Column 10 can hold, let's say 15. Then you would loop through each item in the 10th column of your array Temp(), and then add the appropriate number of spaces to the right of the text for each item, for example...

    Dim i As Long
    For i = LBound(Temp) + 1 To UBound(Temp)
        Temp(i, 10) = Temp(i, 10) & Space(15 - Len(Temp(i, 10)))
    Next i

However, you might want to take a look at the following link for another way to approach this...

Align Columns Differently in VBA UserForm ListBox - wellsr.com

Learn how you can align the text in each column of your UserForm ListBox differently by using the class module I introduce in this tutorial.

wellsr.com

 

[abdelfattah]

thanks for your suggestion .
but that doesn't work when change showing data in listbox from right to left.

 

[abdelfattah]

Hi Domenic
I know you can't help anymore,
just I want to inform you when the data are small in listbox ,then will show all of data whether right to left or left to right , but if the data are big in list box will show scroll bar beside(up to down) then will be the last column does not show the whole amounts as the others column.