Jerry said:
JohnRogers said:
when I print the comments in cells, the the location and the comment
is printed. The location prints as (Cell: S56)
Is thee a way to use some other designation for that cell -
the name of the cell, for instance, the name of a column, etc.
I asked that question earlier. Doesn't seem to be any way to change the
way Excel formats comments. It reads them from the sheet left to right,
top to bottom. No sorting. And prints as:
Cell: [Cell location]
Comment: [Comment]
You can extract the comments from the sheet, then format and print as
you wish. Details would depend on what you wanted to do.
I see from above post you ask how to do that. I'm gathereing comments
from a census spreadsheat application to a page for printing. The
routines use a combination of a macro and a vba function to populate the
page and print it. I'm including copies of the routines -- perhaps it
will help [the routines print with no cell identification, and no
'Comment:' preface, 80 to a page in two columns, sorted by name(since
the comments are created in a standard format):
The macro:
Sub Print_Notes()
'
' Print_Comment Macro
' Macro recorded 6/26/2003 by Jerry Park
'
'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
' Gather the notes to the note page and sort them.
Call Create_Note_Page
' Print Notes and restore print area.
ActiveWindow.ScrollRow = 190
ActiveWindow.SmallScroll Down:=47
Range("A240:AQ279").Select
ActiveSheet.PageSetup.PrintArea = "$A$240:$AQ$279"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$238:$AQ$279"
With ActiveSheet.PageSetup
.LeftHeader = "Dyer Nursing Home, Inc." & Chr(10) & "Census &A"
& Chr(10) & ""
.CenterHeader = "Printed: &D"
.RightHeader = "Page &P of &N"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.CalculateFull
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.ScrollRow = 3
Range("A3:AQ237").Select
Range("AQ3").Activate
ActiveSheet.PageSetup.PrintArea = "$A$3:$AQ$237"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$3:$AQ$237"
With ActiveSheet.PageSetup
.LeftHeader = "Dyer Nursing Home, Inc." & Chr(10) & "Census &A"
& Chr(10) & ""
.CenterHeader = "Printed: &D"
.RightHeader = "Page &P of &N"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
' Clean up.
Call Clear_Note_Page
ActiveWindow.ScrollRow = 3
Range("A3").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
The vba function:
Option Explicit
Sub Create_Note_Page()
Dim Current_Cell As Range
Dim Current_Area As Range
Dim Save_Row As Integer
Dim Warning_Message As String
Dim Title_Message As String
Dim Dummy_Response As String
Save_Row = 240
Application.EnableEvents = False
ActiveSheet.Unprotect
' Clear note sheet.
Set Current_Area = Range("A238", "A319")
Current_Area.Select
Current_Area.Value = ""
' Set Header
Cells(238, 1) = "Census Notes"
'Trap error if no cells contain comments.
On Error Resume Next
Set Current_Area = Range("L3", "AP234").SpecialCells(xlCellTypeComments)
'Restore error checking.
On Error GoTo 0
For Each Current_Cell In Current_Area
If (Len(Current_Cell.NoteText) > 0) Then
If (Save_Row < 320) Then
Cells(Save_Row, 1) = Current_Cell.NoteText
Save_Row = Save_Row + 1
Else
Warning_Message = "Notes exceed 80. Use Page Setup (Sheet
Tab) to print all notes."
Title_Message = "Warning. Too many notes to print."
Dummy_Response = MsgBox(Warning_Message, vbOKOnly,
Title_Message)
Exit For
End If
End If
Next
' Sort notes.
Set Current_Area = Range("A240", "A319")
Current_Area.Select
Selection.Sort Key1:=Range("A240"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect
Application.EnableEvents = True
End Sub
