[Solved] Conditional Comments

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146
This probably isn’t possible, but does anyone know of a way to make a conditional comment? I need something that – like when you insert a comment into a cell – if you hover over the respective cell a comment pops up. However, I want this comment to change dependant on what is listed in the cell (i.e. there is an equation in the cells that change the outputs over time).
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
This code will change all comments in the worksheet, depending on the cell value.
To use, copy the code. Right-click the tab of the sheet where you want it to work, select View Code, and paste the code in the blank space.
Note: This code will create comments in any cell that you edit. To turn that off, comment out or delete the chunk of code that creates the new comment.
In the Select Case block, you can change the cutoff value and the comment that you want to display.
Also, I got the HasComment function from David McRitchie's site.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C As Comment
    Dim Ws As Worksheet
    
    If Target.Cells.Count > 1 Then Exit Sub
    
   'create a comment in the target cell if it doesn't already exist
    'delete to the end of the End If line if not required
    If Not HasComment(Target) Then
        Target.AddComment
    End If
    
    'loop through comments in worksheet, changing as necessary
    Set Ws = ActiveSheet
    For Each C In Ws.Comments
        Select Case C.Parent.Value
            Case Is < 30
                C.Text Text:="Value less than 30"
            Case Else
                C.Text Text:="Value at least 30"
        End Select
    Next C
End Sub

Function HasComment(Target As Range) As Boolean
   'Patrick Molloy, programming 2001-11-17
   On Error Resume Next    ' to use in WS:  =HasComment(a1)
   Dim txt As String       ' to use in VBA: MsgBox hascomment(Range("a1"))
    txt = Target.Comment.Text   'in Event: MsgBox hascomment(Target)
    HasComment = Err.Number = 0
    Err.Clear
End Function
Denis
 
Upvote 0
Here is some code I use for adding comments to cells, and deleting comments from other cells.

[Edit] I've edited the code below slightly. (the helper function was missing End Function)

1.
Code:
' Places a Comment of desired size, in desired cell, with desired text
Sub MyCmt(Rng As Range, Optional cText As String = "", Optional iDefaultMaxWidth As Integer = 300, Optional iHeightStretchFactor As Double = 10)
'If Len(cText) < 1 Then cText = Rng.Value  ' I like to use this default
With Rng
If CommentExists(Rng) = False Then Set Cmmt = .AddComment
.Comment.Text cText
   With .Comment
         .Shape.TextFrame.AutoSize = True
          If .Shape.Width > iDefaultMaxWidth Then .Shape.Width = iDefaultMaxWidth
          y = .Shape.Height
          .Shape.Height = y * iHeightStretchFactor
   End With
If Len(cText) < 1 Then Rng.Comment.Delete
End With
End Sub


2.
Code:
' This is a helper function that determines if a comment already exists in a cell.
' Returns True if Comment Exists, False if it Does Not
Function CommentExists(Rng As Range) As Boolean
On Error GoTo Skip
sDummy = Rng.Comment.Text  'Should error if there is no comment
CommentExists = True
Exit Function
Skip:
CommentExists = False
End Function

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
   Call MyCmt(c, c.Value & Chr(10) & c.Formula) 'adds comment
Next

For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
   Call MyCmt(c, "")
Next c

For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks)
   Call MyCmt(c, "")
Next c
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Okay, I got the stuff pasted in correctly but I’m not sure that I can make it work correctly. Here’s the scenario:

One sheet contains a database of names and dates as so:

Chad ..... 12-Nov-05 ..... 13-Dec-05
Dan ........5-Dec-05 ....... 3-Jan-06
Brad ...... 10-Jan-06 ..... 20-Feb-06

On another sheet, I’ve created a spreadsheet that produces these names in a certain order depending on today’s date and the dates supplied. For example one row of cells produces the following:

Date: ...... Nov-05 ..... Dec-05 ..... Jan-06
Name: ..... Chad ........ Dan ......... Dan

As time progresses, the date in the date row changes, thus causing the names in each cell to change as well. Example, 1 month passes and automatically produces the following:

