detecting new values in a range of cells

[Luis939]

is there anyway excel can detect when a certain value or string will be added in a range of cells, for example if i have a range and i insert the word "total" in any cell in that range, is there anyway excel can detect that and store its cell ID so that i can take that ID and store it in a formula in another cell?

 

[SteveBausch]

There are Validation features built into Excel, perhaps they would work for you. If you think you need to Roll Your Own, here is an example.

Worksheet_Change (ByVal Target as Excel.Range

is where you would start. Accessible from Project Explorer. Right-click the desired worksheet and View Code.

You would qualify Target as the proper location, then process as desired.

Here is a code sample that demonstrates the concept. Yes, I don't use .Intersect to test the Range..... Shoot me.

Private Sub Worksheet_Change(ByVal Target As Range)
'Occurs when cells on the worksheet are changed by the user.
'Deleting cells doesn't trigger this event.
Dim CellToCheck As Range
 If Target.Column > RightOfForm Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo Oops



If Target.Row < BottomOfHeader Then 
    CheckHeaderCells Target
    Application.EnableEvents = True
    Exit Sub
Else
        For Each CellToCheck In Target
            
            'TRIM() THE ENTRY,
            'CellToCheck.Formula = Trim(CellToCheck.Formula)
            If (VarType(CellToCheck) <> vbEmpty) And (Len(CellToCheck) = 0) Then
                CellToCheck.ClearContents
            End If
            
             
            If CellToCheck <> vbNullString Then
                CheckCellsBelowHeader CellToCheck
            End If
            Application.EnableEvents = True
        Next
End If
Application.EnableEvents = True
Application.ScreenUpdating = True

Exit Sub
Oops:
YeOldeErrorHandler Target
Resume
End Sub

Before you even consider implementing this technique of validation, do what you can with locking cells, protecting the sheet, setting .ScrollArea and .EnableSelection to obligate your users to navigate the sheet as you want them to.

Note: I excised out a lot of detail that wasn't so valuable. The code may be redundant or flawed.