Inserting Comments - 2nd post



I have the following code that displays a comment when a cell is selected.
It also deletes the comment when another cell is selected (so that only the
selected cell has a comment showing). It works fine except that when a user
chooses an item from a drop-down list, all of the comments show. Is there a
way to revise my code so that comments don't show when a drop-down list item
is selected? Thank you much.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Delete comments
Dim sh As Worksheet, cmt As Comment
For Each sh In ActiveWorkbook.Worksheets
For Each cmt In sh.Comments

'Add Comment
Dim myCell As Range

Dim myRng As Range
Set myRng = Me.Range("d2:d25")
If Intersect(Target, myRng) Is Nothing Then GoTo ProductFamily
For Each myCell In Intersect(Target, myRng).Cells
With myCell
.ClearComments 'remove old comment if there is one
.AddComment.Text Text:="Please enter a brief, yet descriptive title for
the voice entry."
.Comment.Visible = True
.Comment.Shape.TextFrame.AutoSize = True
End With
Next myCell

........ additional cell comments


On Error Resume Next
c = Range("A1").Validation.Formula1
If Err.Number <> 0 Then
'The is no drop down list
On Error GoTo 0
'there is a drop down list
End If


Thanks for the response Joel. I'm not sure where I'm supposed to add your
code. I added it to the bottom of mine and I got an error at c = Range . . .
If it makes a difference, my Excel file is liked to a SharePoint site which
is creating the drop-down lists.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question