automatically roll the page number after you print

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Perplexed! - 06 Mar 2007 17:39 GMT

I am trying to create a form that will automatically change to the next
number in the sequence after a copy is printed. Is this possible in excel
and if so how is it done?

Reply to this Message

Joerg - 07 Mar 2007 02:40 GMT

Not exactly what you need, but maybe a start: You could use Excel's
BeforePrint event. Put following code into the code page of ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.Range("A1") = ActiveSheet.Range("A1") + 1
End Sub

This would increase the value in A1 every time you are about to print.
Drawbacks: Changes value before print, not after. Changes the value also if
PrintPreview is used or printing is cancelled.

Cheers,

Joerg

> I am trying to create a form that will automatically change to the next
> number in the sequence after a copy is printed. Is this possible in excel
> and if so how is it done?

Reply to this Message

Perplexed! - 07 Mar 2007 19:54 GMT

Thanks Joerg,

I think that this will work, hwoever I'm having a little trouble getting it
to work, I went into VBE and inserted a module, cut and pasted what you wrote
then put in my starting number in cell A1 and went to the print preview
screen and nothing happened. I did manage to get it to work once at my
office, but when I tried it again at home it wouldn't work. I followed the
steps to the letter on the office help site on Creating my own worksheet
function but I must have done something different when it worked(obviously!)
and I cannot for the life of me remember what I did! If you could let me
know what step I am missing it would mean my undying gratitude! Thanks in
advance.

> Not exactly what you need, but maybe a start: You could use Excel's
> BeforePrint event. Put following code into the code page of ThisWorkbook:
[quoted text clipped - 14 lines]
> > number in the sequence after a copy is printed. Is this possible in excel
> > and if so how is it done?

Reply to this Message

Gord Dibben - 07 Mar 2007 20:43 GMT

Code goes into the Thisworkbook module, not a standard module that you created
with Insert>Module.

Select your workbook/project.

Expand the "Microsoft Excel Objects"

Double-click on Thisworkbook and paste the code in there.

Gord Dibben MS Excel MVP

>Thanks Joerg,
>
[quoted text clipped - 27 lines]
>> > number in the sequence after a copy is printed. Is this possible in excel
>> > and if so how is it done?

Reply to this Message