0

I had help from the community to figure out some of this code. I continue to get some weird behavior, though.

I only have three functions for notes: add note with formatting, format a note, and format all notes. AddServiceNote and FormatNote work. However, I'm getting two different errors when I try to run FormatNotes. For awhile the the code stopped running and I got an error message (91, I think). I made some test changes and then changed it back to the original code but now the code is doing something different (maybe I didn't actually return the code to it's original). It does format all notes but positions them according to a single cell rather than to the parent cell of each comment.

Public Note As Comment

Public Sub AddServiceNote()
 If ActiveCell.Comment Is Nothing Then
  Set Note = ActiveCell.AddComment
  Note.Text "Function: "
  OrganizeElements Note
 End If
End Sub

Public Sub FormatNote()
 If Not ActiveCell.Comment Is Nothing Then
  Set Note = ActiveCell.Comment
  OrganizeElements Note
 End If
End Sub

Public Sub FormatNotes()
 For Each Note In ActiveSheet.Comments
  OrganizeElements Note
 Next
End Sub

Public Sub OrganizeElements(ByVal Note As Comment)
 Note.Shape.Top = ActiveCell.Comment.Parent.Top - 10
 Note.Shape.Left = ActiveCell.Comment.Parent.Offset(0, 1).Left + 10
 Note.Shape.Placement = 3
 'And a continuing list of formats that work 
End Sub

In OrganizeElements I have about 30 lines of code that work. They all work when I run AddServiceNote and FormatNote. When I run FormatNotes the the first two lines don't work:

 Note.Shape.Top = ActiveCell.Comment.Parent.Top - 10
 Note.Shape.Left = ActiveCell.Comment.Parent.Offset(0, 1).Left + 10

Those lines should position the comment based on cell it's connected to. I think ActiveCell might be the issue. None of the other formatting lines of code has ActiveCell and seems to be functioning with each note. The code works when I'm working with one note but when I try to format all the notes ActiveCell seems to refer to the actual highlighted cell on the spreadsheet and doesn't change.

How do I get code to format each comment based on it's parent cell?

Mark
  • 1
  • 1

0 Answers0