AddComment on multiple sheets vba Excel

Question

The AddComment syntax works on first selected sheet in workbook, but for the next one gives me this error: Error 1004 "Application-defined or Object-defined error". I do not know why crashes if multiple sheets were selected and works only for the first selected one. Does anyone have some idea?

 If selectedSheet.Cells(7, columnIndex).value <> 100 Then
           selectedSheet.Cells(7, columnIndex).Interior.ColorIndex = 3

           If standardReportFilePath <> "" Then 'not using the Standard Report Evalution algorithm
                        If VerifyStandardReportFile(selectedSheet.Name, selectedSheet.Cells(1, columnIndex).value, wbk, amplitude, missingCrashes) = True Then
                                selectedSheet.Cells(1, columnIndex).Interior.ColorIndex = 36 ' color the crash cell with yellow
                                Set rng = selectedSheet.Cells(1, columnIndex)
                                If rng.Comment Is Nothing Then
                                    **rng.AddComment "In Standard Report this crash starts to deploy from " & CStr(amplitude) & " amplitude"**
                                Else
                                    rng.Comment.Text "In Standard Report this crash starts to deploy from " & CStr(amplitude) & " amplitude"
                                End If
                            End If
                        End If
                    End If
                End If

An alternate set of code that shows the problem. (Run this with three blank worksheets in a new workbook.):

Sub test()
    Dim ws As Worksheet
    Dim Rng As Range

    'Running code with a single sheet selected
    Worksheets("Sheet1").Select

    'Code that shows issue - this will work
    Set ws = Worksheets("Sheet2")
    Set Rng = ws.Cells(1, 1)
    If Rng.Comment Is Nothing Then
        Rng.AddComment "xxx"
    End If

    'Get rid of comment again
    Rng.Comment.Delete

    'Running code with multiple sheets selected
    Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

    'Code that shows issue - will crash on the "AddComment"
    Set ws = Worksheets("Sheet2")
    Set Rng = ws.Cells(1, 1)
    If Rng.Comment Is Nothing Then
        Rng.AddComment "xxx"
    End If

End Sub

Answer 1

I found a workaround, but still don't know why this problem even happens. For some reason error occurs when you have more then one worksheet selected. The solution is... To select one sheet before adding comments with someSheet.Select. At the end of macro you can try to select all previously selected sheets again if needed.

Answer 2

What I do understand - thanks to Yoweks comment - is: You are looping through all the selected sheets, check something, set comments (giving you the problems, because it does'nt work with more than one selected sheet) and want the previosly selected sheets to be selected afterwards.

You can save the previosly selected sheet in a variable, select one of them, run your code and then select all previosly selected sheets again. PLease try the following code:

Sub Comments()
Dim WsArr As Sheets, WS As Worksheet, ColIdx As Long
ColIdx = 7
Set WsArr = ActiveWorkbook.Windows(1).SelectedSheets
    WsArr(1).Select
    For Each WS In WsArr
        '*** your logic
        Set Rng = WS.Cells(1, ColIdx)
        If Rng.Comment Is Nothing Then
            Rng.AddComment "In Standard Report this crash starts to deploy from " & CStr(amplitude) & " amplitude"
        Else
            Rng.Comment.Text "Changed T"
        End If
    Next WS
    WsArr.Select
End Sub