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: 18

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I am not at a computer at the moment but is "Rechercher" by any chance the "name" of the form is does work on ?
 
Upvote 0
You would have to reference userforms first as in:

Userform_Name.ListBox1....
 
Upvote 0
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
VBA Code:
Function ControlsResizeColumns(ByVal UF as Object, LBox As MSForms.Control, Optional ResizeListbox As Boolean)

Then modify every place which calls the function:
VBA Code:
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.
 
Upvote 0
Solution
Hi,
try changing your function as follows

Rich (BB code):
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

Rich (BB code):
Call resizelist.ControlsResizeColumns(Me, False)

and see if this helps

Dave
 
Upvote 0
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
VBA Code:
Function ControlsResizeColumns(ByVal UF as Object, LBox As MSForms.Control, Optional ResizeListbox As Boolean)

Then modify every place which calls the function:
VBA Code:
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.
 
Upvote 0
Hi,
try changing your function as follows

Rich (BB code):
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

Rich (BB code):
Call resizelist.ControlsResizeColumns(Me, False)

and see if this helps

Dave
Thank you I will try this as well and let you know.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top