Problem calling a module in multiple userforms.


New Member
Sep 15, 2021
Office Version
  1. 2016
  1. Windows
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"
VBA Code:
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"
        Set ws = ThisWorkbook.Worksheets("ListboxColumnwidth")
    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
    '---Get ColumnWidths------
    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)
        LBox.Width = w + 10
    End If
    'remove worksheet
    Application.DisplayAlerts = False
    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.
VBA Code:
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
       .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)
          rw = rw + 1

        .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


  • Capture d’écran (89).png
    Capture d’écran (89).png
    49.6 KB · Views: 11


Well-known Member
Jul 3, 2012
Office Version
  1. 2019
  1. Windows
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


Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Latest member

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
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 "".
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