Worksheet Change VBA | My Spreadsheet Lab

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.

(download my Excel file)

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 had a discussion with a work colleague. He said “Oh, Excel can’t do that!”. I said “Oh yes it can”. I built this to prove him wrong 🙂

In range A1:D5 enter words. 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.

About Tom Urtis

He’s an Excel consultant, Excel MVP, and Excel legend! Tom is also a big sports fan (baseball and football). Visit his blog: https://www.atlaspm.com/

About Me

My name is Kevin Lehrbass. I’m a Data Analyst from Markham Ontario Canada.

In these difficult times of Covid-19 I’m keeping my mind occupied here at home by playing around in Excel and Power BI. Wait…I always do that! I’ve also calculated the optimal balance of news and comedy. Oh yeah…these are my dogs Fenton and Cali. Starting yesterday the neighbourhood kids aren’t running around outside so they have nothing to bark at.

The site is about Microsoft Excel.

Worksheet_Change Keep All Time Min Max Numbers

What Does It Do?

The Code

More Fun

1 Comment → Worksheet_Change Keep All Time Min Max Numbers

Leave a Reply Cancel reply