CODEWITHSUNDEEP
excelvba
Mark
Mark

Reputation: 1

Confusing VBA comment insertion behavior in Excel

I'm going to rewrite this request. I see that I could be clearer.

From within the spreadsheet I highlight a cell where I want a standardized comment inserted. I use a keyboard shortcut to activate AddServiceNote and the code inserts a comment with standardized formatting and text.

Everything works, but it is not repeatable. I can't select another cell and add another comment using the same keyboard shortcut.

I can use another keyboard shortcut to activate FormatNotes where all the comments are formatted. Mysteriously, that reactivates my ability to insert another single standardized comment with AddServiceNote.

Am I missing something obvious? Why can't I insert comments over and over? Does this have to do with Set Note = ActiveCell.Comment?

Public Note As Comment

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

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

Public Sub OrganizeElements()
     Note.Shape.TextFrame.AutoSize = True
     'and a long list of other attributes
End Sub

Upvotes: 0

Views: 65

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

Whats happening is this:

  • You have a Module Scope variable, Note. Its value persists.
  • The first time you run AddServiceNote, Note Is Nothing is TRUE, so the If Then code runs
  • In that If code, Note is Set to something
  • The next time you run AddServiceNote, Note Is Nothing is FALSE, so the If Then code does not run
  • Running FormatNotes executes a For loop, setting Note on each iteration
  • After the last iteration of the For loop, Note is left as Nothing (that's a side effect of the For)
  • So, having run FormatNotes, AddServiceNote will work again (once)

The fix is simple

  • move the Dim Note As ... inside AddServiceNote (there is nothing about the code posted that required Note to be Module Scoped)
  • Change OrganizeElements's signature to
    Public Sub OrganizeElements(Note As Comment)
  • Change the lines taht call OrganizeElements to pass Note as a parameter:
    OrganizeElements Note
Public Sub AddServiceNote()
    Dim Note As Comment

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

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

Public Sub OrganizeElements(Note As Comment)
     Note.Shape.TextFrame.AutoSize = True
     'and a long list of other attributes
End Sub

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6388

Properly indenting the code makes it more obvious that the entirety of it in Sub CommentAddLabor only runs if there is no comment in the selected cell.

You want:

Public Sub CommentAddLabor()
    If Ct Is Nothing Then
        ActiveCell.AddComment
    End If
    Set Ct = ActiveCell.Comment
    CommentFormat 'calls the sub above
    Ct.Text "Function: " & Chr(10) & "Envision: " & Chr(10) & "Activity: " & Chr(10) & "Material: " & Chr(10) & "Duration: " & Chr(10) & "Consider: "
End Sub

Upvotes: 0

Related Questions