Circular Reference with drop-down list

Question

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

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).

Is this possible?

if you are using dropdown form control, just assign same linked cell for both the dropdowns — Arya
I'm trying to do the same thing. Here is my question on SU:superuser.com/questions/1031660/linked-dropdown-lists — THE JOATMON
"circular references" is not exactly translatable from Excel formulas to VBA. In VBA, you can [for example] make x = y, then make y = x + 1, then make x = y + x. This works because each line is done in order, so it doesn't run back and forth recomputing. You can't do this with Excel formulas because all cells update at the same time; there is no 'order of operations' between cells. So you cant make A1 = B1, and then make B1 = A1 + 1, because it would keep looping forever. So what this question is basically asking is, is: can a sub impact a cell differently depending on that cell's value? Yes. — Grade 'Eh' Bacon

Unknown Unknown · Accepted Answer · 2016-01-27T02:54:26

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.

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.

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.

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.

Circular Reference with drop-down list

3 Answers