how could autofit columns in listbox without specify values for each column

[tubrak]

Hi Guys

I need procedure to fit columns in listbox without specify values . should expand and contracts and control margins for width based on size of data for each column.

so when run the userform or search for data should autofill columns in listbox
I look forward to add some procedures to do that

Private Sub UserForm_Initialize()
  Dim LastRow As Long
  Set sh = ActiveSheet
  a = sh.Range("A1:F" & sh.Range("A" & Rows.Count).End(xlUp)).Value
  With MY_List
    .ColumnCount = 7
    .ColumnWidths = "20;50;100;100;70;70;70"
    Call CommandButton4_Click
  End With
End Sub
thanks

 

[DanteAmor]

Indeed it is because of the font size that you have in listbox. try like this:

Private Sub UserForm_Initialize()
  Dim i As Long, nmax As Long
  Dim sWidths As String
  
  Set sh = ActiveSheet
  sh.Columns("A:F").EntireColumn.AutoFit
  a = sh.Range("A1:F" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value
  
  For i = 1 To 4
    sWidths = sWidths & Int(sh.Cells(1, i).Width) + 40 & "  pt; "
  Next
  nmax = WorksheetFunction.Max(Int(sh.Cells(1, "E").Width), Int(sh.Cells(1, "F").Width))
  sWidths = sWidths & nmax + 40 & "  pt; " & nmax + 40 & " pt; " & nmax + 40 & " pt"
  With MY_List
    .ColumnCount = 7
    .ColumnWidths = sWidths
    Call CommandButton4_Click
  End With
End Sub

Result:

 

[tubrak]

this is better
much appreciated for help me for two both threads.