multiple Excel comments and validation.add weird behavior

Question

I have quite complex Excel VBA project with sheets containing multiple comments and validations and came over some wierd issue several days ago. It happened that after adding some additional comments to the sheet validation.add stopped working properly showing comment shape for some random cell right after validation.add execution within the cell under validation. After investigation and some tests I was able to replicate the issue on an empty worksheet with the following code:

Sub CommentsBug()
Dim rng As Range
Dim i As Long
Dim rngItem As Range

Set rng = ActiveSheet.Range("A1:C25000")
For Each rngItem In rng
    rngItem.Cells(1, 1).Value = i
    If rng.Comment Is Nothing Then rngItem.AddComment
    rngItem.Comment.Text "Comment # " & i
    i = i + 1
Next

ActiveSheet.Range("E1").Activate
ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1,2,3,4,5"

End Sub

After code execution I have comment box for a random cell appearing right within the validation cell (cannot put screenshot due to lack of rep).In case I change the last processed cell to C20000 the issue does not appear. The system is Excel 2013 32-bit Office, Win 7 64. I will be greatful for any advice and walkaround.

UPDATE AND QUICK FIX:

With the help of BruceWayne it was finally possible to get a quick fix (see below as approved answer). Somehow changing For Each statement to For and addressing separate cell ranges worked. It really seems to be a bug, see important comments of John Coleman and BruceWayne on its specifics below. Hopefully someone from Microsoft will come across it, I have also posted issue at answers.microsoft.com. As soon as I already had a worksheet full of data, the following comments update code worked for me in order to get rid of appearing comment box (takes amazingly outstanding amount of time for large sheets - many hours, put the number of your rows/columns instead of 3000/500 in the cycle, remove protect/unprotect statements in case you do not have cell protection):

Public Sub RestoreComments()
Dim i As Long
Dim j As Long
Dim rng As Range
Dim commentString As String

Application.ActiveSheet.Unprotect
Application.ScreenUpdating = False
For i = 1 To 3000
    For j = 1 To 500
        Set rng = Cells(i, j)
          If Not rng.comment Is Nothing Then
            commentString = rng.comment.Shape.TextFrame.Characters.Text
            'commentString = GetStringFromExcelComment(rng.comment)
            'see Update #2
            rng.comment.Delete
            rng.AddComment
            rng.comment.Text commentString
            rng.comment.Shape.TextFrame.AutoSize = True
          End If
    Next j
Next i
Application.ScreenUpdating = True    
Application.ActiveSheet.Protect userinterfaceonly:=True

End Sub

UPDATE #2

When performing restoring comments I also came across another issue of trancation of comment string exceeding 255 characters when using comment.Shape.TextFrame.Characters.Text. In case you have long comments use the following code to return comment string:

'Addresses an Excel bug that returns only first 255 characters
'when performing comment.Shape.TextFrame.Characters.Text
Public Function GetStringFromExcelComment(comm As comment) As String
Dim ifContinueReading As Boolean
Dim finalStr As String, tempStr As String
Dim i As Long, commStrLimit As Long

ifContinueReading = True
commStrLimit = 255
i = 1
finalStr = ""

Do While ifContinueReading
    'Error handling addresses situation
    'when comment length is exactly the limit (255)
    On Error GoTo EndRoutine
    tempStr = comm.Shape.TextFrame.Characters(i, commStrLimit).Text
    finalStr = finalStr + tempStr
    If Len(tempStr) < commStrLimit Then
        ifContinueReading = False
    Else
        i = i + commStrLimit
    End If
Loop

EndRoutine: GetStringFromExcelComment = finalStr

End Function

The solution was found in the following thread (slightly changed to address the string exactly matching the limit): Excel Comment truncated during reading

Answer 1

This kludge seems to work (although there is no guarantee that the underlying bug won't bubble to the surface somewhere else)

Sub CommentsBug()
    Dim rng As Range
    Dim i As Long
    Dim rngItem As Range
    Dim kludgeIndex As Long
    Dim kludgeRange As Range
    Dim temp As String

    Application.ScreenUpdating = False
    Set rng = ActiveSheet.Range("A1:C25000")
    kludgeIndex = rng.Cells.Count Mod 65536
    For Each rngItem In rng
        rngItem.Cells(1, 1).Value = i
        If i = kludgeIndex Then Set kludgeRange = rngItem
        If rngItem.Comment Is Nothing Then rngItem.AddComment "Comment # " & i
        i = i + 1
    Next
    Application.ScreenUpdating = True
    ActiveSheet.Range("E1").Activate
    ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1,2,3,4,5"

    If Not kludgeRange Is Nothing Then
        Debug.Print kludgeRange.Address 'in case you are curious
        temp = kludgeRange.Comment.Text
        kludgeRange.Comment.Delete
        kludgeRange.AddComment temp
    End If
End Sub

When run like above, kludgeRange is cell $C$3155 -- which displays 9464. If the 25000 is changed to 26000, kludgeRange becomes cell $C$4155, which displays 12464. This is a truly weird kludge where to exorcise the ghost from cell E1 you have to go thousands of cells away.

Answer 2

So, after tweaking the code, I found that if you change the For() loop, you can stop the comment from appearing. Try this:

Sub CommentsBug()
Dim rng           As Range
Dim i             As Long
Dim rngItem       As Range
Dim ws            As Worksheet
Dim k As Integer, x As Integer

Set ws = ActiveSheet

Application.ScreenUpdating = False

Set rng = ws.Range("A1:C25000")

For k = 1 To 25000
    If i > 25000 Then Exit For

    For x = 1 To 3
        Set rngItem = Cells(k, x)
        Cells(k, x).Value = i
        If rng.Comment Is Nothing Then rngItem.AddComment
        rngItem.Comment.Text "Comment # " & i
        rngItem.Comment.Visible = False
        rngItem.Comment.Shape.TextFrame.AutoSize = True

        i = i + 1
    Next x
Next k

ws.Range("E1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1,2,3,4,5"

Application.ScreenUpdating = True
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

Note: This might take a little bit longer to run, but it doesn't give the same random comment popping up as yours does. Also, as for why this works and the other For() loop won't, I have no idea. I suspect it's something to do with the way Excel uses Validation, instead of it being something with the code (but that's pure speculation, perhaps someone else knows what is going on).