1. 删除批注中的用户名：
Private Sub CommandButton1_Click()
For Each i In ActiveSheet.Comments ‘批注中的每一个
With i
Dim MYSTR1 As Integer ‘声明整型
Dim MYSTR2 As Integer ‘声明整型
Dim RESULT As Integer ‘声明整型
MYSTR1 = Len(.Author) ‘作者的长度
MYSTR2 = Len(.Text) ‘整个批注的长度
RESULT = MYSTR2 - MYSTR1 C 1 ‘再减1是因为有一个冒号 占一位
.Text Right(.Text, RESULT) ‘批注的文本为原批注从右边取除了作者名的长度
.Visible = True ‘将批注视为可见
End With
Next
End Sub
2. 将批注中的用户名更名:
在上述代码后接以下代码:
Private Sub CommandButton1_Click()
For Each i In ActiveSheet.Comments ‘批注中的每一个
With i
Dim MYSTR1 As Integer ‘声明整型
Dim MYSTR2 As Integer ‘声明整型
Dim RESULT As Integer ‘声明整型
MYSTR1 = Len(.Author) ‘作者的长度
MYSTR2 = Len(.Text) ‘整个批注的长度
RESULT = MYSTR2 - MYSTR1 C 1 ‘再减1是因为有一个冒号 占一位
.Text Right(.Text, RESULT) ‘批注的文本为原批注从右边取除了作者名的长度
.Visible = True ‘将批注视为可见
End With
For Each i In ActiveSheet.Comments ‘遍历每一个批注
i.Text "PL:" & ONE.Text ‘批注的文本前加PL:
Next
End Sub
3. 将批注中的用户名更名:
在上述代码后接以下代码:
Private Sub CommandButton1_Click()
Dim mCom As Comment
For Each mCom In ActiveSheet.Comments
With mCom.Shape.TextFrame.Characters
With .Font
.Bold = msoTrue '改变粗细
.Size = 14 '改变大小
.ColorIndex = 3 '改变颜色
End With
End With
Next
End Sub
4. 删除所有批注
Private Sub CommandButton1_Click()
For each i in activesheet.comments
i.delete
next
End sub
5. 导出批注
Private Sub CommandButton1_Click()
On Error GoTo line
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim cur, temp As Worksheet
Set cur = ActiveSheet
Set temp = Worksheets.Add
temp.Name = "导出批注"
Dim row As Integer
row = 1
For Each one In cur.Comments
temp.Cells(row, 1).Value = one.Text
temp.Cells(row, 2).Value = one.Parent.row
temp.Cells(row, 3).Value = one.Parent.Column
one.Delete
row = row + 1
Next
MsgBox "批注导出到表中"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
line:
MsgBox "批注未导出,可能以前导过."
temp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
