Excel auto new macro

[JudyL]

Is there a way to have a macro run when starting a new spreadsheet from a template?

I want to have a cell value increased by one (like a work order number) each time a new spreadsheet is created from the template.

 

[SkipVought]

Hi,

Think about it. The template opens as an XLS. Some cell value increases by ONE. Where does that value get back into the template? It does not.

So you're creating invoices. Where are you storing the data about the invoices that you are creating: the InvoiceID, the Customer, the associated Purchase Order(s), the Item list contained in the Invoice??? Any links to available/relieved inventory?

Back to the matter at issue. You ought to have a database that you inquire and update with this information. It could be in the workbook that you use to RUN the Invoice routine - a sheet of which, could be your "template" if you want to think of it that way. And your InvoiceID could be incrimented from a cell value quite easily.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[JudyL]

Thanks. This is a question that a client raised. He said that for a variety of reasons,they are not using a database to track their work orders. I agree, that would be the way to go. These are not invoices, they are internal work orders for things like an immediate project. It has nothing to do with inventory. They want to be able to print out a work order and have each one have a number incremented by one over the last work order.

In Word, you can create a document variable. I am looking for something similar. I thought of creating a Named range with a starting value of 0. Then have a cell in the template form that contains a formula that adds 1 to the Named range. The auto macro would increase the value of the named range by one but I need a way for it to automatically run when a new form is created by the template.

 

[SkipVought]

How is the template populated?

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Rofeu]

Hi, have a look at this help topic:

Using Events with the Application Object


That should get you well on your way.

Cheers,

Roel

 

[JudyL]

Thanks. I haven't written class modules before but I know there are help items on that.

In answer to the previous response, the spreadsheet is being set up as a form with a user entering data in most of the areas. In one of my earlier posts to this question, I said I thought I might use a named range that has the value assigned. The auto macro would update the number each time a new spreadsheet was started from a template.

 

[WinblowsME]

' Code goes in ThisWorkbook within the .xlt file

Private Sub Workbook_Open()
   Dim file_name As String, count As String
   
   file_name = "C:\WINDOWS\Temp\Counter.txt"
   count = "0"
   
   On Error Resume Next
      
   Open file_name For Input As #1
      Line Input #1, count
   Close #1
   
   Open file_name For Output As #1
      Write #1, CInt(count) + 1
   Close #1
   
   MsgBox CInt(count) + 1
End Sub