excel - 在 MsgBox 中显示工作簿每张表的注释

Question

我试图在 Activeworkbook 中显示每个工作表的所有注释文本在 MsgBox (对于每条评论)。

我的代码没有抛出错误，所以我知道我很接近。

Sub ShowAllWorkbookcomments()

On Error Resume Next

Dim ws As Worksheet
Dim rng As Range
Dim cell As Variant
Dim cmt As String
Dim commentcount As Integer

Set ws = ActiveWorkbook.Worksheets(1)
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
commentcount = rng.Count
'cmt = ws.rng.Comment.Text

Dim varComment As String
Dim c As Comment

For Each ws In ActiveWorkbook.Worksheets

    Select Case commentcount
        Case 0
            MsgBox "No Comment", vbExclamation
            Resume Next
        Case Is > 0            
            For Each cell In rng
                varComment = c.Text
                MsgBox varComment, vbInformation
            Next cell
    End Select

    Set rng = Nothing            
Next ws    

End Sub

Answer 1

你很接近，只需要得到Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)里面For Each ws In ActiveWorkbook.Worksheets环形。

此外，添加了另一种方法来捕获工作表没有评论的可能性，并删除了不必要的 Select Case .

试试下面的代码:

Option Explicit

Sub ShowAllWorkbookcomments()

Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim cmt As String
Dim varComment As String

For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next 
    Set rng = ws.Cells.SpecialCells(xlCellTypeComments)
    On Error GoTo 0

    If Not rng Is Nothing Then '<-- current worksheet has comments
        For Each cell In rng.Cells
            'varComment = cell.Comment.text
            varComment = "worksheet " & ws.Name & " comment " & cell.Comment.text ' <-- added the worksheet name as reference 
            MsgBox varComment, vbInformation
        Next cell
    Else '<-- current worksheet has No comments >> rng is Nothing
        'MsgBox "No Comment", vbExclamation
        MsgBox "worksheet " & ws.Name & " has No Comments", vbExclamation ' <-- added the worksheet name as reference 
    End If
    Set rng = Nothing
Next ws

End Sub