Instant Comment Box

**Sanecrazy** · 03-07-2012, 09:57 AM

Hi im after a simple bit of code so that when i enter the number 6111 in a certain colunm, say colunm A:A that the cell that 6111 is entered will automaticly show a comment box saying "Attention"

Shouldnt be hard for you guys?

Cheers

Sane

**Kelshaer** · 03-07-2012, 10:03 AM

use this in the code page of the desired sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 6111 Then Target.AddComment ("Attention")
End Sub

**davesexcel** · 03-07-2012, 10:07 AM

Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

Here is the code.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub

    If Target = "6111" Then
        With Target

            .ClearComments
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:="What the HECK!!"
            .Comment.Shape.TextFrame.AutoSize = True

        End With
    Else: Target.ClearComments
    End If

End Sub

**Kelshaer** · 03-07-2012, 10:24 AM

use this instead of my previous code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Value = 6111 Then
    Target.AddComment ("Attention")
ElseIf Target.Comment.Text = "Attention" Then
    Target.ClearComments
End If
End Sub

**davesexcel** · 03-07-2012, 10:33 AM

Originally Posted by Kelshaer

use this instead of my previous code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Value = 6111 Then
    Target.AddComment ("Attention")
ElseIf Target.Comment.Text = "Attention" Then
    Target.ClearComments
End If
End Sub

What determines Column A ?

**Kelshaer** · 03-07-2012, 10:38 AM

DaveExcel: Thanks, I just missed it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 or Target.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Value = 6111 Then
    Target.AddComment ("Attention")
ElseIf Target.Comment.Text = "Attention" Then
    Target.ClearComments
End If
End Sub