Worksheet_Change Producing Error on Insert Row

[LBinGA]

Hi all:

I have the following code on my sheet. If "Yes" is entered into Column A, the entire Row is moved to the Archive sheet, perfectly.

Private Sub Worksheet_Change(ByVal Target As Range)Dim rngDest As Range
Set rngDest = Worksheets("Prospect Archive").Range("rngDest")
Dim rngTrigger As Range
 
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheets("Prospect List").Range("rngTrigger")) Is Nothing Then
 
' Only trigger if the value entered is TRUE
    
     If Target.Value = "Yes" Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.FormatConditions.Delete
        Selection.Cut
        rngDest.Insert Shift:=xlDown
        Selection.Delete
' Reset EnableEvents
        Application.EnableEvents = True
    End If
End If

It produces Run-Time Error 13: Type Mismatch if the user tries to either manually add or delete a row or use the following code on a button to add a row:

Sub InsertCompany()Dim sNewName As String
Dim lPosition As Long
Dim rEmpList As Range


Set rEmpList = Range("C1:C1000")


sNewName = InputBox("Enter Name of New Company")
On Error Resume Next 'if employee needs to go at start of list, Match will return [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NA"]#NA[/URL] 
lPosition = Application.WorksheetFunction.Match(sNewName, rEmpList, 1)
On Error GoTo 0 'revert to normal error handling (crashing)
rEmpList(lPosition + 1).EntireRow.Insert
rEmpList(lPosition + 1).Value = sNewName
rEmpList(lPosition + 1).Activate


End Sub

Debugging brings me to this line in the Worksheet_Change code: If Target.Value = "Yes" Then

I have change the Dim of RngTrigger to String & Boolean. No difference

The sheet is NOT formatted as a Table because when it is, the Column A code "Yes" code no longer works.

This feels like it should be fairly straightforward. I'm probably missing something uber easy. Can anyone help?

Thanks,

LBinGA

 

[Fluff]

Add this to the top of your change event

   If Target.CountLarge > 1 Then Exit Sub

 

[LBinGA]

Works perfectly! Thank you!

Any thoughts on why the code to move to the Archive sheet no longer works if I Format the sheet as a Table?

The "Yes" just sits in column A staring at me.

 

[Fluff]

Fraid not, I've very rarely had the need to work with tables.

 

[LBinGA]

No problem leaving it as is then, I suppose. Now that I have the add Row working, I don't think it's an issue.

Thanks again~

 

[Fluff]

Your welcome