Why am I getting Type Mismatch errors? Also, how to get a Value before Deletion?

[theteerex]

My code below is supposed to pull up a prompt when deleting a cell in a particular row.
Before the deletion is done, a message box pops up and asks if they're sure.
It should then copy the value of that cell before deleting and use that value as a variable for another macro that deletes cells in another worksheet.
After I run the code as is, I then get a type mismatch error. Why is that?
Otherwise, the row does get deleted.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#008000]' If target column is B, is below the header row AND it's new value is blank then...[/COLOR]
If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then
[COLOR=#008000]' Clear contents of row[/COLOR]
ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
If ans = vbYes Then
[B][COLOR=#ff0000]'How do I select Column 2, Row active cell and copy the value?[/COLOR][/B]
[B][COLOR=#ff0000]'How do I make the value into variable YYY?[/COLOR][/B]


Rows(ActiveCell.Row).EntireRow.Delete 
Exit Sub
End If
End If
End Sub

Any help would be greatly appreciated.

 

[JoeMo]

You didn't say what line is causing the type mismatch error or what you want to do with the copied cell contents, but maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
' If target column is B, is below the header row AND it's new value is blank then...
If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then
' Clear contents of row
    ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
    If ans = vbYes Then
'How do I select Column 2, Row active cell and copy the value?
        With Cells(Target.Row, 2)
            .Copy 'what do you want to do with the copied value??
'How do I make the value into variable YYY?
            YYY = .Value
        End With
        Application.EnableEvents = False
        Rows(ActiveCell.Row).EntireRow.Delete
        Application.EnableEvents = True
        Exit Sub
    End If
End If
End Sub

 

[theteerex]

If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then is the line that gets highlighted in yellow.
I have another code that will be using the variable.
I said that up above too.

Here is the code where I want to use YYY

Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
'This code finds the entries corresponding to the deleted row in Sheet1 and deletes them


strSearch = "YYY" 'Change to the code ActiveCell.Value or some variation
Set rDelete = Nothing


Application.ScreenUpdating = False


With Sheet2.Columns("C:C")
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not rFind Is Nothing Then
        Do
            Set rDelete = rFind
            Set rFind = .FindPrevious(rFind)
            If rFind.Address = rDelete.Address Then Set rFind = Nothing
            rDelete.EntireRow.Delete
        Loop While Not rFind Is Nothing
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

After I run the code as is, I then get a type mismatch error. Why is that?

Because this is 'event' code and when your code deletes the entire row it has changed the worksheet so the code is called again. When it gets to the following line (on this second pass), the red part is meaningless since this time 'Target' is 16,000+ cells which could have many different values so Target.Value is what is causing your error.

You needed to disable the event code while you make the change - see JoeMo's code for the idea about that.

If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then

 

[theteerex]

So I made some changes to the code.
It now reads as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ans As Integer
    Dim YYY As String


    If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then


    ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
    If ans = vbYes Then


        With Application
            .EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
            .Undo ' undo the change
            YYY = Target.Value ' assign the deleted-undone value in to YYY
            Rows(ActiveCell.Row).EntireRow.Delete ' delete that row
            .EnableEvents = True ' re-enable all events
        End With
        MsgBox YYY ' now you have that value in YYY
    Exit Sub
    End If


    End If


End Sub

How do I use YYY as a variable in my other code?

Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
'This code finds the entries corresponding to the deleted row in Sheet1 and deletes them


strSearch =[COLOR=#ff0000] "YYY"[/COLOR] 'Change to the code ActiveCell.Value or some variation
Set rDelete = Nothing


Application.ScreenUpdating = False


With Sheet2.Columns("C:C")
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not rFind Is Nothing Then
        Do
            Set rDelete = rFind
            Set rFind = .FindPrevious(rFind)
            If rFind.Address = rDelete.Address Then Set rFind = Nothing
            rDelete.EntireRow.Delete
        Loop While Not rFind Is Nothing
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[theteerex]

I am trying to call my other code TestDeleteRows but excel giving me an error:
Compile error:
Sub or Function not defined

This is weird because I did not misspell the macro and it definitely exists. Any ideas?

Colored in red so you can see where I added it.
I defined YYY outside the sub routine so it can be used in multiple macros.

Public YYY As String


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ans As Integer


    If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then


    ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
    If ans = vbYes Then


        With Application
            .EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
            .Undo ' undo the change
            YYY = Target.Value ' assign the deleted-undone value in to YYY
            Rows(ActiveCell.Row).EntireRow.Delete ' delete that row
            .EnableEvents = True ' re-enable all events
        End With
        MsgBox YYY ' now you have that value in YYY
       
    Exit Sub
    End If
[B][COLOR=#ff0000]        Call TestDeleteRows[/COLOR][/B]
    End If


End Sub