Date: ...... Dec-05 ..... Jan-06 ..... Feb-06
Name: ...... Dan ......... Brad ........ Brad

Here’s what I’m trying to do. I would like each cell that contains a name to have a comment which shows the two dates associated with that name. For example, a cell containing Dan’s name would have a comment which reads:

Dan: 5-Dec-05 to 3-Jan-6

As time progresses and the name in each cell updates, I would like each comment in each cell to update automatically reflecting the name change.

I’m not sure that this is possible with the code provided …or – more likely – I don’t know how to do it.
 
Upvote 0
Honestly, I can still probably make SydneyGeek’s version work (I’ll just create a refresh button that inputs the name entries when pressed thus forcing a manual entry which should make the code run) but I still am not sure how to modify the portion of the code that produces the comment text. How do I code so that I take the name from the cell, match it with the name in the database, then display the name and the two dates associated with the name in the comment box as previously mentioned? Also, if for some reason the name is not in the database, I would like for no comment to be inserted in the cell and, if a comment is already in the cell, I would like it deleted.
 
Upvote 0
I edited my original post slightly. The helper function I'd pasted was missing the last couple of lines.

Assuming:
1. You've pasted 1, and 2, from my (now modified) earlier post into a module.
2. You have a worksheet called Database with names in column A, and Dates in Columns B and C.

Use this worksheet calculate event on your sheet.

Code:
Private Sub Worksheet_Calculate()
Dim c As Range, s As String
On Error Resume Next
For Each c In Cells.SpecialCells(xlCellTypeFormulas)
  Call MyCmt(c, "")
  s = c.Value
  s = Format(Application.WorksheetFunction.VLookup(c.Value, Worksheets("Database").Range("A2:C100"), 2, 0), "mm-dd-yy") 'Date 1
  s = s & " to "
  s = s & Format(Application.WorksheetFunction.VLookup(c.Value, Worksheets("Database").Range("A2:C100"), 3, 0), "mm-dd-yy") ' Date 2
  Call MyCmt(c, s)
Next c
End Sub
 
Upvote 0
PA HS Teacher (or anyone that can answer), two questions:

1) Is there a way to restrict your code so that it only puts comments in a range of cells (i.e. the range of cells that produce names)? Right now the code goes through the entire second sheet (the one with the worksheet calculate event code attached to it) and places comments in every cell that contains an equation. Many cells with equations on this sheet do not require comments. I would like it to only check and put names in the range of cells that I specify.

2) Is there a way to restrict this code so that it only puts comments in the cells that contain names that match those in the database? Right now the code places a comment in all the name cells even though some names should not be in the database. I would like it to not place a comment in any cell that does not have a matching name in the database.

Thanks for all the help.
 
Upvote 0
Try using this as your worksheet calculate event.

Code:
Private Sub Worksheet_Calculate()
Dim c As Range, RngDataBase As Range, RngNames As Range, RngDesired As Range, s As String
Set RngDesired = Range("B2:IV2")
Set RngDataBase = Worksheets("Database").Range("A2:C200")
Set RngNames = Worksheets("Database").Range("A2:A200")

On Error Resume Next
For Each c In RngDesired ' Cells.SpecialCells(xlCellTypeFormulas)
  s = ""
  If Len(c.Value) > 1 And Application.WorksheetFunction.CountIf(RngNames, c.Value) > 0 Then
   s = c.Value & ": "
   s = s & Format(Application.WorksheetFunction.VLookup(c.Value, RngDataBase, 2, 0), "mm-dd-yy") 'Date 1
   s = s & " to "
   s = s & Format(Application.WorksheetFunction.VLookup(c.Value, RngDataBase, 3, 0), "mm-dd-yy") ' Date 2
  End If
  Call MyCmt(c, s)
Next c
End Sub
 
Upvote 0
It’s working great. I am amazed at what Excel and VBA are capable of everyday (assuming you know how to use it). I have a lot to learn but I’m starting to get things figured out due entirely to the help I receive on this site. Thank you so much for the help.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top