Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Assigning Variable Text to a Comment Based Upon a Formula

    I am looking for ideas about a way to assign text to a comment that is assigned to a cell based upon certain conditions much the same way that I can utilize a VLOOKUP formula to a conditional format rule to determine a format of a cell.
    Overall, I am looking for ways to view numerous characters of text in a cell of an Excel spreadsheet that will change based upon a numerical value in the cell (or another cell) and will appear in its entirety on the page in a controlled way (like when you hover over the cell) and then disappear or minimize in a controlled way (like not hovering over the cell) to prevent the text from overcrowding the page when it is not needed. Any ideas or solutions will be greatly appreciated.

    Thank you

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Workingonit,

    The following code placed in the sheet module will add/change a comment in Cell F1 based on the numeric value in cell A1.

    In A1, enter a 1, 2, or delete the contents of the cell and see the effect it has on the comment in F1

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            With Range("F1")
            Select Case Target
                Case 1
                    .ClearComments
                    .AddComment
                    .Comment.Text Text:="The number 1 has been selected"
                Case 2
                    .ClearComments
                    .AddComment
                    .Comment.Text Text:="The number 2 has been selected"
                Case ""
                    .ClearComments
             End Select
            End With
        End If
    End Sub
    This can be expanded to any number of numeric Values and/or any number of cells.

    HTH,
    Maud

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Here is the code written as a function

    if you want to add a comment in D1 with the contents based on the value in A1 then place the following formula in Cell D1 =AddComment(A1)

    If A1=1 then the comment will be: The number 1 has been selected
    If A1=2 then the comment will be: The number 2 has been selected
    If A1 is anything else then the cell will have no comment.

    The function returns a blank to contents of the cell itself.

    Comment.png

    Code:
    Public Function AddComment(rng As Range)
        With Application.Caller
        Select Case rng
           Case 1
               .ClearComments
               .AddComment
               .Comment.Text Text:="The number 1 has been selected"
           Case 2
               .ClearComments
               .AddComment
               .Comment.Text Text:="The number 2 has been selected"
           Case Else
               .ClearComments
        End Select
        End With
        AddComment = ""
    End Function
    Last edited by Maudibe; 2015-02-27 at 07:23.

  4. #4
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you Maudibe. The code for your first thread (#2) worked great. I am unable to get anything besides"#NAME?" back from the code in the second thread (#3).
    Is there a way that the code can be written to perform the same way with the added feature that the text in the comment can be referenced from text in yet another cell? For example: The comment in Sheet 1, cell F1 will contain the text entered in Sheet 2, cell F2.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Workingonit,

    That can easily be done by replacing the comment text in the code with a cell reference to Worksheets("Sheet2").Range("F1").Value and ..."F2).value

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            With Range("F1")
            Select Case Target
                Case 1
                    .ClearComments
                    .AddComment
                    .Comment.Text Text:=Worksheets("Sheet2").Range("F1").Value
                Case 2
                    .ClearComments
                    .AddComment
                    .Comment.Text Text:=Worksheets("Sheet2").Range("F2").Value
                Case ""
                    .ClearComments
             End Select
            End With
        End If
    End Sub

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Workingonit,

    Here is the same code as a function instead that will take the comments from another sheet based on the value of another cell..

    Code:
    Public Function AddComment(rng As Range)
        With Application.Caller
        Select Case rng
           Case 1
               .ClearComments
               .AddComment
               .Comment.Text Text:=Worksheets("Sheet2").Range("F1").Value
           Case 2
               .ClearComments
               .AddComment
               .Comment.Text Text:=Worksheets("Sheet2").Range("F2").Value
           Case Else
               .ClearComments
        End Select
        End With
        AddComment = ""
    End Function
    HTH,
    Maud
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •