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

 

[Alex Blakenburg]

I am not at a computer at the moment but is "Rechercher" by any chance the "name" of the form is does work on ?

 

[Gokhan Aycan]

You would have to reference userforms first as in:

Userform_Name.ListBox1....

 

[Gokhan Aycan]

I believe my post will not be of any help as is.

Since the code won't know which userform is asking for the resize, you would have to change the function and add an additional argument for the calling userform.
This can be an object (the userform) or a string (userform name).

This is an example utilizing the userform object, don't just go change stuff yet

Modify the function

Function ControlsResizeColumns(ByVal UF as Object, LBox As MSForms.Control, Optional ResizeListbox As Boolean)

Then modify every place which calls the function:

Call resizelist.ControlsResizeColumns(Me, ListBox1, False)

Finally, inside the function, change all instances of "Rechercher" to "UF". UF can be anything else, just a variable name really.

I hope these make sense.

 

[KateD]

I am not at a computer at the moment but is "Rechercher" by any chance the "name" of the form is does work on ?

hello, No in fact it works in "Rechercher" not in the others.

 

[dmt32]

Hi,
try changing your function as follows

Function ControlsResizeColumns(ByVal Form As Object, Optional ResizeListbox As Boolean)
 
    Dim ws As Worksheet
    Dim LBox As MSForms.Control
   
    Set LBox = Form.listbox1

'rest of code

and modify the call as follows

Call resizelist.ControlsResizeColumns(Me, False)

and see if this helps

Dave

 

[KateD]

I believe my post will not be of any help as is.

Since the code won't know which userform is asking for the resize, you would have to change the function and add an additional argument for the calling userform.
This can be an object (the userform) or a string (userform name).

This is an example utilizing the userform object, don't just go change stuff yet

Modify the function

Function ControlsResizeColumns(ByVal UF as Object, LBox As MSForms.Control, Optional ResizeListbox As Boolean)

Then modify every place which calls the function:

Call resizelist.ControlsResizeColumns(Me, ListBox1, False)

Finally, inside the function, change all instances of "Rechercher" to "UF". UF can be anything else, just a variable name really.

I hope these make sense.

Hello, Thank you I will create another copy and try it there 1st. I had forgotten about the call to "rechercher". I had looked at it so many times and missed it! !! omg. I will post back once I have tried your sggestion.

 

[KateD]

Hi,
try changing your function as follows

Function ControlsResizeColumns(ByVal Form As Object, Optional ResizeListbox As Boolean)
 
    Dim ws As Worksheet
    Dim LBox As MSForms.Control
   
    Set LBox = Form.listbox1

'rest of code

and modify the call as follows

Call resizelist.ControlsResizeColumns(Me, False)

and see if this helps

Dave

Thank you I will try this as well and let you know.

 

[Gokhan Aycan]

Check dmt32's post, essentially the same thing, try mine in case later you might have differently named listboxes. Otherwise make the changes he proposed.

 

[KateD]

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.