Comments Question

[Brian]

I have a spreadsheet with comments in cells. When we print it out we print
it out so that comments print out at the end. The only problem with this is
that on the comments it says reference C3. I would like it to list a
reference of what is has as a column and row header
example
A B C D E
1 Monday Tuesday Wednesday Thursday
2 Server 1
3 Server 2

currently if I put a comment in c1 and print out the sheet it will show
reference:C2 when I would like it to say
reference: Tuesday Server 1

Is it possible to do this.

Thanks in advance.

 

[Tom Ogilvy]

Not with any built in setting. If you want to write a macro, you could list
the comments at the bottom of the sheet and not use the built in option.

 

[Bob Phillips]

Brian,

Don't know how to do what you request, but this will add the column header
as text in any new comment

Sub EnhancedComment()
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment _
Text:=Cells(1, ActiveCell.Column).Text & vbCrLf
Set cmt = ActiveCell.Comment
With cmt.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 11
.Bold = False
.ColorIndex = 0
End With

End If
SendKeys "%ie~"
End Sub

I would change the 'Insedrt Comment' option on the right-click menu to
automatically call this.

Sub addEnhancedComment()
Dim oCtl As Office.CommandBarControl
Dim iPos As Long
With Application.CommandBars("cell")
On Error Resume Next
.Controls("Insert Comment+").Delete
On Error GoTo 0
Set oCtl = Application.CommandBars.FindControl(ID:=2031)
iPos = oCtl.Index
.Controls("Insert Comment").Visible = False
Set oCtl = .Controls.Add(before:=iPos)
With oCtl
.BeginGroup = True
.Caption = "Insert Comment+"
.FaceId = 2031
.OnAction = "'" & ThisWorkbook.Name & "'!EnhancedComment"
End With
End With
End Sub

Two problems here, existing comments still don't have that column header,
and it won't reflect changes to the coilumn header. Second one would need
worksheet change event code to sort out, but the following once-off code
will add thes column headings to existing comments.

Sub AmendComments()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.Comment Is Nothing Then
cell.Comment.Text Text:=Cells(1, cell.Column).Text & vbCrLf & _
cell.Comment.Text
End If
Next cell

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Brian]

Thanks a bunch, this worked great for me.