VBA Code to automatically add a comment if a cell contains a certain letter

I just started learning VBA last week and now I am trying to solve the following problem: If users type in a certain letter (and only these specified letters), then a comment should be added automatically to the selected cell. The comment should contain a small "header" and then an explanatory text, which is typed in by the user. The shown code actually worked, but only for the first cell I tested it with. So, when I executed it the first time, the comment automatically appeared with the specified text as I intended, but in the next cell it didn't. Even restarting Excel didn't help - so now even the first cell doesn't create the comment. That's why I am confused.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x

Application.EnableEvents = False

If Target.Value = "" Then

    Application.Undo
    x = Target.Value
    Target.Value = ""
    On Error Resume Next

    If (x = "A") Or (x = "B") Or (x = "C") Or (x = "D") Or (x = "E") Then Target.Comment.Delete
    On Error GoTo 0   

ElseIf Target.Value = "A" Then
    Target.AddComment ("explanationA: ")

ElseIf Target.Value = "B" Then
    Target.AddComment "explanationB: "

ElseIf Target.Value = "C" Then
    Target.AddComment "explanationC: "

ElseIf Target.Value = "D" Then
    Target.AddComment "explanationD: "

ElseIf Target.Value = "E" Then
    Target.AddComment "explanationE: "

End If
End Sub

I have specified to "Worksheet" and "Change".

There is also an additional question, which would be of interest for me: is there a possibility, that excel automatically selects the comment after it was generated, so that the user can type in some explanatory information without needing to select the comment manually?

Help is appreciated! Thank you very much in advance!

Solution

You can use Option Compare Text at the declarations section (top) of your module to allow comparisons for the entire module to be case-insensitive.

Although the code within the sub routine "MyCompare" is identical, their outcome is different:

Sub MyCompare

    Debug.print "a" = "A"
    ' Prints False

End Sub

Option Compare Text

Sub MyCompare

    Debug.Print "a" = "A"
    ' Prints True

End Sub

But anyway, let's get to your actual problem. It's with this line in your code:

Application.EnableEvents = False

Notice the reason your code worked the first time was because you captured the Worksheet_Change event. But now you just disabled events within this code and never turned it back on, therefore you will no longer be able to run your Worksheet_Change subroutine.

Just make sure to turn it back on before you exit your code. Also, it would be wise to handle your errors in a way that will enable events rather than prematurely stopping your code, which would keep them disabled.

The way I usually enable events without running a Sub is by using the debug window. Press Ctrl + G within the VBE to open this window, then within the window simply type Application.EnableEvents = True and press Return and you've just change that property.