No pop up message if the input exceeds the cell width

[logstarter]

I have written the following VBA. It works well when the input is within cell width. However, when the input is too long and exceeds the cell width (which some part of input will be hidden), the function seems not functioning properly.

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim ChangedRow As Integer
    With Sh
        If Not Intersect(Target, Sh.Range("E:E")) Is Nothing Then
            
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            ChangedRow = Target.Row
         
            If WorksheetFunction.CountA(Target) > 0 Then
                MsgBox "run 2"
                If checkDuplicate(Target) = False Then
                    confirmChange = MsgBox("No duplicate", vbYesNo + vbQuestion)
                Else
                    confirmChange = MsgBox("Duplicate", vbYesNo + vbQuestion)
                End If
                MsgBox "run 3"
                If confirmChange = vbYes Then
                        UserForm1.Show
                End If
                
            End If
        End If
              
    End With
    
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Function checkDuplicate(ByVal ChangedCell As Range) As Boolean
    
    ContactNo = ChangedCell.Value
    
    Dim dupContactNo As Boolean
    dupContactNo = False
                   
    MsgBox "run 11"
    Set Rng = ActiveSheet.Cells.Find(What:=ContactNo, After:=ChangedCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not Rng Is Nothing And Rng.Address <> ChangedCell.Address Then dupContactNo = True
          
    MsgBox "run 22"
  .......
End Function

if the input exceeds the cell width, Message Run 11 can be shown but Run 22 cannot. I have no idea why and would like to ask for help for this issue.

Thank you.

 

[DanteAmor]

At the beginning of your macro, you have this instruction

[COLOR=#333333]On Error GoTo [/COLOR][COLOR=#ff0000]bm_Safe_Exit[/COLOR][COLOR=#333333][/COLOR]

That means that in any error the macro will be directed to the line bm_Safe_Exit.


If the search result is nothing then an error is generated, because rng is nothing therefore you can not ask rng.address, as this is an error, then it goes to the line bm_Safe_Exit.

Set rng = ActiveSheet.Cells.Find(What:=ContactNo

One option and the most advisable is that with code you identify the possible errors and solve them.

Most likely, when you modify two cells at the same time, then in target you do not have a single value, you have an array and you can not use .Find


try this

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim ChangedRow As Integer
    With Sh
        If Not Intersect(Target, Sh.Range("E:E")) Is Nothing Then
            
[COLOR=#ff0000]            If Target.Count > 1 Then Exit Sub[/COLOR]
            'On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            ChangedRow = Target.Row
         
            If WorksheetFunction.CountA(Target) > 0 Then
                MsgBox "run 2"
                If checkDuplicate(Target) = False Then
                    confirmChange = MsgBox("No duplicate", vbYesNo + vbQuestion)
                Else
                    confirmChange = MsgBox("Duplicate", vbYesNo + vbQuestion)
                End If
                MsgBox "run 3"
                If confirmChange = vbYes Then
                        UserForm1.Show
                End If
                
            End If
        End If
              
    End With
    
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub


Function checkDuplicate(ByVal ChangedCell As Range) As Boolean
    
    ContactNo = ChangedCell.Value
    
    Dim dupContactNo As Boolean
    dupContactNo = False
                   
    MsgBox "run 11"
    Set rng = ActiveSheet.Cells.Find(What:=ContactNo, After:=ChangedCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not rng Is Nothing And rng.Address <> ChangedCell.Address Then dupContactNo = True
          
    MsgBox "run 22"
  '.......
End Function

 

[logstarter]

Thanks.

I would also like to ask how to prevent the following find follow to find the cells before the changedcell, because it seems that it will loop back to the beginning of the worksheet.

Set Rng = ActiveSheet.Cells.Find(What:=customerContactNo, After:=ChangedCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Thank you.

 

[DanteAmor]

Youre welcome.

I do not understand what you have in the variable customerContactNo, if you have an arrangement you will also send an error, in that case you should verify that only one data