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

Question

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?

Answer 1

I'm not sure that you are talking about a CheckBox in a UserForm, but if you are just place this line in the CheckBox_Change() :

Sheets("Your_Sheet_Name").Calculate

And if it is an Excel CheckBox :

1. Add a linked cell (here is in C1)
2. Place this in the Worksheet module

This code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    If Application.Intersect(Target, Range("C1")) Is Nothing Then
      'Nothing
    Else
       Sheets("Your_Sheet_Name").Calculate
    End If    
End Sub