Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comments
JohnRogers wrote:
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comments
Jerry Park wrote:
JohnRogers wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2000 how to format the comments font all 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) | |||
comments | Excel Discussion (Misc queries) | |||
Comments | Excel Discussion (Misc queries) |