Worksheet_Change Event - Target.Name.Name error in certain situations

I'm a newbie to VBA so apologies for any daft code, I'm learning one error at a time.

The following code is supposed to copy the contents of a changed cell to the associated cell in 'Wkg - Comments' sheet (if user wants to save the changes) or reset the cell to existing value from 'Wkg - Comments'. It works fine if the user presses the Enter key or clicks the Enter tick button by the formula bar.

It doesn't work if the user presses the Delete key, or uses Ctrl+Enter - it then throws up an 'Application-defined or object-defined' error on the line 36 (I think): "strCellName = Target.Name.Name".

Please Login or Register  to view this content.

My original attempt looked like as follows, and the error appeared at "CommentColumn = CommentDBColumn(Target.Name.Name)"

Please Login or Register  to view this content.

Why does the error occur with 'Delete' and Ctrl+Enter? Does Worksheet_Change behave differently for some reason? I've tried to examine Target.Name.Name on a separate spreadsheet to see what's happening, and that seems to show that it always passes a string in any situation. What am I missing?


CommentDBColumn() is in Module1, but with my amendment above it appears the error is occurring before it is being called. Here it is just in case:

Please Login or Register  to view this content.

you would get an error if the cell is not named, or if you change more than one cell at a time-in which case you need to loop through the cells and process each one in turn

you mentioned ctrl+enter which is typically only used for entering the same information into multiple cells at once, and delete is often used on multiple cells