Run time error 13 Type mismatch

[TODDLL]

I have an Excel table with several columns. In 3 of the columns, I have vba code entered to pop up a msg if a "Y" is entered into the field. Works as it should. However, if I want to delete the row from the table, I get the Runtime error 13. If I delete just the cell with the "Y", no problem. If I try to delete other column fields in that row I receive the error. The data does get deleted, but I get the runtime error. Any way around this? I have also tried clearing the content of the row, but same issue. I have attached the vba code associated with these columns if that would help figure out what to do. Thank you.

 

[Michael M]

Try declaring your variables.
If you have further problems, post the code, rather than a picture, and someone will providing assistance

 

[joeu2004]

Add:

If Target.Count <> 1 Then Exit Sub

before the Target.Value="Y" line.

PS.... But that means that if the user selects G1:G10, types Y, then presses ctrl+Enter (not ctlr+shift+Enter), your event macro will not display any messages. You could loop when Target.Count > 1 is true. But it is unclear that that is what you want to do when deleting a column or row, notably column G, H or I.

 

[joeu2004]

(Sorry, too late to edit....)

PS.... But that means that if the user selects G1:G10, types Y, then presses ctrl+Enter (not ctlr+shift+Enter), your event macro will not display any messages. You could loop when Target.Count > 1 is true. But it is unclear that that is what you want to do when deleting a column or row, notably column G, H or I.

Use the event macro below, and try the following experiment to decide what you want.

1. Select G1:G10, type Y, then press ctrl+Enter to enter Y into the 10 cells all at once.
2. Select G3:G5, right-click and click Delete > Shift Up.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Count & vbNewLine & Target.Address
End Sub

 

[TODDLL]

Michael, thank you for telling me about the XL2BB. I was not aware of this, and will be helpful going forward.
Joeu2004, your code worked perfectly, thank you!