VBA question for .AddComment in With statement

[craigexcel]

Hoping you can help troubleshoot what part of my code is causing it to hiccup. This procedure will be called from another, and is intended to make identical changes (i.e. add several Comment boxes) on various sheets within each workbook in the user-selected network folder.

It has run successfully (@ 1st pass) on a test collection of .xlsm workbooks (each of which has both an "Open" and "BeforeSave" macro). However, variously, running the code on subsequent instances causes it to fail at the line in red.

The calling macro requests the network folder via "Application.FileDialog(msoFileDialogFolderPicker)", and targets only .xlsm workbooks. Each of the target workbooks has an "Open" and "BeforeSave" macro, which also removes password protection (workbook and worksheets) only for defined users.

I thought the "ws.Range.." syntax was correct. Is that not true? Or is it possible that's not even the true source of the error that Excel is encountering, and it's really something else? Thoughts?

Dim ws      As Worksheet
For Each ws In Worksheets
    If Len(ws.Name) = 5 Then
        If ws.Name Like "[A-Z][A-Z][A-Z]##" Then    'if sheet name matches the pattern [AAA##], i.e 3 UPPER CASE alpha, followed by 2 numeric
            Range("F1").ClearComments   'clear any existing Comment in cell
            [COLOR=#ff0000]With ws.Range("F1").AddComment      [/COLOR]'add new Comment
                .Visible = False
                .text Date & ": formula explanation: sadfkms slkdfm salkglsa kj glsakf gsalfkgsa lfkgjsl as dfkgjslakj gas;fkgj"
            End With
            With ws.Range("F1").Comment.Shape   'format Comment box size
                Dim lArea   As Long
                lArea = .Width * .Height
                .Width = 120
                .Height = (lArea / .Width)
                .TextFrame.AutoMargins = False
                .TextFrame.MarginBottom = 1
                .TextFrame.MarginTop = 1
                .TextFrame.MarginLeft = 1
                .TextFrame.MarginRight = 1
            End With
        Else
        End If
    Else
    End If
Next ws

 

[John_w]

The red With... line will only work if there isn't a comment in F1, hence the code fails the 2nd time it runs against the same file. Try this instead:

    With ws.Range("F1")
        If .Comment Is Nothing Then
            .AddComment Date & ": formula explanation: xxxx"
            .Comment.Visible = False
        Else
            .Comment.Text Date & ": 2 formula explanation: xxxx"
        End If
    End With

 

[craigexcel]

Thanks for your reply & suggestion John_w. That makes sense.
I was assuming that "Range("F1").ClearComments" line would have enabled adding the Comment to the cell, since at that point, it would no longer have a Comment. Not true? Am I missing somthing in that logic? I ultimately want to write a new piece of text to the Comment, 'adding' if none exists, or, overwriting if one does exist.

 

[John_w]

I didn't notice the ClearComments line. Yes, you're correct, however the Range reference doesn't have a sheet qualifier so it always operates on the active sheet. Try this change:

ws.Range("F1").ClearComments