Auto Comments

[justDave]

Hi all, is there any way of having the contents of a cell automatically pasted as a Comment in a cell on a different worksheet?

For example, I have a Details tab in which the user will be typing details of projects. The details from here will then be pulled through to a Gantt chart style of planner - the Details tab has a Remarks/Comments box and this is the data which I would like pasted as a comment.

Is this do-able? What my boss actually wants is a HTML style hover box when the mouse is hovered over the cell but I assume this isn't possible?

Many thanks in advance,

Dave

 

[Jerry Sullivan]

Hi Dave,

You could try the code below. It will take values that you enter into Cell(s) on Column A of your Details sheet and make them Comments on the corresponding Cell(s) in Sheet1. You can modify the names in Blue font to match your sheet names and ranges.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run (In your case "Details")
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim c As Range
    On Error GoTo ErrorHandler
 
    With Sheets("Sheet1")
        For Each c In Intersect(Target, Range("A:A"))
            With .Range(c.Address)
                If .Comment Is Nothing Then .AddComment
                If c.Value = vbNullString Then
                    .Comment.Delete
                Else
                    .Comment.Visible = False
                    .Comment.Text Text:=c.Value
                End If
            End With
        Next c
    End With
ErrorHandler:
    Application.EnableEvents = True
    Set c = Nothing
End Sub

 

[justDave]

Many thanks Jerry, that is great.

I know it's possibly pushing things too far but is it possible to change the target range to something more 'dynamic'? (I'm not sure if that is the correct word).

At the moment, the user will typing a date and some remarks into the detail tab and, using Conditional Formatting, these date will be highlighted in a calendar tab - what I would love to do, in an ideal world, is paste the comments into the highlighted cells.

Is this achievable?

Many thanks once again for your help so far.

Regards,

Dave

 

[Jerry Sullivan]

Hi Dave,

No that isn't pushing things too far.

...is it possible to change the target range to something more 'dynamic'?
...snip..
the user will typing a date and some remarks into the detail tab and, using Conditional Formatting, these date will be highlighted in a calendar tab - what I would love to do, in an ideal world, is paste the comments into the highlighted cells.

It shouldn't be too hard to change the relationship between the source and the target for the comment.

Please provide some information about what ranges on your detail tab are used for dates and remarks; and what sheetname and ranges are used in a calendar tab to identify the cell representing the matching date.

You are also welcome to post your workbook to a host site, or exchange email addresses through a Private Message if that makes it easier than trying to describe.

 

[justDave]

Thanks again Jerry, I have sent you a PM re: e-mail addresses.

 

[Jerry Sullivan]

Dave,

Glad we were able to come up with a solution that worked for you.

For other Board users who might benefit from this, here is a brief description of the approach and code.

The goal was to enter task information (Start date, End date, Description) and have the description added as a Cell Comment to each cell on that row that falls within the Start-Finish date range.

The date format shown is DD-MM-YYYY however the code should work with other date formats. The color "Gantt Chart" bars are generated by Conditional Formatting rules you already had in place.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M
2	Start	End	Description	Owner	01/01/2011	08/01/2011	15/01/2011	22/01/2011	29/01/2011	05/02/2011	12/02/2011
3	01/01/2011	01/07/2011	Project 1
4	01/01/2011	01/02/2011	Sub Section 1
5	15/01/2011	01/03/2011	Sub Section 2
Calendar

Excel 2007



Paste this code in the Calendar worksheet code module:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:E")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim c As Range, rngDates As Range
    Dim varStart As Variant, varFinish As Variant
    Dim varDates As Variant
    Dim strComment As String
    Dim lngRow As Long, lngCol As Long
    Dim lngElement As Long, lngRowsDone() As Long
 
    On Error GoTo ErrorHandler
    ReDim lngRowsDone(1)
    With ActiveSheet
        Set rngDates = .Range("G2:BH2")
        varDates = rngDates
 
        For Each c In Intersect(Target, Range("C:E"))
            If Not isMember(c.Row, lngRowsDone) Then
            '---update comments this task row
                varStart = .Cells(c.Row, 3)
                varFinish = .Cells(c.Row, 4)
                If Not IsDate(varStart) Or Not IsDate(varFinish) Then
                    varStart = #1/1/1980#
                    varFinish = #1/1/1980#
                End If
                strComment = .Cells(c.Row, 5)
                For lngElement = 1 To UBound(varDates, 2)
                    With .Cells(c.Row, lngElement + 6)
                        If varStart<= varDates(1, lngElement) And _
                            varFinish >= varDates(1, lngElement) Then
                            '-add comment
                            If .Comment Is Nothing Then .AddComment
                            If strComment = vbNullString Then
                                .Comment.Delete
                            Else
                                .Comment.Visible = False
                                .Comment.Text Text:=strComment
                            End If
                        Else
                        '---delete comment
                            If Not .Comment Is Nothing Then
                                .Comment.Delete
                            End If
                        End If
                    End With
                Next lngElement
                lngRowsDone(UBound(lngRowsDone)) = c.Row
                ReDim Preserve lngRowsDone(UBound(lngRowsDone) + 1)
            End If
        Next c
    End With
ErrorHandler:
    Application.EnableEvents = True
    Set c = Nothing
    Set rngDates = Nothing
    Erase lngRowsDone()
End Sub

Paste this code in a standard code module:

Option Explicit
Public Function isMember(lngRow As Long, ByRef lngArr() As Long) As Boolean
    Dim i As Long
    On Error Resume Next
    If IsError(LBound(lngArr)) Then
        isMember = False
        Exit Function
    End If
    For i = LBound(lngArr) To UBound(lngArr)
        If lngArr(i) = lngRow Then
            isMember = True
            Exit Function
        End If
    Next i
    isMember = False
End Function