VBA 运行时错误 -214724809 (80070057)

VBA Run-time error -214724809 (80070057)

提问by methuselah

I want fill a drop down list based on user selection in another. I'm trying to update the contents of a field based on selection in another by adding the items on the fly once the first combobox (cbo_park) is selected.

我想根据另一个用户选择填充一个下拉列表。我正在尝试通过在选择第一个组合框 (cbo_park) 后即时添加项目来根据另一个字段的选择更新字段的内容。

I have a four drop down lists:

我有四个下拉列表：

The first drop down cbo_parkhas the following options:

第一个下拉cbo_park有以下选项：

Central
East
West

I have a second workbook called lookupRoomwhich contains the following table:

我有一个名为lookupRoom的第二个工作簿，其中包含下表：

roomCode    park
A.0.01      Central 
A.2.01      Central 
A.3.01      Central 
HE.0.10     East
HE.0.21     East
HE.0.22     East
KG.1.07     West
KG.1.09     West
KG.1.10     West

When the user selects the Central park option under the first drop down cbo_parkI only want rooms in the Central park to be displayed in dropdown cbo_prefRoom1, cbo_prefRoom2and cbo_prefRoom3. How would I go about achieving this?

当用户在第一个下拉菜单cbo_park下选择中央公园选项时，我只希望中央公园中的房间显示在下拉菜单cbo_prefRoom1、cbo_prefRoom2和cbo_prefRoom3 中。我将如何实现这一目标？

Please find below my attempts so far. I keep receiving an error at the line: Me.cbo_prefRoom1.RemoveItem 0.

请在下面找到我迄今为止的尝试。我一直收到以下行的错误：Me.cbo_prefRoom1.RemoveItem 0。

Private Sub cbo_park_Change()

Dim lLoop As Long, rgLoop As Range

    For lLoop = 1 To Me.cbo_park.ListCount

        Me.cbo_prefRoom1.RemoveItem 0

    Next lLoop

    Sheets("lookupRoom").[a1].CurrentRegion.AutoFilter
    Sheets("lookupRoom").[a1].CurrentRegion.AutoFilter Field:=3, Criteria1:=Left(Me.cbo_park.Value, 2)

    For Each rgLoop In Sheets("lookupRoom").[a1].CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Columns(1).Cells
        If Len(rgLoop) > 0 Then
            Me.cbo_prefRoom1.AddItem rgLoop
        End If
    Next rgLoop

End Sub

回答by danielpiestrak

Below is my solution for achieving this.

以下是我实现这一目标的解决方案。

I rewrote everything to be contained in just the one Forloop and set it to update both comboboxes.

我重写了所有包含在一个For循环中的内容，并将其设置为更新两个组合框。

Private Sub cbo_park_Change()

    Dim lLoop As Long
    '- clear the two comboboxes we are about to update
    Me.cbo_prefRoom1.Clear
    Me.cbo_prefRoom3.Clear

    '- loop through the worksheet and test each row
    For lLoop = 1 To Sheets("lookupRoom").Range("A" & Sheets("lookupRoom").Rows.Count).End(xlUp).Row
        '- if the row's column C matches the combobox then add the corresponding values to other combos
        If Sheets("lookupRoo"m).Range("C" & lLoop).Value = Me.cbo_park.Value Then
            Me.cbo_prefRoom1.AddItem Sheets("lookupRoom").Range("B" & lLoop).Value
            Me.cbo_prefRoom2.AddItem Sheets("lookupRoom").Range("B" & lLoop).Value
        End If
    Next lLoop

End Sub

回答by Robert Co

Here's how to implement it without VBA and without using the combo box. Excel cells has Data Validation which would act like a combo box. Since it part of the spreadsheet you don't have to worry about sizing and positioning.

这是在不使用 VBA 和不使用组合框的情况下实现它的方法。Excel 单元格具有数据验证，其作用类似于组合框。由于它是电子表格的一部分，因此您不必担心大小和位置。

With the label "Park" in A2, use B2as your input cell. With B2 as your active cell, go to Data -> Validation; choose Listfor Allow and just type Central,East,Westin the Source. Try it out and see if you like your new drop down.

使用 中的标签“Park” A2，B2用作输入单元格。将 B2 作为活动单元格，转到数据 -> 验证；选择List允许并输入Central,East,West源。试试看，看看你是否喜欢你的新下拉菜单。

Now for the Rooms "trickery".

现在是房间“诡计”。

1. Type ="LookupRoom!A"&MATCH(B2,lookupRoom!B1:B10,0)&":A"&MATCH(B2,lookupRoom!B1:B10,1)into C2.
2. Go to B3and us data validation again, but this time type =INDIRECT($C$2)into the source input.

1. 键入="LookupRoom!A"&MATCH(B2,lookupRoom!B1:B10,0)&":A"&MATCH(B2,lookupRoom!B1:B10,1)成C2。
2. B3再次转到我们的数据验证，但这次=INDIRECT($C$2)输入源输入。

Try it. Now you have a drop down that responds to your park selection.

尝试一下。现在您有一个下拉菜单来响应您的公园选择。

回答by chris neilsen

Its not clear what you are trying to achieve. If it's to clear all entries from a combo box, use this

目前尚不清楚您要实现的目标。如果要清除组合框中的所有条目，请使用此

Do While Me.combo.ListCount > 0
    Me.combo.RemoveItem(0)
Loop