Monitoring a range for changes
October 28, 2008 • JP • No Comments • Rate This Article
• Links to this article
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.
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
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
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 if you want to learn more about VBA. Keep Reading »
Previous Post: HTML Link Building Using Excel
Next Post: Check your generated emails for valid recipients
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.
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.
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
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/
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
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.