22 Mar 2020

## Worksheet_Change Keep All Time Min Max Numbers

Excel is not meant to store historical data. That’s what databases do. However, Tom Urtis shows us a trick to keep the all time min value from a single input cell.

(Post concept from Tom Urtis. Visit Tom’s blog)

### What Does It Do? In rare cases this could be extremely helpful. Instead of using vba to store all values and then use MIN and MAX functions you could use Tom’s vba solution.

### The Code

I modified Tom’s code a bit to also include an all time maximum (along with all time minimum).

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> “\$A\$2” Then Exit Sub
If Len(.Value) = 0 Then
Exit Sub
End If
If .Value < Range(“B2”).Value Then Range(“B2”).Value = .Value
If .Value > Range(“C2”).Value Then Range(“C2”).Value = .Value
End With
End Sub
Worksheet_Change is a special kind of vba. It monitors a cell or range and performs an action when a condition(s) is met.
The code above performs various checks. If the activecell isn’t cell A2 then stop (exit sub).  If the length of cell A2 is zero then stop.  If the code is still going it means that the current cell is A2 and the length of cell A2 is not zero.
If .Value < Range(“B2”).Value Then Range(“B2”).Value = .Value
If cell A2 value is less than cell B2 value then take A2 value and put it in cell B2. This keeps the all time minimum value. The all time max works the same.

### More Fun

Years ago I was had a discussion with a work colleague. He said “Oh, Excel can’t do that!”. I said “Oh yes it can”. This is what I built to prove him wrong 🙂

In the light brown input area enter words (or numbers). Those words will be stored in column F. If you type over a word the original word is still stored in column F (in the same order) and the new word is added further down.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextrow As Integer, Savetxt As Range
Set Savetext = Range(“A1:D5”)
nextrow = Application.WorksheetFunction.CountA(Savetext)
With Target
If Target.Column > 4 Then Exit Sub
If Target.Row > 4 Then Exit Sub
On Error GoTo Handler
If Len(.Value) = 0 Then
Exit Sub
End If
Range(“F” & nextrow + 1).Value = .Value
End With
Handler:
End Sub
I got a bit lazy with the code and used On Error GoTo Handler to hide my incompetence (you’ll see).
Well…thanks again to Tom Urtis. Unfortunately I couldn’t find my original code for the multi cell input area but I was able to recreate it to do pretty much the same thing. I think now I’ll get some more coffee and study Power BI and DAX.