运行代码将用户窗体中的列表框选择填充到Excel单元格中时如何解决错误91？-解网

问：

我有一个包含列表框的窗体，我正在尝试编写代码以将列表框中的选定项目（多项选择）填充到 excel 工作表中。我收到错误 91（对象变量或未设置块变量）。下面是出现错误的代码部分。具体来说，它指的是：对于 x = 0 到 lstCategory.ListCount - 1

Sub Submit()
    Dim sh As Worksheet
    Dim iRow As Long
    Dim Itemsselected As String
    Dim x As Long
    Dim lstCategory As ListBox
   iRow = [Counta(Database!B:B)] + 1

    Set lstCategory = InvestorForm.lstCategory

    With lstCategory
        For x = 0 To lstCategory.ListCount - 1
            If lstCategory.Selected(x) = True Then
            Itemsselected = Itemsselected & lstCategory.List(x) & ", "
            End If
        Next x
    End With

我尝试在“list”和“count”之间添加一个“.”，但它没有解决错误。我只是试图让用户从列表框中选择的数据出现在工作表单元格中。

Excel VBA 处理器错误

Sub Submit
    Dim sh As Worksheet
    Dim iRow As Long
    Dim Itemsselected As String
    Dim x As Long
'    Dim lstCategory As Listbox         <<-- Not Required
   iRow = [Counta(Database!B:B)] + 1

'    Set lstCategory = InvestorForm.lstCategory         <<-- Not Required

'   Change the With..End block to reference the listbox directly.
    With InvestorForm.lstCategory
        For x = 0 To .ListCount - 1     '<<-- Changed
            If .Selected(x) = True Then      '<<-- Changed
            Itemsselected = Itemsselected & .List(x) & ", "     '<<-- Changed
            End If
        Next x
    End With

你真的应该顺其自然。它的效率要高得多。每次引用对象时，都需要先解析该对象，然后才能对它执行任何操作。在这种情况下，循环的每次迭代都需要并解析两次。使用意味着并且只需要解决一次。如果列表框中有很多项目，这可能很重要。它还使代码更具可读性。With ... End WithFor ... NextInvestorFormlstCategoryWith ... End WithInvestorFormlstCategory

上一个：表和结果 - 如何编写 If 语句 [duplicate]

下一个：VBA - 初始运行期间 #VALUE 错误，乘法器到动态范围

运行代码将用户窗体中的列表框选择填充到Excel单元格中时如何解决错误91？

How to resolve error 91 when running code to populate list box selection in userform into excel cell?

评论

评论