mercredi 27 mai 2015

Excel VBA: User defined function based on checkbox input not recalculating

In Excel 2013, I have defined a custom function CUSTOM_EQUITY which returns "EXTERNAL" or "INTERNAL" based on whether a checkbox (form control) is checked or not. It works okay when I press enter, but it doesn't auto-update when I click the checkbox, checking or unchecking it.

Forcing the update with Ctrl+Alt+F9 works perfectly, but I'm unable to make the cell automatically update when the checkbox is clicked. As you see I have already tried with Application.Volatile.

Function CUSTOM_EQUITY()

    Application.Volatile
    Application.Calculation = xlCalculationAutomatic

    Dim taxesExt As Boolean
    taxesExt = ActiveSheet.Shapes("TAXES_EXT").ControlFormat.Value = 1

    If taxesExt Then
        CUSTOM_EQUITY = "EXTERNAL"
    Else
        CUSTOM_EQUITY = "INTERNAL"
    End If

End Function

How can I make my function automatically recalculate when the checkbox value changes?




Aucun commentaire:

Enregistrer un commentaire