excel - 使用列表框调用用户窗体时出现运行时错误

Question

希望你能在这里帮助我，因为我真的看不到错误。

我正在尝试使用列表框创建用户窗体，用户可以在其中从数组中选择值。数组的代码如下：

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

这完美地工作。现在我正在初始化用户窗体：

Public Sub UserForm_Initialize()



Auswertung.Lst_Tabellen.List.Clear


Auswertung.Lst_Tabellen.List = arArray


End Sub

但每次我尝试调用用户窗体“Auswertung”时，都会收到错误“运行时错误 424：需要对象”。大家能看出问题吗？

Public Sub Call_Userform()

Auswertung.Show


End Sub

当我运行代码时，调试器会标记“Auswertung.Show”行。

标签： excelvbalistboxuserform

Answer 1

正如我所提到的，您需要在里面填充数组UserForm_Initialize()

这是一个例子。要对此进行测试，请UserForm_Initialize使用以下代码替换您的代码。

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