Inserting New Comments

[centaur63]

Where in this code does it tell it to populate a new worksheet. How do I set it to equal a worksheet. I have a worksheet named test. I want the worksheet to equal this instead of creating a new one. I am having problems with that.

Sub PrintCommentsByColumn()
  
  Dim cell As Range
  Dim myrange As Range, myrangeC As Range
  Dim col As Long
  Dim RowOS As Long
  Dim wsSource As Worksheet
  Dim wsNew As Worksheet
  If ActiveSheet.Comments.Count = 0 Then
    MsgBox "No comments in entire sheet"
    Exit Sub
  End If
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Set wsSource = ActiveSheet
  Sheets.Add
  Set wsNew = ActiveSheet
  wsSource.Activate
  With wsNew.Columns("A:D")
      .VerticalAlignment = xlTop
      .WrapText = True
  End With
  wsNew.Columns("B").ColumnWidth = 15
  wsNew.Columns("C").ColumnWidth = 15
  wsNew.Columns("D").ColumnWidth = 60
  wsNew.PageSetup.PrintGridlines = True
  RowOS = 2
  wsNew.Cells(1, 4) = "'" & Application.ActiveWorkbook.FullName & " -- " & _
      Application.ActiveSheet.Name
  For col = 1 To ActiveSheet.UsedRange.Columns.Count
     Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _
         Cells.SpecialCells(xlCellTypeComments))
     If myrangeC Is Nothing Then GoTo nxtCol
     For Each cell In myrangeC
        If Trim(cell.Comment.Text) <> "" Then
           RowOS = RowOS + 1
           wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":"
           wsNew.Cells(RowOS, 2) = "'" & Date & "     " & Time
           wsNew.Cells(RowOS, 3) = "'" & cell.Text
           wsNew.Cells(RowOS, 4) = "'" & cell.Comment.Text
        End If
     Next cell
nxtCol:
  Next col
  wsNew.Activate
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

If you need anymore information please let me know.

 

[Richard Schollar]

Try this:

Sub PrintCommentsByColumn()
 
  Dim cell As Range
  Dim myrange As Range, myrangeC As Range
  Dim col As Long
  Dim RowOS As Long
  Dim wsSource As Worksheet
  Dim wsNew As Worksheet
  If ActiveSheet.Comments.Count = 0 Then
    MsgBox "No comments in entire sheet"
    Exit Sub
  End If
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Set wsSource = ActiveSheet
  Set wsNew = Sheets("Test")
  wsSource.Activate
  With wsNew.Columns("A:D")
      .VerticalAlignment = xlTop
      .WrapText = True
  End With
  wsNew.Columns("B").ColumnWidth = 15
  wsNew.Columns("C").ColumnWidth = 15
  wsNew.Columns("D").ColumnWidth = 60
  wsNew.PageSetup.PrintGridlines = True
  RowOS = 2
  wsNew.Cells(1, 4) = "'" & Application.ActiveWorkbook.FullName & " -- " & _
      Application.ActiveSheet.Name
  For col = 1 To ActiveSheet.UsedRange.Columns.Count
     Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _
         Cells.SpecialCells(xlCellTypeComments))
     If myrangeC Is Nothing Then GoTo nxtCol
     For Each cell In myrangeC
        If Trim(cell.Comment.Text) <> "" Then
           RowOS = RowOS + 1
           wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":"
           wsNew.Cells(RowOS, 2) = "'" & Date & "     " & Time
           wsNew.Cells(RowOS, 3) = "'" & cell.Text
           wsNew.Cells(RowOS, 4) = "'" & cell.Comment.Text
        End If
     Next cell
nxtCol:
  Next col
  wsNew.Activate
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

It was the Sheets.Add line that added a new sheet.

The code above assumes you have a sheet named "Test", otherwise the code will error out.

Richard

 

[centaur63]

Thanks Alot

Yeah that worked. Thanks Alot

 

[centaur63]

Second Part

How do i get the above code to insert the new comments to the sheets. Right now if a cell is changed it over rights the previouscomments inserted into that cell. How can i get it to insert a new line instead of overrighting the privous entry.

 

[Richard Schollar]

Give this a go:

Sub PrintCommentsByColumn()
 
  Dim cell As Range
  Dim myrange As Range, myrangeC As Range
  Dim col As Long
  Dim RowOS As Long
  Dim wsSource As Worksheet
  Dim wsNew As Worksheet
  If ActiveSheet.Comments.Count = 0 Then
    MsgBox "No comments in entire sheet"
    Exit Sub
  End If
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Set wsSource = ActiveSheet
  Set wsNew = Sheets("Test")
  wsSource.Activate
  With wsNew.Columns("A:D")
      .VerticalAlignment = xlTop
      .WrapText = True
  End With
  wsNew.Columns("B").ColumnWidth = 15
  wsNew.Columns("C").ColumnWidth = 15
  wsNew.Columns("D").ColumnWidth = 60
  wsNew.PageSetup.PrintGridlines = True
  RowOS = wsNew.Range("A65536").End(xlUp).Row
  wsNew.Cells(1, 4) = "'" & Application.ActiveWorkbook.FullName & " -- " & _
      Application.ActiveSheet.Name
  For col = 1 To ActiveSheet.UsedRange.Columns.Count
     Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _
         Cells.SpecialCells(xlCellTypeComments))
     If myrangeC Is Nothing Then GoTo nxtCol
     For Each cell In myrangeC
        If Trim(cell.Comment.Text) <> "" Then
           RowOS = RowOS + 1
           wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":"
           wsNew.Cells(RowOS, 2) = "'" & Date & "     " & Time
           wsNew.Cells(RowOS, 3) = "'" & cell.Text
           wsNew.Cells(RowOS, 4) = "'" & cell.Comment.Text
        End If
     Next cell
nxtCol:
  Next col
  wsNew.Activate
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Richard

 

[centaur63]

Cool

Yeah that seemed to work. I appreciate your help. I just am wondering one more thing. Instead of it just inserting every comment again. Is there anyway to get it to only insert the ones that has changed???? Since it was last ran or is that not possible.

 

[centaur63]

Also

If I cant do what i asked for above is there a way for me to sort by cell???