"Pleading paper" in Excel?

"Alan Beban" wrote...
....
Recognizing that you do indeed mean *in* Excel (for the convenience of
the court investigator), . . .
....

If you're correct, then the goal would be to make Excel workbooks appear as
if they're written in pleading format on screen. There are times (OK, darn
near all the time) I'm grateful I never wanted to be a lawyer!

OK, let me see if I understand this. You have to print the XL document
with a particular line numbering requirement. You also have to send
the actual XL document for verification purposes.

So, bottom line is that the line numbers must be in the XL document,
not just on the printed paper.

Correct? If so, the code below does what you want. Were you serious
about paying for it? Obviously, you don't have to since the code is
below. {g} But, if you were, how does $25 sound? Visit
http://www.tushar-mehta.com/paypal/ and I will use it to increase my
contribution to one of the causes I support.

Option Explicit

Sub testIt()
Const TopRows As Integer = 9
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$" & CStr(TopRows)
.PrintTitleColumns = "$A:$A"
MsgBox .PrintTitleRows & vbNewLine _
& .PrintTitleColumns
End With
With ActiveSheet
.UsedRange.Cells(.UsedRange.Cells.Count).Activate
'XL has some strange problem with the pagebreaks _
collection if the entire sheet hasn't been viewed
.Columns("A").ClearContents
Range(.Cells(1, 1), .Cells(TopRows, 1)).Formula = "=ROW()"
.Cells(TopRows + 1, 1).Value = TopRows + 1

Dim allHPageBreaks As HPageBreaks, _
aHPageBreak As HPageBreak, i As Long
Set allHPageBreaks = ActiveSheet.HPageBreaks
For i = 1 To allHPageBreaks.Count
Set aHPageBreak = allHPageBreaks(i)
With aHPageBreak.Location
MsgBox .Address
.Value = TopRows + 1
End With
Next i

With Application.Intersect(.UsedRange, .Columns("A"))
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"
.Copy
.PasteSpecial xlPasteValues
End With
End With
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Actually, not so much! I didn't go into great detail about WHY I need to keep
the data in Excel, because I didn't think it was relevant to the Forum. The
fact is, I do. This is a court-required accounting, and they (the court
investigator) needs to review the calculations in the raw Excel data. He
isn't all that technically advanced, and just the IDEA of sending him a Word
document with links to an Excel spreadsheet... [shudder].

Apparently, attorneys are asked to do this (submit data in Excel) a lot. I
was hoping someone would have already worked out a solution for it. It may
well be that most attorneys do what RagDyer suggested: printing out
pleading-paper "forms" using Word, and then printing Excel data over top.
Harlan is right about issues with printer jams and non-alignment with
preprinted data ("registration errors"); however, I regularly see briefs come
out of attorney's offices which were clearly done in Word, but the text
doesn't line up with the line numbers because somebody switched a font or
font size!! So apparently the courts are somewhat tolerant of such errors. It
would be interesting to see if there was a correlation between papers filed
this way and cases lost....

But back to the problem: I'm very sure that there is a way to detect a Page
Break through Visual Basic. It's probably some facet of the Page object, but
I'm not enough of a programmer to figure it out (or at least, not easily!)
Assuming that Page Breaks CAN be detected through VB, presumably one could
write a Macro to fill the first column of a Worksheet with appropriate
numbers. Any of you wikked programmers want to take a shot at it?? I can pay
(not too much) for a solution...

Thanks,
GB

"Harlan Grove" wrote:

RagDyeR wrote...
OP stated he preferred not to use that, . . .

There are occasions on which OPs need to be told to rethink their
assumptions/preconceived notions. This is one of those occasions.

Tushar Mehta wrote...
OK, let me see if I understand this. You have to print the XL
document
with a particular line numbering requirement. You also have to send
the actual XL document for verification purposes.

So, bottom line is that the line numbers must be in the XL document,
not just on the printed paper.
....

Your macro doesn't do what the OP asked. It just creates sequential
line numbers. Why a macro is needed to do this rather than just using
=ROW() in all cells in column A is a mystery to me.

Reread the OP's specs and do a little bit of research. All pages in a
legal pleading start with row number 1 and end with either 25 or 28
depending on paper size. Here's a specimen.

http://www.azd.uscourts.gov/azd/courtinfo.nsf/0/19c15b1ef79c182007256e84008319d1/$FILE/pleadingpaper.pdf

