从行数组填充ListBox(Populating a ListBox from row array)

从行数组填充ListBox(Populating a ListBox from row array)

任何人都可以解释我的代码有什么问题吗？ 我试图在行数组上填充ListBox。 我得到运行时错误438.对象不支持此属性或方法，但不清楚我编码错误。

Private Sub ListBox1_Click() Dim LastRow As Long With Sheets("TempList") LastRow = .Range("A" & Rows.Count).End(xlUp).Row End With Sheets("tblSurveyMatches").ListBox1.RowSource = Sheets("TempList").Range("A2" & LastRow) End Sub

Can anyone explain what's wrong with my code? I am trying to populate a ListBox on an array of rows. I get the run-time error 438. Object doesn't support this property or method, but it is unclear what I have coded wrong.

Private Sub ListBox1_Click() Dim LastRow As Long With Sheets("TempList") LastRow = .Range("A" & Rows.Count).End(xlUp).Row End With Sheets("tblSurveyMatches").ListBox1.RowSource = Sheets("TempList").Range("A2" & LastRow) End Sub

最满意答案

如果这是工作表上的ActiveX控件，这将起作用，您需要使用ListFillRange而不是RowSource 。

Sheets("tblSurveyMatches").ListBox1.ListFillRange= Sheets("TempList").Name & "!" & Range("A2:A" & LastRow).Address

请注意，通常最好将Range限定为工作表，但在这种情况下，因为我们只需要一个地址字符串，所以在这里并不重要。

Provided this is an ActiveX control on your sheet, this will work, you need to use ListFillRange instead of RowSource.

Sheets("tblSurveyMatches").ListBox1.ListFillRange= Sheets("TempList").Name & "!" & Range("A2:A" & LastRow).Address

Note that it is normally best practice to qualify the Range to a worksheet, but in this case since all we need is an address string, it's not as important here.