Why does thjs code trigger another

[Giordano Bruno]

I have a userform with a textbox and a listbox. In the form's code module I have the following code:

Private Sub tbxAB_AfterUpdate()
shtCorrel3.Range("ValidMax").Calculate
End Sub

This code immediately triggers the procedure, "Private Sub lbxSource_Change()".
The calculation of the single cell {ValidMax) is unrelated to the Listbox source. Can anyone explain why it starts the second code and just as importantly how to stop it doing so?

 

[Ruddles]

I don't know the answer to your first question but as a first step you might try stopping it by coding:-

Private Sub tbxAB_AfterUpdate()
Application.EnableEvents=False
shtCorrel3.Range("ValidMax").Calculate
Application.EnableEvents=True
End Sub

Just a suggestion – I haven't tested this.

 

[Giordano Bruno]

Thanks for the suggestion Ruddles but no, it has no effect whatever. I appreciate your interest, however.

 

[RoryA]

Is the listbox bound directly to a range on a worksheet using its Rowsource property?

 

[Giordano Bruno]

Hi Rory. Yes it is, but the cell being calculated does not affect the Rowsource cells. The cell (ValidMax) has no dependents. Are you suggesting that the code will trigger the ListBox whenever there is a calculation anywhere. If so, how can I stop it doing that? As a workaround I've made a Boolean Global variable which stops the ListBox code at the start. This prevents the unwanted effects that would occur if the whole code were allowed to run, but It's a pretty cluncky solution.

 

[RoryA]

What is the actual rowsource for the control?

 

[Giordano Bruno]

The RowSource is a named range "ListDs" which is defined as =OFFSET('BasicRiskModelv2-01d-6TT.xlsb'!D_LIst,1,0,'BasicRiskModelv2-01d-6TT.xlsb'!CtDs,1)
CtDs is the formula "=SUMPRODUCT(--(LEN(AF2:AF11)>0))". The cell I calculate, which causes all the trouble, is in N15 and does not appear to have anything to do with the named range. Does this help?

 

[RoryA]

That’s the issue. OFFSET is volatile so it recalculates whenever any open workbook does.

 

[Giordano Bruno]

Thanks Rory. That's really clever. I guess that means I have to go back to my clunky solution - Ugh! Nonetheless, I'm very grateful for your explanation.

 

[RoryA]

Since you're only loading one column, you could simply not use Rowsource at all. Just populate the listbox using its List property and update that whenever necessary.