How to display SUM of visible cells in a userform label?

Question

I would like to display the SUM of visible cells from column D in a Userform Label, and have it update automatically as the spreadsheet gets filtered.

I have this code

Private Sub SUM_click()
    Me.SUM.caption = range ("AA2")
End Sub

This is problematic.

• 1. I use a SUM formula in cell AA2.
     I’d like to avoid using formulas in any cells, if possible.
• 2. The return value isn’t excluding rows which are hidden.
• 3. It displays the value after I click on the label.
     I want it to display automatically.

Answer 1

Automate display of SubTotals in UserForm

This reveals being not as trivial as it may seem.

Of course you might profit from a sheet's Worksheet_Calculate event to get subtotals (of visible cells) by means of VBA whenever the (enabled) calculation processes a new result. This is effective when filtering and/or when changing values, whereas the Worksheet_Change event wouldn't cover filtering.

Private Sub Worksheet_Calculate()
Dim subtotal As Double
    subtotal = Sheet1.Evaluate("=Subtotal(109,D:D)")     ' argument 109 sums up only visible cells!
    Debug.Print "Subtotal: " & Format(subtotal, "0.00")  ' display in VB Editor's immediate window
End Sub

... but there's no synchronized event to refresh a label in the Userform's code.

---

So I suggest the following tricky workaround profiting from the ListBox property RowSource:

• add the subtotal formula =SUBTOTAL(109,D:D) to cell AA2
• instead of a label ... let a listbox do the display binding it to a .RowSource; whenever a subtotal gets changed this will be reflected in the only list value of the bound listbox.

Defining the appearance via .SpecialEffect = fmSpecialEffectFlat makes the Listbox appear nearly like a label. Small beauty mistake: the background color can't be modified

UserForm code example

Private Sub UserForm_Initialize()
With Me.ListBox1                ' << rename control to any other name
    .ColumnCount = 1            ' define columncount
    .ColumnWidths = .Width - 8  ' play around with small sizes to avoid scroll bars
    .Height = 12                
    .RowSource = "Sheet1!AA2"       ' reference to cell containing subtotal formula
    .SpecialEffect = fmSpecialEffectFlat
    .ListIndex = 0          ' activate first (and only) record
End With

Tip

To enable concurrent actions like editing or filtering in the table, you need to show the UserForm modeless, e.g. via

    With New UserForm1
        .Show vbModeless
    End With

Alternative suggested in comment

Thx to @norie's hint, it would also be possible to profit from a textbox'es .ControlSource property (having more ressemblance to a label) without the need of further cosmetics. Nevertheless this promising approach needs further (or even more complicated?) steps: a textbox allows inputs which would overwrite the initial formula in the referred cell which isn't wanted.

Possible ways out I won't develop here: prevent key-event entries (have a look into other SO posts) or use the TextBox1_Change event to rewrite the formula each time the textbox got changed (doubtful), place a blocking control over the textbox etc.