Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
2 Conditional formats and insert comment - ajit | Excel Discussion (Misc queries) | |||
Adding default comment text | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
comment indicators should feature lock or pw protect limiting acc. | Excel Worksheet Functions |