Monitoring a range for changes

A co-worker of mine has a workbook with an inventory list in column D. He wants a way to highlight low inventory quantities, when the inventory for any particular item falls below a certain amount. At first I thought about conditional formatting, for example:

But he'd rather have a popup messagebox. I guess the more intrusive, the more likely it won't be ignored. So I came up with this code. If any cell in column D is changed, and the amount is below 50, the messagebox is shown.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Excel.Range

On Error Resume Next
Set rng = Intersect(Range("D:D"), Range(Target.Address))
On Error GoTo 0

If Not rng Is Nothing Then
  If (Target.Value < 50) And (Target.Value <> "") Then
    MsgBox "Below 50, please reorder now.", vbInformation
  End If
End If

End Sub

Enjoy,
JP

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 5 Comment(s) on Monitoring a range for changes:

  1. Stan Scott writes:

    Excellent tip. The only thing I might add is code for the situation where the user adds a value into more than one cell using CTRL-ENTER. A FOR-EACH loop on the cell(s) in the Target range will cover this.

  2. Stan,

    Good point. My code assumes that only one cell is selected, but theoretically my coworker could change multiple cells, and the code would throw an error. Here's a reworked version that takes this into account.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Excel.Range
    Dim cell As Excel.Range
    On Error Resume Next
    Set rng = Intersect(Range("D:D"), Range(Target.Address))
    On Error GoTo 0
    
    If Not rng Is Nothing Then
      For Each cell In Range(Target.Address)
        If (cell.Value < 50) And (cell.Value <> "") Then
          MsgBox "Below 50, please reorder now.", vbInformation
          Exit For
        End If
      Next cell
    End If
    
    End Sub
    

    Thx,
    JP

  3. hmm.. did you consider using data validations. You can show popup messages in them.

    checkout #8 on this http://chandoo.org/wp/2008/08/26/date-time-tips-ms-excel/

  4. That's a good one, you could set the warning style to "Information" and it wouldn't stop you from entering the number. But the purpose of this notification is not to confirm that the correct number is entered. The end user might get confused if they're prompted to re-enter the value.

    Thx,
    JP

  5. Hmm… as far as I remember, the messages are shown only when you dont enter correct value (based on the validation criteria you define in first tab, which could be <=50.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel