Runtime Error when calling userform with listbox

user_14326480 0
Viewed 53

hope you can help me out here, because I really can't see the error.

I'm trying to create a Userform with a listbox, where the user can choose values from an array. The code for the array is below:

Dim arrayData   As Range
Dim sh As Worksheet
Dim Row_Count As Integer
Dim i As Integer
Dim lastRow2 As Long

Set sh = ThisWorkbook.Sheets("Import")
lastRow2 = sh.Columns(45).Find("*", , , , xlByRows, xlPrevious).Row

Set arrayData = sh.Range("AS2:AS" & lastRow2)

arArray = sh.Range("AS2:AS" & lastRow2)

Row_Count = arrayData.Rows.Count

   
For i = 1 To Row_Count

  
    arArray(i, 45) = Cells(i, 45).Value

Next i

This works perfectly. Now I'm initializing the Userform:

Public Sub UserForm_Initialize()



Auswertung.Lst_Tabellen.List.Clear


Auswertung.Lst_Tabellen.List = arArray


End Sub

But everytime I'm trying to call the userform "Auswertung", I get the error "Runtime Error 424: Object Required". Can you guys see the issue?

Public Sub Call_Userform()

Auswertung.Show


End Sub

The Debugger marks the line "Auswertung.Show", when I run the code.

userform vba excel listbox
2 Answers

As I mentioned, you need to populate the array inside UserForm_Initialize()

Here is an example. To test this, relace your UserForm_Initialize with the below code.

Option Explicit

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim arRange As Range
    Dim arData As Variant
    
    '~~> Set your worksheet here
    Set ws = ThisWorkbook.Sheets("Import")
    
    With ws
        '~~> Find last row in column AS
        lRow = .Range("AS" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify your range
        Set arRange = .Range("AS2:AS" & lRow)
        
        '~~> Get the data into an array. This will be a 2D array
        arData = arRange.Value2
    End With
    
    With Lst_Tabellen
        .Clear
        '~~> Transpose to get 0-based array
        .List = Application.Transpose(arData)
    End With
End Sub
user_11405790

as you wrote, arArray is Variant

arArray = sh.Range("AS2:AS" & lastRow2) --> arArray is a one dimensional array

...

arArray(i, 45) = Cells(i, 45).Value --> arArray becomes a 2 dimensional array

...

Auswertung.Lst_Tabellen.List = arArray --> result unknown

Pls check the above, is it what you really want?

user_144310200
Related