Private Sub Worksheet_Change(ByVal Target As Range) not working

[Benders]

Column A may have entries like A001, A002...A00n! For certain such entries a message box should pop up if the corresponding 'Fruits' are not found in Row 23. I tried the following code but cannot get it to work. Where am I going wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
    
    If Target.Column = 1 Then
    Cur_row = ActiveCell.Row
            If Range("A" & Cur_row).Value = "A001" Or Range("A" & Cur_row).Value = "A002" Or Range("A" & Cur_row).Value = "A003" Then
           
                 Rows("23:23").Select
                    On Error Resume Next
                    Cells.Find(What:="Apples", After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate
            If Err.Number = 91 Then
            MsgBox ("Please select Button3 to add Apples")
            End
            Else
            GoTo Letscontinue
            End If
           
    Else
      
            If Range("A" & Cur_row).Value = "A004" Then
    
                Rows("23:23").Select
                    On Error Resume Next
                    Cells.Find(What:="Bananas", After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate
            If Err.Number = 91 Then
            MsgBox ("Please select Button3  to add Bananas")
            End
            Else
            GoTo Letscontinue
            End If
    Else
    
            If Range("A" & Cur_row).Value = "A005" Then
            
                Rows("23:23").Select
                    On Error Resume Next
                    Cells.Find(What:="Grapes", After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate
            If Err.Number = 91 Then
            MsgBox ("Please select Button3  to add Grapes")
            End
            Else
            GoTo Letscontinue
            End If
            
    Else
            GoTo Letscontinue
            End If
            End If
            End If

    End If
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
Letscontinue:
    Application.EnableEvents = True
    Exit Sub

 

[Benders]

Perhaps the Change byVal code was not working since I made a change to the original code you send. Removing the option of 'OR' made it work.
What you'd send was

 If Target.Column = 1 Then
      Select Case Target.Value
         Case "A001", "A002", "A003"
            sFruit = "Apples"

And I changed it to

If Target.Column = 1 Then
      Select Case Target.Value
         Case "A001", "A002", "A003"
            sFruit = "Apples" or sFruit="Guavas"

Removing "Guavas" made it to work. Now I have to think of something to make this work.

 

[RoryA]

I don't understand what you're trying to do with that. Can any of those codes be either of those fruits?

 

[Benders]

Yes. Should the code be

If Target.Column = 1 Then
      Select Case Target.Value
         Case "A001", "A002", "A003"
            sFruit = "Apples" or "Guavas"

 

[RoryA]

No. That will require different logic and a code rewrite. Presumably a match for either fruit would make the entry valid?

 

[Benders]

Your assumption is correct.

 

[RoryA]

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim sFruit                      As String
   Dim bMatch                      As Boolean
   Dim vFruits
   Dim n                           As Long
   On Error GoTo Whoa

   If Target.Count > 1 Then Exit Sub

   If Target.Column = 1 Then
      Select Case Target.Value
         Case "A001", "A002", "A003"
            sFruit = "Apples|Guavas"
         Case "A004"
            sFruit = "Bananas"
         Case "A005"
            sFruit = "Grapes"
      End Select

      If Len(sFruit) > 0 Then
         vFruits = Split(sFruit, "|")
         For n = LBound(vFruits) To UBound(vFruits)
            If Not IsError(Application.Match(vFruits(n), Rows("23:23"), 0)) Then
               bMatch = True
               Exit For
            End If
         Next n
         If Not bMatch Then MsgBox "Please select Button3 to add " & Join(vFruits, " or ")
      End If

   End If

   Exit Sub
Whoa:
   MsgBox Err.Description
End Sub

 

[Benders]

I tried this. However this is not working as intended. Upon Entering A001 to A005, it pops up the message even if the Corresponding Fruit exists on Row 23. The idea is that if the Fruit already exists on Row 23 the Message pop-up is not required. for Entries A001 to A003, if either of the fruits exists then the prompt should be for the missing fruit. And if both the fruits exist then there should be no pop-up. Sorry to keep frustrating you and if I am not being able to explain this any better.

 

[RoryA]

I asked "Presumably a match for either fruit would make the entry valid?" and you said that was correct. Apparently it is not - there must be both fruits present for the entry to be valid?

 

[Benders]

Sorry about that. I misunderstood. Just one of those days in the office.

 

[RoryA]

It happens.

What if neither fruit is present - two messages, or one? Will there ever be more than two?