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]

Sorry, but I don't have anything else that I can offer at the moment. Just curious, though, did you try the approach mentioned in that last link that I posted? It's this one here...

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

I haven't actually tried it or look at it closely, but it seems that it would certainly be worth a try.

 

[abdelfattah]

Hi Domenic again ,
I 'm surprised from my stupid bad !
after many tries and search in the internet all of solutions doesn't work for me

did you try the approach mentioned in that last link that I posted?

well, I did it and I'm not sure if I did it correctly but also nothing changes .
do you believe all of my problems from this :

should change to

it seemed to be impossible to solve it, but sometimes when somebody doesn't give up and try simple ways shouldn't be obstacle & should not underestimate of it .
I truly appreciated for your assistance & time .
thank you so much for your code .

 

[Domenic]

First, as you may know, the TextAlign property affects all columns within the ListBox. But I don't think that this is what you want, is it?

Secondly, just to be sure, did you try setting the font and font size for both your worksheet and listbox to the same fixed-width font? For example, if you set the font and font size for your worksheet and listbox to "Courier New" and size 10, and then run my original code in Post #2 , does it help?

 

[abdelfattah]

First, as you may know, the TextAlign property affects all columns within the ListBox. But I don't think that this is what you want, is it?

every column will be in center and the the column width in listbox will be based on same width size , so I don't see any affects(like unformatted).

Secondly, just to be sure, did you try setting the font and font size for both your worksheet and listbox to the same fixed-width font?

yes the problem is same thing . the last half column stay invisible .