CODEWITHSUNDEEP
excelvba
Shevy
Shevy

Reputation: 57

Circular reference even though the code should not create it like that

I have a combobox and text box in one excel sheet that I need should be dependent on one another. In order to avoid circular references I added code that would change the values of the cells they are linked to based on were the change is taking place.

Private Sub TextBox1_Change()

    With Range("P5")
        .NumberFormat = "0"
        .Value = .Value
    End With
    Range("Q5").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R3C2:R43C3,2,FALSE)"

End Sub


Private Sub ComboBox1_Change()

    Range("Q5").Value = ComboBox1.Text
    Range("P5").FormulaR1C1 = "=LEFT(RC[1],3)"
    TextBox1.Value = Range("P5")

End Sub

The text box is linked to cell P5 and the Combobox is linked to cell Q5. Why am I still getting circular reference?

Upvotes: 0

Views: 72

Answers (1)

Vitaliy Prushak
Vitaliy Prushak

Reputation: 1162

When you change combobox - you set a formula in P5, which uses value from Q5 ("=LEFT(RC[1],3)"). When you change a textbox - it set a formula to Q5 to get value from P5. But P5 at this time is dependent on Q5's value, here's your circular reference.
Consider clearing values on control's change to avoid this.

Upvotes: 1

Related Questions