带有下拉列表的循环引用

Is it possible in MS. Excel or VBA to have a circular reference with a drop-down list?

是否有可能在MS。 Excel或VBA是否具有带有下拉列表的循环引用？

Here is what I am after: I want to generate on two sheets (sheet 1, sheet 2) a drop down list that says either "Complete" or "Incomplete." If I change sheet 1 from Complete to Incomplete, I want sheet 2 to say the same thing, but I also want vice versa
(If I change sheet 2 from Complete to Incomplete, I want sheet 1 to change).

这就是我所追求的：我想在两张纸（表1，表2）上生成一个下拉列表，其中显示“完成”或“未完成”。如果我将表单1从完全更改为不完整，我希望表单2说同样的事情，但我也想反过来（如果我将表格2从完全更改为不完整，我希望表单1更改）。

Is this possible?

这可能吗？

3 个解决方案

#1

Acting on a change in any of the worksheets' B5 range seems a likely way to proceed but the individual Worksheet_Change event macros have some limitations.

对任何工作表的B5范围进行更改似乎是一种可行的方法，但单个Worksheet_Change事件宏有一些限制。

The code has to be repeated across many worksheet code sheets and any modifications have to be cloned across the same. New worksheets require the sub procedure to be incorporated into their own code sheets.

代码必须在许多工作表代码表中重复，并且必须克隆任何修改。新工作表要求将子过程合并到他们自己的代码表中。

Without disabling events before writing new values, each worksheet receiving a new value is going to initiate its own Worksheet_Change event macro which in turn will rewrite values which will trigger more events. A cascade event failure is almost sure to happen.

如果在编写新值之前没有禁用事件，则每个接收新值的工作表将启动自己的Worksheet_Change事件宏，该宏又会重写将触发更多事件的值。级联事件失败几乎肯定会发生。

By exchanging the Worksheet_Change event macro for the more universal Workbook_SheetChange event macro located in the ThisWorkbook code sheet, all of the code can be localized to a single location. Adjustments are made in a single place and new worksheet will automatically be added to the queue of worksheets to process. They can easily be added to the array of worksheet not to process as well.

通过将Workheet_Change事件宏替换为ThisWorkbook代码表中更通用的Workbook_SheetChange事件宏，可以将所有代码本地化到一个位置。调整在一个地方进行，新工作表将自动添加到要处理的工作表队列中。它们很容易添加到工作表数组中，也不能处理。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$B$5" And Sh.Name <> "Sheet3" Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Dim w As Long
        For w = 1 To Worksheets.Count
            With Worksheets(w)
                'skip this worksheet and Sheet3
                If CBool(UBound(Filter(Array(Sh.Name, "Sheet3"), _
                        .Name, False, vbTextCompare))) Then
                    .Range("B5") = Target.Value
                    '.Range("B5").Interior.ColorIndex = 3  '<~~testing purposes
                End If
            End With
        Next w
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Any worksheet that is not to receive an update to the value in its own B5 cell can be added to the array used in the Filter function. Currently, Sheet3 and the worksheet that initiated the Workbook_SheetChange event are excluded.

任何不接收其自身B5单元格中的值更新的工作表都可以添加到Filter函数中使用的数组中。目前，Sheet3和启动Workbook_SheetChange事件的工作表被排除在外。

#2

I would create a hidden sheet that contains the range for input and the linked cell. Then link both drop downs to the list and the linked cell. Then when you change one it will change the other. The key here is the Linked Cell. This is assuming Excel 2013, using the Form control Combo Box.

我会创建一个隐藏的工作表，其中包含输入和链接单元格的范围。然后将下拉列表链接到列表和链接的单元格。然后，当你改变一个，它将改变另一个。这里的关键是Linked Cell。这是假设Excel 2013，使用表单控件组合框。

#3

Please have a look at @Jeeped's answer as it is the most efficient answer.

请看看@ Jeeped的答案，因为它是最有效的答案。

---

After a little trial and error I've gotten this to work with a cell with a "Data Validation" dropdown menu. In my test case, I had 3 sheets with a Data Validation list in cell $B$5 on each worksheet linked to a list on a hidden sheet to populate the list, the sheet with the options list was "Sheet3" and did not contain a data validation list.

The Code below needs to be copied to every sheet module.

下面的代码需要复制到每个工作表模块。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    If Target.Address = "$B$5" Then
        For Each ws In ThisWorkbook.Worksheets
            If Not ws.Name = Me.Name And Not ws.Name = "Sheet3" Then
                If Not ws.Range(Target.Address) = Me.Range(Target.Address) Then
                    ws.Range(Target.Address) = Me.Range(Target.Address)
                End If
            End If
        Next ws
    End If
End Sub

---

This is fairly easy with activeX comboboxes on the sheets

使用工作表上的activeX组合框很容易

On the workbook module add the code below to populate the comboboxes

在工作簿模块上添加以下代码以填充组合框

Private Sub Workbook_Open()
    With ThisWorkbook
        With .Worksheets("Sheet1").ComboBox1
            .AddItem "Complete"
            .AddItem "Incomplete"
        End With
        With .Worksheets("Sheet2").ComboBox1
            .AddItem "Complete"
            .AddItem "Incomplete"
        End With
    End With
End Sub

On the "Sheet1" Module add

在“Sheet1”模块上添加

Private Sub ComboBox1_Change()
    If Me.ComboBox1 = "Complete" Then
        ThisWorkbook.Worksheets("Sheet2").ComboBox1.Value = "Complete"
    ElseIf Me.ComboBox1 = "Incomplete" Then
        ThisWorkbook.Worksheets("Sheet2").ComboBox1.Value = "Incomplete"
    End If
End Sub

On the "Sheet2" Module add

在“Sheet2”模块上添加

Private Sub ComboBox1_Change()
    If Me.ComboBox1 = "Complete" Then
        ThisWorkbook.Worksheets("Sheet1").ComboBox1.Value = "Complete"
    ElseIf Me.ComboBox1 = "Incomplete" Then
        ThisWorkbook.Worksheets("Sheet1").ComboBox1.Value = "Incomplete"
    End If
End Sub