Problem calling a module in multiple userforms.

KateD

New Member
Joined
Sep 15, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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"
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"
    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.
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
       .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
 

Attachments

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  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

Dave
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,148,371
Messages
5,746,308
Members
424,006
Latest member
Metal_warrior

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 MrExcel.com.
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 "mrexcel.com".
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
Top