EXCEL批注修改代码

1、批量修改批注位置属性

Sub 设置批注属性()

Dim TTT$

TTT = InputBox("●本程序将设置本工作表批注属性为：" & Chr(13) _

& " 【大小固定，位置随单元格而变】" & Chr(13) _

& "●选定单一单元格时，对整个工作表" & Chr(13) _

& " 进行处理，选定多个单元格时，只" & Chr(13) _

& " 处理选定区域内的单元格." _

& Chr(13) & "是否确定继续执行？", "注意", "是")

If TTT <> "是" Then Exit Sub

Application.ScreenUpdating = False

On Error GoTo 10

Dim TempRngA As Range

Dim tempRngB As Range

Set TempRngA = Selection

Set tempRngB = ActiveCell

Dim Rng As Range

Application.DisplayCommentIndicator = xlCommentAndIndicator

If Selection.Cells.Count = 1 Then Cells.Select Selection.SpecialCells(xlCellTypeComments).Select For Each Rng In Selection

Rng.Activate

ment.Shape.Select True

Selection.Placement = xlMove

Next Rng

10 TempRngA.Select

tempRngB.Activate

Set TempRngA = Nothing

Set tempRngB = Nothing

Application.DisplayCommentIndicator = xlCommentIndicatorOnly

Application.ScreenUpdating = True

End Sub

批量修改批注位置

Sub 恢复批注到默认位置()

Dim Cmt As Comment

For Each Cmt In ments

With Cmt

.Shape.Top = .Parent.Top - 7.5

.Shape.Left = .Parent.Left + .Parent.Width + 11.25 End With

Next

End Sub

2、批量修改批注文字格式

Sub 批量修改批注()

Dim cm As Comment

For Each cm In ments 'sheet1是我们要批量修改批注的工作表，可以按需改变。

With cm.Shape.TextFrame.Characters.Font

.Name = "楷体"

.Size = 14

.ColorIndex = 3

End With

Next

End Sub

3、批量修改批注框大小，统一尺寸

Sub 批量修改批注框大小()

Dim Cmt As Comment

For Each Cmt In ments

ment.Shape.Width = 200

Next Cmt

End Sub

高度

Sub 批量修改批注框高度()

Dim Cmt As Comment

For Each Cmt In ments ment.Shape.Height = 250 Next Cmt

End Sub