(or http://makeashorterlink.com/?N3E1428CA ).

If numbers really are supposed to appear every other line, and ignoring
blank lines above the top line and below the bottom line in the body of
the page, then column A needs a 1 in row 1, a 2 in row 3, etc. The top
9 rows repeat on each page, so line numbers 1 through 5 would repeat
from rows 1, 3, 5, 7 and 9. Row 11 would show line number 6, row 13
line number 7, etc. through row 55 showing line number 28. Then the
line number would reset to 6 in row 57. If all this holds, then this
could be done with simple worksheet formulas. Enter the following
formula in A11.

A11:
=IF(MOD(ROW(),2),MOD(ROW()-11,2*(28-5))/2+6,"")

Fill A11 down as far as needed. Then play with the page setup to ensure
that pages break 2 rows after the rows containing line number 28. If
the particular jurisdiction in question uses line numbers only through
25, replace the 28 in the formula above with 25.


If it's not necessary for the Excel files to appear like this on
screen, it'd be REALLY FOOLISH to screw them up with this sort of
formatting. If they only need to appear as pleadings in hardcopy, I'll
repeat that it'd be better to print them as text files from Excel, then
import them into Word's legal pleading template and print from there.

In article . com,
says...

Your macro doesn't do what the OP asked. It just creates sequential
line numbers. Why a macro is needed to do this rather than just using

And, what do you think the following does?

For i = 1 To allHPageBreaks.Count
Set aHPageBreak = allHPageBreaks(i)
With aHPageBreak.Location
MsgBox .Address
.Value = TopRows + 1
End With
Next i

Reread the OP's specs and do a little bit of research. All pages in a
legal pleading start with row number 1 and end with either 25 or 28
depending on paper size. Here's a specimen.

It is rather arrogant of you to presume I didn't research what a
pleading document is before coming up with my solution.

Get off your high horse, Harlan. You are not the only person on this
planet with a brain. 1/2 a brain maybe, a complete one, no way.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article . com,
says...
Tushar Mehta wrote...
OK, let me see if I understand this. You have to print the XL
document
with a particular line numbering requirement. You also have to send
the actual XL document for verification purposes.

So, bottom line is that the line numbers must be in the XL document,
not just on the printed paper.
...

Your macro doesn't do what the OP asked. It just creates sequential
line numbers. Why a macro is needed to do this rather than just using
=ROW() in all cells in column A is a mystery to me.

Reread the OP's specs and do a little bit of research. All pages in a
legal pleading start with row number 1 and end with either 25 or 28
depending on paper size. Here's a specimen.

http://www.azd.uscourts.gov/azd/courtinfo.nsf/0/19c15b1ef79c182007256e84008319d1/$FILE/pleadingpaper.pdf

(or http://makeashorterlink.com/?N3E1428CA ).

If numbers really are supposed to appear every other line, and ignoring
blank lines above the top line and below the bottom line in the body of
the page, then column A needs a 1 in row 1, a 2 in row 3, etc. The top
9 rows repeat on each page, so line numbers 1 through 5 would repeat
from rows 1, 3, 5, 7 and 9. Row 11 would show line number 6, row 13
line number 7, etc. through row 55 showing line number 28. Then the
line number would reset to 6 in row 57. If all this holds, then this
could be done with simple worksheet formulas. Enter the following
formula in A11.

A11:
=IF(MOD(ROW(),2),MOD(ROW()-11,2*(28-5))/2+6,"")

Fill A11 down as far as needed. Then play with the page setup to ensure
that pages break 2 rows after the rows containing line number 28. If
the particular jurisdiction in question uses line numbers only through
25, replace the 28 in the formula above with 25.


If it's not necessary for the Excel files to appear like this on
screen, it'd be REALLY FOOLISH to screw them up with this sort of
formatting. If they only need to appear as pleadings in hardcopy, I'll
repeat that it'd be better to print them as text files from Excel, then
import them into Word's legal pleading template and print from there.

Just a question.

I can understand how MSWord works with a pleading document. It counts lines per
page.

But in excel, you have rows and columns. Wouldn't you need a counter for both
rows and columns?

This may not work for you (or your bosses or the court), but I used it when I
printed documents for meetings.

File|page setup|Sheet tab
Check "row and column headings"

If you need numbers for the columns, you can change that via:
tools|options|general tab|check R1C1 reference style box.

Note that your formulas will change to that R1C1 format, too.



geebee wrote:

I need to be able to print Excel data so that it looks like it was printed on
"pleading paper" (this term will be well-known to legal-types. It mostly
involves having an incrementing line number appear at the left edge of the
paper, on every other line. There also has to be a double line at the right
edge of the line-number cells, but I know how to accomplish that part!) The
line numbers need to restart with every new page, and this is where I am
running into problems.

For each worksheet in a book, I want the first 9 rows to repeat at the top
of each page. I figured out how to do that (File/Page Setup/Sheet...), and I
also repeated the first (leftmost) column on every page. I manually formatted
the first 9 rows with "pleading paper" number information as well. Hoping to
use that as a starting point, I tried to use a formula like this in the first
column of the remaining (data) rows:

=IF(CELL("contents",A9)="","",A9+1)

... attempting to use the pattern set up by the first nine rows to number
the rest of each page. This worked fine for the first page, but it just kept
on incrementing the numbers on each successive page. Obviously, the process
of inserting the "9 rows" takes place at print time, and so this formula
fails. I tried variations on the theme of the formula above before realizing
this.

I figure I'm not the first person in the world to need to do this; but all
my searches so far have been fruitless. Of course, MS Word has a Pleading
Wizard that automates this whole process; and I suppose I COULD import all
the Excel data into Word, and let Word format it into Pleading form. However,
I need to also submit the data in Excel to show other calculations, and it
would just be much more convenient to just keep everything in Excel. I would
be willing to consider accomplishing this using a template or macro, as well
as a formulaic solution.

Thanks,
GB

--

Dave Peterson

Tushar Mehta wrote:
says...
Your macro doesn't do what the OP asked. It just creates sequential
line numbers. Why a macro is needed to do this rather than just using

And, what do you think the following does?

For i = 1 To allHPageBreaks.Count
Set aHPageBreak = allHPageBreaks(i)
With aHPageBreak.Location
MsgBox .Address
.Value = TopRows + 1
End With
Next i

I did test it, but apparently not thoroughly. It does reset to TopRows
+ 1 after each page break.

That said, if the OP hasn't yet set page breaks, your macro almost
certainly doesn't produce the required line numbers - ending at 28 then
resetting to 10. Adding your macro to a new workbook with factory
default US page settings, A1:A52 get line numbers 1-52, A53:A103 get
line numbers 10-60, A104:A154 get line numbers 10-60, etc. If you
wanted to make this a *USEFUL* macro maybe worth a few bucks ($25.00?
You're smokin' something!), then why not *ENSURE* that line numbers
reset after 28 *AND* insert a page break in the row below where the
line number resets to 10?!

Reread the OP's specs and do a little bit of research. All pages in a
legal pleading start with row number 1 and end with either 25 or 28
depending on paper size. Here's a specimen.

It is rather arrogant of you to presume I didn't research what a
pleading document is before coming up with my solution.

OK, so you didn't understand it. Fine. You're not (only) careless and
lazy, you're stupid. Or didn't you read the OP's original message, in
which:

'"pleading paper" (this term will be well-known to legal-types. It
mostly
involves having an incrementing line number appear at the left edge of
the
paper, on every other line.'

Emphasis on the phrase 'on every other line'. This is a bit complicated
because parts of such documents may be single spaced (parties to the
suit, attorneys, etc. at the top of the first page, footnotes and
verbatim quotes set off from the main text) while other parts must be
double spaced. For example,

http://www.bcltechnologies.com/docum...s/Pleading.pdf

At best uncertain whether spreadsheet exhibits would need to be double
spaced or could be single spaced.

BUT if it's as simple as you believe it to be *AND* line numbers range
from 1 to 28, just use formulas.

A1:
1

A2:
=IF(A1=28,10,A1+1)

and fill A1 down as far as needed.

BTW, *YOU* are the one assuming on screen *MUST* look like hardcopy,
and hardcopy appearance is rigidly specified. *EVERY* hardcopy page
must run from line number 1 to line number 28, so *EVERY* on screen
'page' must also run from line 1 to line 28, so given the top 9
lines/rows frozen, all other lines must run from 10 to 28 with *NO*
pages stoping before line 28 or extending past line 28. Horizontal page
breaks are *IRRELEVANT* or should be set at each row in which the line
number resets to 10 (but not the first instance of line number 10 in
cell A10). So your macro is ill-conceived (rough translation: you wanna
get paid for this dreck?!).

Get off your high horse, Harlan. You are not the only person on this
planet with a brain. 1/2 a brain maybe, a complete one, no way.

Stand up, Tushar, and take some pressure off your brain.