Dropdown list circular reference Excel VBA

0 votes

I am following up on a answer that has been posted before at the following link: Circular Reference with drop-down list

When the dropdown lists and sources are in the same cell on their respective sheets, the solution works. However, I'm attempting to figure out how it operates when they are not. Many thanks

I am following this answer:

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

I'm attempting to have two lists so that if I edit one, it will automatically update the other. How can I achieve the same outcome, for instance, if the dropdown menus are in cells A3 on Sheet1 and D9 on Sheet2?

Here is what I'm looking for: I want to create a drop-down list that says "Complete" or "Incomplete" on two sheets (sheet 1 and sheet 2). I want page 2 to state the same thing as sheet 1 if I alter it from Complete to Incomplete, but I also want the opposite to be true (If I change sheet 2 from Complete to Incomplete, I want sheet 1 to change).

Feb 18, 2023 in Others by Kithuzzz
• 38,000 points
494 views

1 answer to this question.

0 votes

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim arrCells, el, i As Long, m, tgt, arr
    
    arrCells = Array("Sheet1|D3", "Sheet2|B4") 'all cells with the list
    
    tgt = Sh.Name & "|" & Target.Address(False, False)
    m = Application.Match(tgt, arrCells, 0) 'matches one of the list cells?
    
    If Not IsError(m) Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        For i = LBound(arrCells) To UBound(arrCells)
            If arrCells(i) <> tgt Then 'skip the cell raising the event...
                arr = Split(arrCells(i), "|")
                ThisWorkbook.Sheets(arr(0)).Range(arr(1)).Value = Target.Value
            End If
        Next i
        Application.EnableEvents = False
    End If
    
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub
answered Feb 18, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
959 views
0 votes
0 answers

Simple way to remove blank cells dynamic dropdown list Excel

Every time I create a dependent dynamic ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,000 points
1,139 views
0 votes
1 answer

How to fix a circular reference error by if condition in excel file?

Circular reference in this context refers to ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,600 points
741 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
988 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
2,062 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
1,781 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,600 points
1,293 views
0 votes
1 answer

How to create Dropdown list in excel using php

Try this: $objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation(); $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST ); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
2,455 views
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,600 points
1,156 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP