https://bugs.freedesktop.org/show_bug.cgi?id=74101
Priority: medium
Bug ID: 74101
Assignee: libreoffice-bugs@lists.freedesktop.org
Summary: add NOPRINTROW and NOPRINTCOL functions
Severity: enhancement
Classification: Unclassified
OS: All
Reporter: t...@timdeaton.org
Hardware: Other
Status: UNCONFIRMED
Version: 4.0.6.2 release
Component: Spreadsheet
Product: LibreOffice
I would like to urge you to add a NOPRINTROW and a NOPRINTCOL function to
LibreOffice Calc. I'll explain what I mean below, and include the details of
what I do now (in hopes that it helps). I've never seen what I do in print,
but because of how frequently I use it, I'd be very surprised if many others
didn't find such functions to be almost indispensible once they found them.
The first spreadsheet program I used (ancient shareware: ExpressCalc) had a
function called NOPRINT. I could put it in a cell by itself, or use it inside
another function (like IF(G15=0,NOPRINT,"")). Either way, if any cell in a
given row evaluated to "NOPRINT", then that row would not be printed when I
printed the spreadsheet.
This function was very handy whenever I wanted to build a re-usable table that
might allow for 100 rows of data entry & related calculations, but in which
not all rows are likely to be filled every time. For instance, I might have
an invoice sheet listing 30 different possible items, but I only want the 5
that happen to have quantities filled in to actually show up on the printout.
Unfortunately, I've never found a similar function in any other spreadsheet
program. However, in Excel97, I can achieve the same result using a couple of
macros, some predefined range names, and a "Print" button that runs the
macros. I define a convenient column with the range name "NOPRINT", the cell
that I want to appear (after printing) at the upper-left corner of the screen
as "PRESTART", and the cell that I want to be active when printing is finished
as "START". If there happen to be any columns I don't want printed, I define
them in a fourth range name called "COLNOPRINT". Finally, in the NOPRINT
column, I put the NA() function on each row that I never want printed, and I
use an IF() statement that evaluates to NA() unless the row should be printed.
Then, the macros basically do this:
1) Unprotect the sheet.
2) Go to the PRESTART and START cells to get to the view I want to return to.
3) Create a "TempView" view for later use.
4) Hide any columns included in a COLNOPRINT range.
5) Find all cells in the NOPRINT column that evaluate to NA(), and hide those
rows.
6) Print the sheet.
7) Return to the "TempView" view (to undo all the things hidden in this
process).
8) Delete the "TempView" (so it doesn't interfere with future use and other
sheets)
9) Protect the sheet.
The 'Visual Basic For Applications' macros are as follows:
'
'==============================================================================
' FindRange Function
' Macro from MS Excel Tech support 5/1/96 by Paul Peck
' to find if a range exists
'
'
Function FindRange(r)
Dim x As Range
On Error Resume Next
Set x = ActiveSheet.Range(r)
On Error GoTo 0
If Not (x Is Nothing) Then FindRange = True
End Function
'
'==============================================================================
' Print_Valid Macro
' Macro recorded & edited 11/24/99 by Tim Deaton
'
'
Sub Print_Valid()
On Error GoTo ErrorMsg
ActiveSheet.Unprotect
'set up & create the view to return to when finished
If FindRange("PRESTART") Then
Application.Goto Reference:="PRESTART", Scroll:=True
End If
If FindRange("START") Then
Application.Goto Reference:="START"
End If
ActiveWorkbook.CustomViews.Add "TempView", True, True
'Hide any columns that should not be printed
If FindRange("COLNOPRINT") Then
Application.Goto Reference:="COLNOPRINT"
Selection.EntireColumn.Hidden = True
End If
'select "noprint" range, then "n/a" cells, then hide "n/a" rows
Application.Goto Reference:="NOPRINT"
Selection.SpecialCells(xlFormulas, 16).Select
Selection.RowHeight = 0
'delete any defined print range so the entire visible area will print
If FindRange("Print_Area") Then
ActiveWorkbook.Names("Print_Area").Delete
End If
'view, then print the sheet
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True
'return to the view you started with, then delete that view name
ActiveWorkbook.CustomViews("TempView").Show
ActiveWorkbook.CustomViews("TempView").Delete
ActiveSheet.Protect
Exit Sub
ErrorMsg:
Msg = Str(Err) & ": " & Error(Err)
MsgBox Msg, vbCritical
Resume Next
End Sub
'
'==============================================================================
' Print_Valid_Plus Macro
' Macro recorded & edited 12/15/99 by Tim Deaton
'
'
Sub Print_Valid_Plus()
On Error GoTo ErrorMsg
'unprotect all sheets in workbook
For i = 1 To Sheets.Count
Sheets(i).Unprotect
Next i
'call Print_Valid() procedure to setup & print the sheet
Print_Valid
'protect all sheets in the workbook
For i = 1 To Sheets.Count
Sheets(i).Protect
Next i
Exit Sub
ErrorMsg:
Msg = Str(Err) & ": " & Error(Err)
MsgBox Msg, vbCritical
Resume Next
End Sub
'
Usually when I need a NOPRINT function, I also need to protect the sheet to
protect various pre-defined formulas. But since the macros use VIEWs, they
have to "Unprotect" all sheets in the workbook and then "Protect" them at the
end of the process. Therefore, if I have multiple sheets in the workbook, the
PRINT button has to run the "PrintValidPlus" macro. Otherwise, I have the
button run the "PrintValid" macro.
In order for this to work well, I have to define the range names as local ONLY
to this sheet.
("Sheetname!Rangename")
Otherwise, I could only use this technique on one sheet in a workbook.
It would be very helpful if I could do the same with views, but Excel doesn't
offer that option. Starting with Excel97, all views are global to the entire
workbook -- a very bad idea in my opinion.
I hope I've explained this in enough detail and clearly enough that some
of your spreadsheet developers will agree that it's needed, and will build it
into the spreadsheet program.
In any case, thanks for taking the time to read and understand what I've
written.
Sincerely,
Tim Deaton
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
