Question

EXCEL VBA改变批注的框的大小.

在EXCEL VBA中 如果C列有单元格=atiendhold@yahoo.com.cn (此处换行) DIKEIDM@TOMTOM.COM,就在这个EMAIL单元格处加批注:1.This message was transferred with a trial version, (此处换行) 2. I will not in the office in Monday,May 16 and will back in Tuesday,May 17.
我自己写了个VBA. 但自动添加的批注太丑了.设定批注框的大小,不知道如何写语句.
高手帮忙看看.改改

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
For Each C In Target
With C
If C = "atiendhold@yahoo.com.cn " & Chr(10) & "DIKEIDM@TOMTOM.COM" Then
.AddComment
.Comment.Text Text:="1.This message was transferred with a trial version," & Chr(10) & "2) I will not in the office in Monday,May 16 and will back in Tuesday,May 17.
& ""
Comment.Shape.Select True
With MyComments
.Comment.Shape.ScaleWidth 3, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleHeight 8, msoFalse, msoScaleFromTopLeft
.Shape.TextFrame.AutoSize = True
If .Shape.Width <> 100 Then
lArea = .Shape.Width * .Shape.Height ' 将批注转成面积
.Shape.Width = 200
.Shape.Height = (30)
End If
End With
End If
End With
Next
End Sub

Answer 1

 EXCEL VBA改变批注的框的大小的方法：

1.打开 excel 工作薄，按下 Alt + F11 键，调出excel的VBA编辑器；

2. 单击菜单栏上的“插入”，打开的菜单单击“模块”命令，如图：

3. 将下面的代码复制到模块框中，这里以将所有批注的宽度改为350为例：

Sub 批量修改批注框宽度()
Dim Cmt As Comment
For Each Cmt In ActiveSheet.Comments
Cmt.Parent.Comment.Shape.Width = 350
Next Cmt
End Sub

4. 单击工具栏绿色的“运行”按钮或直接按F5键;效果如图：

5. 点击右上角关闭按钮 退出VBA编辑器窗口，现在看一下效果图：

这样，就完成了批量修改所有excel批注框大小的任务了。

Answer 2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
With Target
If .Value = "atiendhold@yahoo.com.cn" & Chr(10) & "DIKEIDM@TOMTOM.COM" Then
.AddComment
.Comment.Text Text:="1.This message was transferred with a trial version," & Chr(10) & "2) I will not in the office in Monday,May 16 and will back in Tuesday,May 17." & ""
.Comment.Shape.TextFrame.AutoSize = True
End If
End With
End Sub

追问

不知道为什么,一运行就出现错误.".AddComment"就变成了黄色.

追答

什么错误啊,我这里可以运行啊
如果原来有批注就增加不了,会出错