Geek Answers Handbook

AddComment on multiple vba excel sheets

The AddComment syntax works on the first sheet selected in the workbook, but for the next it gives me this error: Error 1004 "User-defined or object error." I donโ€™t know why it crashes when selecting multiple sheets and only works for the first one selected. Does anyone have an 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 alternative set of code that indicates the problem. (Run this with three blank sheets in a new book.):

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
+6
source share
5 answers

, , . - , . ... someSheet.Select. .

+3

, , Yoweks - : , -, ( , ) , .

, , . PLease :

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
+3

Excel

,

"" Excel , . , Excel, , .

, , , .

, , , .

Sub UpdateComment(Rng As Range, Cmnt As String)
    Application.ScreenUpdating = False
    ' Get currently selected sheets
    Dim mySheets As Sheets: Set mySheets = ThisWorkbook.Windows(1).SelectedSheets
    ' Set current selection to just one sheet: this is where error is avoided
    ThisWorkbook.Sheets(1).Select
    ' Set Comment, new if doesn't exist or changed if it does
    If Rng.Comment Is Nothing Then
        Rng.AddComment Cmnt
    Else
        Rng.Comment.Text Cmnt
    End If
    ' Tidy up: re-select sheets & enable screen updating
    mySheets.Select
    Application.ScreenUpdating = True
End Sub

, :

' ... your previous code
Set rng = selectedSheet.Cells(1, columnIndex)
UpdateComment rng, "In standard report this crash starts to deploy from ..." 


Dim sh As Worksheet
For Each sh In ThisWorkbook.Windows(1).SelectedSheets
    Set rng = sh.Cells(1, columnIndex)
    UpdateComment rng, "In standard report this crash starts to deploy from ..."
Next sh
+2

, , , , ; .

Sub General_Functions_Comments(InCell As Range, TxtComment As String, Optional IsMergedAnalyzed As Boolean)
Dim IsComment As Comment
Dim RangeFixedMerged As Range
    If InCell.MergeCells = False Or IsMergedAnalyzed = True Then ' 3. If InCell.MergeCells = False
    With InCell
    Set IsComment = .Comment
    If IsComment Is Nothing Then ' 1. If Iscomment Is Nothing
    .AddComment.Text Text:=TxtComment
    .Comment.Shape.TextFrame.AutoSize = True
    .Comment.Visible = False
    Else ' 1. If Iscomment Is Nothing
    If InStr(.Comment.Text, TxtComment) Then ' 2. If InStr(.Comment.Text, TxtComment)
    Else ' 2. If InStr(.Comment.Text, TxtComment)
    .Comment.Text .Comment.Text & Chr(10) & TxtComment
    .Comment.Shape.TextFrame.AutoSize = True
    .Comment.Visible = False
    End If ' 2. If InStr(.Comment.Text, TxtComment)
    End If ' 1. If Iscomment Is Nothing
    End With
    Else ' 3. If InCell.MergeCells = False
    Set RangeFixedMerged = InCell.Cells(1, 1)
    Call General_Functions_Comments(RangeFixedMerged, TxtComment, True)
    Set RangeFixedMerged = Nothing
    End If ' 3. If InCell.MergeCells = False
End Sub


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
                                Call General_Functions_Comments(Rng, "In Standard Report this crash starts to deploy from " & CStr(amplitude) & " amplitude", True)
                                Else: Call General_Functions_Comments(Rng, "In Standard Report this crash starts to deploy from " & CStr(amplitude) & " amplitude", True)
                                End If
                            End If
                        End If
                    End If
                End If

* , if, else, ?

+1

( - ), , , , , ...

Please note that if you select multiple sheets, the "New Comment" button on the ribbon is inactive , so you simply cannot do this from the code if you cannot do it manually.
What for? - Do not ask me. I see a good workaround above, which seems to be the only way to achieve what you need.

+1
source

More articles:


All Articles