add comment to several WS's (2003 SP3)

[steveh]

Morning all

I have a WB with about 270 WS's, on 265 of these sheets I would like to put the same comment in the same cell (D24), if I select all of these sheets the 'add comment' function is disabled, is there any way to do this rather than going through each one individually?

[HansV]

You could modify this macro:

Code: Select all

Sub AddComment()
  Dim wsh As Worksheet
  Dim strText As String
  strText = "This is a comment"
  For Each wsh In ActiveWorkbook.Worksheets
    Select Case wsh.Name
      Case "Intro", "Summary", "Overview"
        ' Skip these sheets
      Case Else
        wsh.Range("D24").AddComment Text:=strText
    End Select
  Next wsh
End Sub

Make sure that only a single sheet is selected when you run this macro.

[steveh]

You could modify this macro:

Thanks Hans

That is great.

A tip though for any one using this, if you are stupid like me and run a test first using Han's test message 'This is a comment' a subsequent attempt will produce an error 1004 as it cannot overwrite the comment as is, therefore make sure that your comment is inserted when you run it, believe me it will work and does not need testing!!

[HansV]

Here is a version that will delete existing comments before inserting new ones.

Code: Select all

Sub AddComment()
  Dim wsh As Worksheet
  Dim strText As String
  strText = "This is a comment"
  For Each wsh In ActiveWorkbook.Worksheets
    Select Case wsh.Name
      Case "Intro", "Summary", "Overview"
        ' Skip these sheets
      Case Else
        On Error Resume Next
        wsh.Range("D24").Comment.Delete
        On Error GoTo 0
        wsh.Range("D24").AddComment Text:=strText
    End Select
  Next wsh
End Sub

[steveh]

Cheers Hans

That is going straight into my tips workbook, Thanks