一、獲取單元格的備注
Private SubCommandButton1_Click()
Dim strGotIt As String
strGotIt
= WorksheetFunction.Clean(Range(“A1”).Comment.Text)
MsgBox strGotIt
End Sub
Range.Comment.Text用于得到單元格的備注文本，如果當(dāng)前單元格沒有添加備注，則會(huì)引發(fā)異常。注意代碼中使用了WorksheetFunction對(duì)象，該對(duì)象是Excel的系統(tǒng)對(duì)象，它提供了很多系統(tǒng)函數(shù)，這里用到的Clean函數(shù)用于清楚指定文本中的所有關(guān)鍵字(特殊字符)，具體信息可以查閱Excel自帶的幫助文檔，里面提供的函數(shù)非常多。下面是一個(gè)使用Application.WorksheetFunction.Substitute函數(shù)的例子，其中第一個(gè)Substitute將給定的字符串中的author:替換為空字符串，第二個(gè)Substitute將給定的字符串中的空格替換為空字符串。
Private FunctionCleanComment(author As String, cmt As String) As String
Dim tmp As String
tmp = Application.WorksheetFunction.Substitute(cmt, author & “:”, “”)
tmp = Application.WorksheetFunction.Substitute(tmp, Chr(10), “”)
CleanComment = tmp
End Function
二、修改Excel單元格內(nèi)容時(shí)自動(dòng)給單元格添加Comments信息
Private SubWorksheet_Change(ByVal Target As Excel.Range)
Dim newText As String
Dim oldText As String
For Each cell In Target
With cell
On Error Resume Next
oldText = .Comment.Text
If Err <> 0 Then .AddComment
newText = oldText & ” Changed by ” & Application.UserName & ” at ” & Now & vbLf
MsgBoxnewText
.Comment.Text newText
.Comment.Visible = True
.Comment.Shape.Select
Selection.AutoSize = True
.Comment.Visible = False
End With
Next cell
End Sub
Comments內(nèi)容可以根據(jù)需要自己修改，Worksheet_Change方法在Worksheet單元格內(nèi)容被修改時(shí)執(zhí)行。
三、改變Comment標(biāo)簽的顯示狀態(tài)
SubToggleComments()
If Application.DisplayCommentIndicator = xlCommentAndIndicator Then
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
Else
Application.DisplayCommentIndicator = xlCommentAndIndicator
End If
End Sub
Application.DisplayCommentIndicator有三種狀態(tài)：xlCommentAndIndicator-始終顯示Comment標(biāo)簽、xlCommentIndicatorOnly-當(dāng)鼠標(biāo)指向單元格的Comment pointer時(shí)顯示Comment標(biāo)簽、xlNoIndicator-隱藏Comment標(biāo)簽和單元格的Comment pointer。
四、改變Comment標(biāo)簽的默認(rèn)大小
SubCommentFitter1()
With Range(“A1”).Comment
.Shape.Width = 150
.Shape.Height = 300
End With
End Sub
注意：舊版本中的Range.NoteText方法同樣可以返回單元格中的Comment，按照Excel的幫助文檔中的介紹，建議在新版本中統(tǒng)一使用Range.Comment方法。
