Problem calling a module in multiple userforms.

[KateD]

Hello,
I have used code for auto-sizing a list box from a question posted a while back. It was provided by alexofrhodes, and it works amazingly. However I want to be able to call on this module for listboxes on other user forms. All the listboxes have the same name "ListBox1". When I call on the code it comes up with "error 424" (see picture attached for where it bugs.) I really don't know enough to understand why it doesn't work if all listboxes have the same name. I have put in all the code rather than snippets in case I miss anything important. Thanks in advance for the help!

Here is the code I used from alexofrhodes It is in a module called "resizelist"

Function ControlsResizeColumns(LBox As MSForms.Control, Optional ResizeListbox As Boolean)
 Application.ScreenUpdating = False
    Dim ws As Worksheet
    'Dim mylistbox As ListBox
   ' Set mylistbox = LBox
    If sheetExists("ListboxColumnWidth", ThisWorkbook) = False Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "ListboxColumnwidth"
    Else
        Set ws = ThisWorkbook.Worksheets("ListboxColumnwidth")
        ws.Cells.Clear
    End If
    '---Listbox/Combobox to range-----
    Dim rng As Range
    Set rng = ThisWorkbook.Sheets("ListboxColumnwidth").Range("A1")
    Set rng = rng.Resize(UBound(LBox.List) + 1, LBox.ColumnCount)
    rng = LBox.List
    rng.Characters.Font.Name = Rechercher.ListBox1.Font.Name
    rng.Characters.Font.Size = Rechercher.ListBox1.Font.Size
    rng.Columns.AutoFit
    
    '---Get ColumnWidths------
    rng.Columns.AutoFit
    Dim sWidth As String
    Dim vR() As Variant
    Dim n As Integer
    Dim cell As Range
    For Each cell In rng.Resize(1)
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = cell.EntireColumn.Width + 10 'if not some extra space it cuts a bit off the tail
    Next cell
    sWidth = Join(vR, ";")
    Debug.Print sWidth

    '---assign ColumnWidths----
    With LBox
        .ColumnWidths = sWidth
        '.RowSource = "A1:A3"
        .BorderStyle = fmBorderStyleSingle
    End With

    
    '----Optionaly Resize Listbox/Combobox--------
    If ResizeListbox = True Then
        Dim w As Long
        For i = LBound(vR) To UBound(vR)
            w = w + vR(i)
        Next
        DoEvents
        LBox.Width = w + 10
    End If
        
    'remove worksheet
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
End Function

When I call it from the original userform it works. When I call it in the same way in another userform it doesn't. Here is where it doesn't work.

Private Sub UserForm_Initialize()

 Dim ws As Worksheet
   
    Dim rng As Range
    Dim rnglr As Long
    rnglr = ThisWorkbook.Sheets("données").Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String


    Set ws = ThisWorkbook.Sheets("données")
    

    Set rng = ws.Range("A1:O" & rnglr).SpecialCells(xlCellTypeVisible)

   With Me.ListBox1
       .Clear
       .ColumnHeads = False
       .ColumnCount = rng.Columns.Count

       ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

       rw = 0

      For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
           Next
          rw = rw + 1
      Next

        .List = Myarray
       .ColumnWidths = "60;60;60;60;60;60;60;60;60;60;60;60;60;60;60;60"
       .TopIndex = 0
    End With
    
    Call resizelist.ControlsResizeColumns(ListBox1, False)
    
    Dim lrow As Long
    Dim ii As Long
    Dim col As New Collection
    Dim itm As Variant
    
    
  
    With ws
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        On Error Resume Next
        For ii = 2 To lrow
            col.Add .Range("A" & ii).Value2, CStr(.Range("A" & ii).Value2)
        Next ii
        On Error GoTo 0
        
        For Each itm In col
            entreprise.AddItem itm
        Next itm
    End With

actionStage.List = Sheets("dataset").Range("E2:E8").Value
stagenum.List = Sheets("dataset").Range("I2:I4").Value


End Sub

 

[dmt32]

Hello again and thank you for your suggestions. I tried dmt32's first and I am not sure why but I didn't have much luck. However, I tried Gokhan Aycan afterward and it worked the first time.

I am so new to this that not everything makes sense but I am getting there. I will look at dmt32's again so that I understand what I'm doing wrong.

No worries, so long as you have found a solution

Dave