Need help with setting up specific cells value monitoring once a certain condition is met

**Brad J.** · 06-14-2012, 11:06 AM

Hi all!

I am currently working with a spreadsheet that is holding information regarding issues with our products. I currently have it set with a simple count formula to count the number of times a particular product number appears in the worksheet. What we have decided to do recently due to the fact that several product numbers appear many more times than we would like, is to setup an alert system to notify anyone that one of these particular product numbers are being entered. I plan on doing this by having a script run once the event occurs perhaps from a user dialog with a command button attached that will send an email to the necessary person(s).

So, to make it a bit simpler, I want to do this:

Count all entries
Once an entry appears 5 times, I want to begin monitoring that product number
The next instance of this product number being entered, I want to prompt the person creating the entry to send an email by clicking a command button.

The issue I am running across is this, I am struggling like crazy to tie the selective monitoring together once the condition is met. I understand how to setup a count, I understand how to monitor cells for change (I thought I could use this to trigger the user prompt), and I understand how to send an email from my command button. I guess what I'm really having trouble with is finding out how to selectively monitor cells for value changes once they have appeared certain number of times. Currently, I have the number of instances appear in a column at the end of the row, and I even went a step further and tried using an IF formula in the next column to present a word for any of those which met my condition. I just don't know how to use that in a script to selectively monitor those product numbers for new entries. I could also be using more steps than I actually need to, but, I'm no expert by any means with VBA.

If anyone has any ideas how I might be able to bridge this gap, or even do it in a more efficient way, I'm more than willing to humbly bow to your advice.

Thanks in advance,
Brad

**tigeravatar** · 06-14-2012, 11:24 AM

Brad,

I'm not entirely sure if this is correct, but you can give it a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngMonitor As Range
    Set rngMonitor = Range("A1:A10")
    
    If Not Intersect(Target, rngMonitor) Is Nothing And Target.Cells.Count = 1 Then
        If WorksheetFunction.CountIf(rngMonitor, Target.Value) > 5 Then
            'code to prompt to send email here
        End If
    End If
    
End Sub

Need help with setting up specific cells value monitoring once a certain condition is met

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need help with setting up specific cells value monitoring once a certain condition is met

Re: Need help with setting up specific cells value monitoring once a certain condition is

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions