CODEWITHSUNDEEP
excelvba
Jake West
Jake West

Reputation: 131

If statement to check if user is deleting cells

I am new to vba and I am running into a Type mismatch error when I delete multiple cells in my workbook, and it is failing at the If statement here:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "Multiple Choice - Multiple Answer" Then
        MsgBox "You selected MC-MA"
    End If
End Sub

I am wondering if there's a way to check if a user is deleting contents of a cell and NOT run this if statement if that is the case?

I have tried If Trim(Target.Value) <> Empty but it throws the error I'm guessing because Im trying to run an If statement on a value that doesn't exist.

I expect If Trim(Target.Value) <> Empty to make it skip the above code but it throws the Type mismatch error.

Upvotes: 0

Views: 68

Answers (2)

Jake West
Jake West

Reputation: 131

I figured out a way around it. If I just do an If statement that checks if Target.Count = 1 then run the code it works!

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Try the SelectionChange Event like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range

    For Each cel In Target
    If cel.Value = "Multiple Choice - Multiple Answer" Then
        MsgBox "You selected MC-MA"
    End If
Next
End Sub
  • In Change event even if you try and check cells one by one, you won't spot the cell with that match because the code runs after the delete has been executed. So by the time it comes to the If statement, the cell is already blank.

Upvotes: 1

Related Questions