Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comments
Hey everyone. I just signed up to have this one issue solved. I have a large
spreadsheet that includes many comments. Occasionally the comments move to several cells (up to hundreds of cells) below the cell they should be close to or resize so they are not visible. I have researched how to reformat the comment boxes using this macro (see below) that I found on the internet and it works fine except when I go to edit the comments they continue to be located in random places and the incorrect size. Any help is greatly appreciated. Option Explicit Dim cCell As Range Dim sComment As Comment Dim allComments As Range Sub reset_comments() On Error Resume Next Application.ScreenUpdating = False Set allComments = Range("A1").SpecialCells(xlCellTypeComments) If allComments Is Nothing Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Ex For Each cCell In allComments With cCell.Comment ..Shape.Height = 200 ..Shape.Width = 125 End With 'cCell.Select not necessary ? cCell.Comment.Visible = True cCell.Comment.Shape.Select True With Selection ' .Interior.ColorIndex = 19 ..Font.Bold = False ..Font.Size = 10 End With cCell.Comment.Visible = False Next cCell Ex: Set allComments = Nothing Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comments
You might try this.
Sub RestoreComments() Dim AllCommentCells As Range Dim Cell As Range Dim LastCol As Integer LastCol = ActiveSheet.Columns.Count Set AllCommentCells = Cells.SpecialCells(xlCellTypeComments) For Each Cell In AllCommentCells With Cell.Comment.Shape .Width = 96 .Height = 55.5 .Top = Cell.Top + 5 If Cell.Column < (LastCol - 3) Then .Left = Cell.Offset(0, 1).Left + 10 Else .Left = Cell.Offset(0, (LastCol - Cell.Column) - 3).Left End If End With Next End Sub -- Jim "Jon" wrote in message ... | Hey everyone. I just signed up to have this one issue solved. I have a large | spreadsheet that includes many comments. Occasionally the comments move to | several cells (up to hundreds of cells) below the cell they should be close | to or resize so they are not visible. I have researched how to reformat the | comment boxes using this macro (see below) that I found on the internet and | it works fine except when I go to edit the comments they continue to be | located in random places and the incorrect size. Any help is greatly | appreciated. | | Option Explicit | | Dim cCell As Range | | Dim sComment As Comment | | Dim allComments As Range | | | | Sub reset_comments() | | On Error Resume Next | | Application.ScreenUpdating = False | | Set allComments = Range("A1").SpecialCells(xlCellTypeComments) | | If allComments Is Nothing Then MsgBox "No comments in " & ActiveSheet.Name: | GoTo Ex | | For Each cCell In allComments | | With cCell.Comment | | .Shape.Height = 200 | | .Shape.Width = 125 | | End With | | 'cCell.Select not necessary ? | | cCell.Comment.Visible = True | | cCell.Comment.Shape.Select True | | With Selection | | ' .Interior.ColorIndex = 19 | | .Font.Bold = False | | .Font.Size = 10 | | End With | | cCell.Comment.Visible = False | | Next cCell | | Ex: | | Set allComments = Nothing | | Application.ScreenUpdating = True | | End Sub | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comments
Thanks Jim! You have saved me a lot of grief trying to figure that out.
Cheers, Jon "Jim Rech" wrote: You might try this. Sub RestoreComments() Dim AllCommentCells As Range Dim Cell As Range Dim LastCol As Integer LastCol = ActiveSheet.Columns.Count Set AllCommentCells = Cells.SpecialCells(xlCellTypeComments) For Each Cell In AllCommentCells With Cell.Comment.Shape .Width = 96 .Height = 55.5 .Top = Cell.Top + 5 If Cell.Column < (LastCol - 3) Then .Left = Cell.Offset(0, 1).Left + 10 Else .Left = Cell.Offset(0, (LastCol - Cell.Column) - 3).Left End If End With Next End Sub -- Jim "Jon" wrote in message ... | Hey everyone. I just signed up to have this one issue solved. I have a large | spreadsheet that includes many comments. Occasionally the comments move to | several cells (up to hundreds of cells) below the cell they should be close | to or resize so they are not visible. I have researched how to reformat the | comment boxes using this macro (see below) that I found on the internet and | it works fine except when I go to edit the comments they continue to be | located in random places and the incorrect size. Any help is greatly | appreciated. | | Option Explicit | | Dim cCell As Range | | Dim sComment As Comment | | Dim allComments As Range | | | | Sub reset_comments() | | On Error Resume Next | | Application.ScreenUpdating = False | | Set allComments = Range("A1").SpecialCells(xlCellTypeComments) | | If allComments Is Nothing Then MsgBox "No comments in " & ActiveSheet.Name: | GoTo Ex | | For Each cCell In allComments | | With cCell.Comment | | .Shape.Height = 200 | | .Shape.Width = 125 | | End With | | 'cCell.Select not necessary ? | | cCell.Comment.Visible = True | | cCell.Comment.Shape.Select True | | With Selection | | ' .Interior.ColorIndex = 19 | | .Font.Bold = False | | .Font.Size = 10 | | End With | | cCell.Comment.Visible = False | | Next cCell | | Ex: | | Set allComments = Nothing | | Application.ScreenUpdating = True | | End Sub | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comments | Excel Discussion (Misc queries) | |||
Comments | Excel Discussion (Misc queries) | |||
Comments | Excel Discussion (Misc queries) | |||
in excel useing comments how do you add clip art to comments? | New Users to Excel | |||
comments | Excel Discussion (Misc queries) |