1

My idea was to get an alert every time I digit the word "high" in a cell of column A (also if the word is contained in a longer string). This alert should pop up just if i edit a cell and my text contains "high" and I confirm (the alert shows when I press "enter" on the cell to confirm or just leave the edited cell). So I made this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsError(Application.Match("*high*", Range("A:A"), 0)) Then
       MsgBox ("Please check 2020 assessment")
End If
End Sub

The code seemed working fine. I digit "high" in a cell of column A and get the alert when I confirm- leave the cell. The problem is that when i have a single "high" cell, the alert continues to pop up at every modification I do, in every cell. So is impossible to work on the sheet. I need a code to make sure that, after digiting "high", i get the alert just one time, and then I do not get others when editing any cell, unless i digit "high" in another cell, or i go and modify a cell that already contains "high" and I confirm it again. What could I do? Thanx!!

jonny_87
  • 21
  • 2

3 Answers3

0

This will set a target (monitored range) and check if the first cell changed contains the word

Be aware that if you wan't to check every cell changed when you modify a range (for example when you copy and paste multiple cells), you'r have to use a loop

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' Set the range that will be monitored when changed
    Dim targetRange As Range
    Set targetRange = Me.Range("A:A")
    
    ' If cell changed it's not in the monitored range then exit sub
    If Intersect(Target, targetRange) Is Nothing Then Exit Sub
    
    ' Check is cell contains text
    If Not IsError(Application.Match("*high*", targetRange, 0)) Then
        ' Alert
        MsgBox ("Please check 2020 assessment")
    End If
    
End Sub

Let me know if it works

Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
0

I tried your code; now, if column "A" has a cell "high", the alert correctly pop up and if then I edit cells in a column other than column "A", I don't get alert, so this is the good news!

The bad news is that if I have one single "high" in column A, when I edit any other cell in column "A" itself, I still get the alert everytime.

jonny_87
  • 21
  • 2
0

A Worksheet Change: Target Contains String

  • The message box will show only once whether you enter one ore multiple criteria values.

The Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const srcCol As String = "A"
    Const Criteria As String = "*high*"
    
    Dim rng As Range: Set rng = Intersect(Columns(srcCol), Target)
    If rng Is Nothing Then
        Exit Sub
    End If
    
    Application.EnableEvents = False
    
    Dim aRng As Range
    Dim cel As Range
    Dim foundCriteria As Boolean
    For Each aRng In rng.Areas
        For Each cel In aRng.Cells
            If LCase(cel.Value) Like LCase(Criteria) Then
                MsgBox ("Please check 2020 assessment")
                foundCriteria = True
                Exit For
            End If
        Next cel
        If foundCriteria Then
            Exit For
        End If
    Next aRng
    
    Application.EnableEvents = True
    
End Sub

Sub testNonContiguous()
    Range("A2,A12").Value = "high"
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thank you. The code works perfectly! One more question: if I would add another criteria (alert if cell contains the word "high", but also if it contains the word "critic")..you said this code can work also with more criteria, I tried "and, "or", "& _", after the first condition Const Criteria As String = "*high*", but nothing seems work...any hint? – jonny_87 Jan 23 '21 at 08:25
  • By *multiple criteria values* I meant if you e.g. copy/paste 50 cells containing the criteria value, there will not be 50 (annoying), but only one message box. You can ask another question with the new requirements and post a link to it on this page e.g. in your comments, so who ever is following this question gets notified. – VBasic2008 Jan 23 '21 at 08:36
  • thank you for the tip. These are my first posts on this forum after some months of vba testing at work and home. I asked another question, link is https://stackoverflow.com/questions/65857382/vba-add-multiple-criteria-if-entering-word-1-2-and-so-on-in-a-cell-then-m – jonny_87 Jan 23 '21 at 08:59