Prerinted forms

I am currently using Excel in Office 2000. Is it possible to create a form
on screen and designate the form labels as non-printing while the data cells
would print? i.e this would be necessary to use an on-screen form with a
preprinted form. I can hide the label cells but then they disappear from
view, defeating the usefulness of the on-screen form. Would a later Excel
version offer this capability?

Hi Arthur

by "on screen form" you do mean a user form (created in vba - insert
userform) or a worksheet set up like a form - if you could set up a
worksheet like the form you could have a print macro that selected the cells
that you wanted to print and then printed them. From a userform, i'm not
sure how you would do this.

alternatively, if you are using a userform, you could have code that wrote
the information entered out to a blank worksheet that had been sized etc for
printing onto the form. If the sizing & layout was a problem you could
always (i've not tried this, but can't see any reason it can't be done)
write the information out to a word table and print it from there.

Cheers
JulieD

"Arthur F. Dunn, M.D." <[email protected]> wrote in message
news:e%[email protected]...
>I am currently using Excel in Office 2000. Is it possible to create a form
> on screen and designate the form labels as non-printing while the data
> cells
> would print? i.e this would be necessary to use an on-screen form with a
> preprinted form. I can hide the label cells but then they disappear from
> view, defeating the usefulness of the on-screen form. Would a later Excel
> version offer this capability?
>
>
>

What you could do is create a special hidden sheet specifically for the
purpose of printing.
The hidden sheet is formatted for printing. Any values are formulas
referencing the onscreen sheet.

The workbook event BeforePrint could be used to trap and print the hidden
sheet instead:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet2").PrintOut
End Sub

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Arthur F. Dunn, M.D." <[email protected]> wrote in message
news:e%[email protected]...
>I am currently using Excel in Office 2000. Is it possible to create a form
> on screen and designate the form labels as non-printing while the data
> cells
> would print? i.e this would be necessary to use an on-screen form with a
> preprinted form. I can hide the label cells but then they disappear from
> view, defeating the usefulness of the on-screen form. Would a later Excel
> version offer this capability?
>
>
>

I realised shortly after posting that is wasn't quite that straightforward.
Sorry.

Assuming your Worksheets are named OnScreen and OffScreen...
The Workbook Event code should look more like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Static blnScreen_Override As Boolean

If ActiveSheet Is Worksheets("OnScreen") And Not blnScreen_Override Then
blnScreen_Override = True
Cancel = True
Worksheets("OffScreen").PrintOut
blnScreen_Override = False
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Rob van Gelder" <[email protected]> wrote in message
news:%[email protected]...
> What you could do is create a special hidden sheet specifically for the
> purpose of printing.
> The hidden sheet is formatted for printing. Any values are formulas
> referencing the onscreen sheet.
>
> The workbook event BeforePrint could be used to trap and print the hidden
> sheet instead:
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Worksheets("Sheet2").PrintOut
> End Sub
>
> --
> Rob van Gelder - http://www.vangelder.co.nz/excel
>
>
> "Arthur F. Dunn, M.D." <[email protected]> wrote in message
> news:e%[email protected]...
>>I am currently using Excel in Office 2000. Is it possible to create a form
>> on screen and designate the form labels as non-printing while the data
>> cells
>> would print? i.e this would be necessary to use an on-screen form with a
>> preprinted form. I can hide the label cells but then they disappear from
>> view, defeating the usefulness of the on-screen form. Would a later Excel
>> version offer this capability?
>>
>>
>>
>
>

Hi

If I understand your setup correctly; you want some worksheet cells
containing descriptions to be invisible when you print the worksheet to
paper ? You would need to use the "BeforePrint" macro event for that, and
that's quite primitive. This is better:

Put textboxes from the Drawing toolbar over those cells, clear the cells and
type into the textboxes instead, they are your new labels. Rightclick the
textbox border and select Format Textbox, pane Properties. Unselect "Print
object" and select "move and size with cells".

(Apologies for all the guessing; Forms and Labels come in many variations on
computers)

HTH. Best wishes Harald

"Arthur F. Dunn, M.D." <[email protected]> skrev i melding
news:e%[email protected]...
> I am currently using Excel in Office 2000. Is it possible to create a form
> on screen and designate the form labels as non-printing while the data
cells
> would print? i.e this would be necessary to use an on-screen form with a
> preprinted form. I can hide the label cells but then they disappear from
> view, defeating the usefulness of the on-screen form. Would a later Excel
> version offer this capability?
>
>
>

That's a great suggestion Harald - thinking out of the box - I like it.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Harald Staff" <[email protected]> wrote in message
news:%[email protected]...
> Hi
>
> If I understand your setup correctly; you want some worksheet cells
> containing descriptions to be invisible when you print the worksheet to
> paper ? You would need to use the "BeforePrint" macro event for that, and
> that's quite primitive. This is better:
>
> Put textboxes from the Drawing toolbar over those cells, clear the cells
> and
> type into the textboxes instead, they are your new labels. Rightclick the
> textbox border and select Format Textbox, pane Properties. Unselect "Print
> object" and select "move and size with cells".
>
> (Apologies for all the guessing; Forms and Labels come in many variations
> on
> computers)
>
> HTH. Best wishes Harald
>
> "Arthur F. Dunn, M.D." <[email protected]> skrev i melding
> news:e%[email protected]...
>> I am currently using Excel in Office 2000. Is it possible to create a
>> form
>> on screen and designate the form labels as non-printing while the data
> cells
>> would print? i.e this would be necessary to use an on-screen form with a
>> preprinted form. I can hide the label cells but then they disappear from
>> view, defeating the usefulness of the on-screen form. Would a later Excel
>> version offer this capability?
>>
>>
>>
>
>