Conditional Comment
 

I want to add a comment based on the contents of a particular cell. The
comment should either appear or not, depending on the cell value. I realise
this will require a macro, but don't know how to do this.

Hi Ken,
Does this help?
Just as a demo, if the value in cell A1 of the active sheet changes to
become greater than 10 then Cell B1 has the comment "A1 is greater than
10" added.
The code must be pasted into the ThisWorkbook module. If you don't
want all sheets affected then paste the code into the WorkSheet_Change
Sub of the sheet you want affected.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If ActiveSheet.Cells(1, 1).Value 10 Then
With ActiveSheet.Cells(1, 2)
On Error Resume Next 'if no comment to delete then error is
ignored
.Comment.Delete 'remove old comment
On Error GoTo 0
.AddComment
.Comment.Visible = True 'change to False if you only want to
see the comment
'when the cursor is positioned over the
commented cell
.Comment.Text Text:="A1 is greater than 10"
.Comment.Shape.TextFrame.AutoSize = True 'comment frame size
will suit any size comment
End With
Else: On Error Resume Next
ActiveSheet.Cells(1, 2).Comment.Delete 'delete old comment because A1
is not 10
On Error GoTo 0
End If
End Sub

Ken Johnson

If you want a comment to appear as the value is being entered in the
cell, you can use DataValidation. On the first tab in the Data
Validation dialog box, enter your settings, and on the Error Alert tab,
enter the comment you want to appear for an invalid entry.

Ken G. wrote:
I want to add a comment based on the contents of a particular cell. The
comment should either appear or not, depending on the cell value. I realise
this will require a macro, but don't know how to do this.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

This is what I was looking for. Thanks Ken. The only problem I now have is
that although I've unprotected the comment in the format comment dropdown,
and I've re-sized it (commented out the auto size in the macro) when I
protect the sheet, the comment protection is re-set to on and my re-size
dimensions disapper. I'm assuming this is because its not an Excel comment
but one created by the macro. How do I overcome this?

" wrote:

Hi Ken,
Does this help?
Just as a demo, if the value in cell A1 of the active sheet changes to
become greater than 10 then Cell B1 has the comment "A1 is greater than
10" added.
The code must be pasted into the ThisWorkbook module. If you don't
want all sheets affected then paste the code into the WorkSheet_Change
Sub of the sheet you want affected.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If ActiveSheet.Cells(1, 1).Value 10 Then
With ActiveSheet.Cells(1, 2)
On Error Resume Next 'if no comment to delete then error is
ignored
.Comment.Delete 'remove old comment
On Error GoTo 0
.AddComment
.Comment.Visible = True 'change to False if you only want to
see the comment
'when the cursor is positioned over the
commented cell
.Comment.Text Text:="A1 is greater than 10"
.Comment.Shape.TextFrame.AutoSize = True 'comment frame size
will suit any size comment
End With
Else: On Error Resume Next
ActiveSheet.Cells(1, 2).Comment.Delete 'delete old comment because A1
is not 10
On Error GoTo 0
End If
End Sub

Ken Johnson

Ken G,
Sorry Ken I'm not sure what you can do about that.
Try a new posting for the real experts to have a look.
I wasn't even aware of the format comment drop down till you poited it
out.
Hope you get it sorted out.
Ken Johnson