Conditional Comments

[jbrousseau]

Hello,

I am trying to insert a comment on data-validated cells in a column through VBA. There are multiple possible values in the range and each has their own conditional comment.

Eg,, if cell value = X, insert comment X. If cell value = Y, insert comment Y.

I've been searching the boards and hacking together bits and pieces of other users who have requested similar assistance, but it's not quite there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address <> Range("F3:F300") Then Exit Sub
        If Target = "Value X" Then
            Target.ClearComments
            Target.AddComment.Text Text:="This cell contains Value X"
        If Target = "Value Y" Then
            Target.ClearComments
        Target.AddComment.Text Text:="This cell contains Value Y"
    End If
End Sub

Any help would be much appreciated!

Many thanks,
jb

 

[Jerry Sullivan]

Hi jb,

You could try the code below.

It allows for the possibility that more than one cell is changed at the same time.

One important thing to understand about Event code is that you need to
temporarily set Application.EnableEvents = False
if there is a chance your code will inadvertently trigger other events.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F3:F300")) Is Nothing Then Exit Sub
    Dim c As Range
    Dim sNewComment As String
    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    For Each c In Intersect(Target, Range("F3:F300"))
        Debug.Print c.Value
        Select Case c.Value
            Case "Value X"
                sNewComment = "This cell contains Value X"
            Case "Value Y"
                sNewComment = "This cell contains Value Y"
            Case Else
                'do nothing
        End Select
        
        If sNewComment <> vbNullString Then
            c.ClearComments
            c.AddComment.Text Text:=sNewComment
            sNewComment = vbNullString
        End If
    Next c
CleanUp:
    Application.EnableEvents = True
End Sub

 

[jbrousseau]

[SOLVED] Conditional Comments

Awesome, it worked! Thank you, JS411. You are a gentleman and a scholar, good sir.

Thanks again!

 

[Jerry Sullivan]

Re: [SOLVED] Conditional Comments

Awesome, it worked! Thank you, JS411. You are a gentleman and a scholar, good sir.

Thanks again!

Glad to have helped.
BTW you can delete the Debug.Print line. I used while testing and forgot to remove before posting.

 

[jbrousseau]

Hrm. How might I be able to clear a comment if the value in the cell is deleted?

Cheers!

 

[Jerry Sullivan]

You can use a second statement with .ClearComments

With rng
    .ClearContents
    .ClearFormats
End With

Unfortunately there isn't a way to combine those into one statement. (As far as I know in xl2007).

 

[Jerry Sullivan]

Just noticed I completely misread your question.

Could you clarify what you mean by "to clear a comment if the value in the cell is deleted". The same .ClearComments Method should work whether the cell has a value or not.

 

[jbrousseau]

Just noticed I completely misread your question.

Could you clarify what you mean by "to clear a comment if the value in the cell is deleted". The same .ClearComments Method should work whether the cell has a value or not.

If a cell within the range of the VBA code is change, a comment is inserted (as per the code you provided). If I then delete the contents of that cell, the comment remains.

Was hoping to clear the comment in the above case.

 

[Jerry Sullivan]

Thanks for the clarification.

You can try this...

        Select Case c.Value
            Case "Value X"
                sNewComment = "This cell contains Value X"
            Case "Value Y"
                sNewComment = "This cell contains Value Y"
            Case vbNullString
                c.ClearComments
            Case Else
                'do nothing
        End Select

This will only clear comments if the user deletes or clears the value from the cell. If they replace the value with a space, the comment will remain.

 

[jbrousseau]

Worked!

Thanks again