如何用VBA给EXCEL插批注，当我单元格输入1时，批注是一个值，输入2时是另外一个值，该怎么做，最好有源代码

Question

如题所述

Answer 1

在工作表标签单击右键
点选"查看代码"
粘贴下面的代码
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Comment Is Nothing Then
If Target.Value = 1 Then
Target.AddComment "A"
Else
Target.AddComment "B"
End If
Else
If Target.Value = 1 Then
Target.Comment.Text "A"
Else
Target.Comment.Text "B"
End If
End If
End Sub

回到工作表中输入试试追问

如果不止1,2呢？还有直到10的话怎么写呢

追答

'每一项里面按要求增加更多的elseif
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Comment Is Nothing Then
If Target.Value = 1 Then
Target.AddComment "A"
ElseIf Target.Value = 2 Then
Target.AddComment "B"
ElseIf Target.Value = 3 Then
Target.AddComment "C"
ElseIf Target.Value = 4 Then
Target.AddComment "D"
ElseIf Target.Value = 5 Then
Target.AddComment "E"
ElseIf Target.Value = 6 Then
Target.AddComment "F"
ElseIf Target.Value = 7 Then
Target.AddComment "G"
ElseIf Target.Value = 8 Then
Target.AddComment "H"
ElseIf Target.Value = 9 Then
Target.AddComment "I"
ElseIf Target.Value = 10 Then
Target.AddComment "J"
End If
Else
If Target.Value = 1 Then
Target.Comment.Text "A"
ElseIf Target.Value = 2 Then
Target.Comment.Text "B"
ElseIf Target.Value = 3 Then
Target.Comment.Text "C"
ElseIf Target.Value = 4 Then
Target.Comment.Text "D"
ElseIf Target.Value = 5 Then
Target.Comment.Text "E"
ElseIf Target.Value = 6 Then
Target.Comment.Text "F"
ElseIf Target.Value = 7 Then
Target.Comment.Text "G"
ElseIf Target.Value = 8 Then
Target.Comment.Text "H"
ElseIf Target.Value = 9 Then
Target.Comment.Text "I"
ElseIf Target.Value = 10 Then
Target.Comment.Text "J"
End If
End If
End Sub

Answer 2

=========================================================================

 

打开Excel，按ALT+F11，双击左边的sheet1，输入以下代码：

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:D100")) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub

If Target.Value = "1" Then

Target.Comment.Delete

Target.AddComment.Text Text:="内容是1"

ElseIf Target.Value = "2" Then

Target.Comment.Delete

Target.AddComment.Text Text:="内容是2"

End If

End Sub

 

 

如下图（点击可放大） 

 

 

本回答被网友采纳

Answer 3

这是用单元格事件完成的，在A1中输入时根据条件插入批注
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Str1$
If Target.Address = "$A$1" Then
If Target.Value = 1 Then
Str1 = "1的批注"
ElseIf Target.Value = 2 Then
Str1 = "2的批注"
Else
Str1 = "其他的批注"
End If
With Range("A1")
.Comment.Delete
.AddComment
.Comment.Visible = False
.Comment.Text (Str1)
End With
End If
End Sub