Help please I cannot get an answer to this question

Help please I cannot get an answer to this question

Post by QWxhbiB » Fri, 21 Apr 2006 22:35:02


Hi
I have a macro which uses code to populate certain cells in a document and
then prints this page.
I would like to be able to use code to generate a sequential whole number
each time the macro is run and place this number in a cell on the document to
be printed. Thus whenever the document is printed it is numbered and this
number increases by one each time.

How can this be done please?
 
 
 

Help please I cannot get an answer to this question

Post by Ivan Raimi » Fri, 21 Apr 2006 22:49:56

Hi Alan,

I hope I understood your question (but I am not sure).

Supposing that you store this sequential number in sheet1, cell A1, you
can use following code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
worksheets("sheet2").range("a1").value=worksheets("sheet2").range("a1").value+1
End Sub

adjust the code to your needs.

It will add 1 to value of cell A1 on sheet1 and application will
continue showing the print dialog. What should happen if user cancels
it?

Regards,
Ivan

 
 
 

Help please I cannot get an answer to this question

Post by Ivan Raimi » Fri, 21 Apr 2006 22:51:28

Sorry, I mistyped, in the code "sheet2" should be "sheet1" to
correspond with the description.

Ivan
 
 
 

Help please I cannot get an answer to this question

Post by QUEyZTcyR » Fri, 21 Apr 2006 22:55:02

In the immediate window of the VBE, add a name e.g. MyCounter, thus:

Application.ActiveWorkbook.Names.Add "MyCounter","1"

Save the workbook.

Application.ActiveWorkbook.Save

In your macro, to query the current value:
Evaluate(Application.ActiveWorkbook.Names.Item("MyCounter").Value)

To upadate the value:
Application.ActiveWorkbook.Names.Item("MyCounter").Value = 100

Save the workbook to ensure counter is updated:

Application.ActiveWorkbook.Save