save as change on cell detail change

[kaplonk]

Let me explain what I mean.
I have a load of invoices numbered consecutively. To make a new one
open the most recent spreadsheet change the invoice no. in one of th
cells and then 'save as' the_invoice_no.xls
It's a bit of a pain.
What would be nice is to be able to open a document ready for writin
with the invoice no. cell and the file name auto incremented.
Is this possible

 

[Frank Kabel]

Hi
one way (using the Windows registry for storing the last number). Put
the following code in the workbook module (not in a standard module) of
your template:
- It changes cell A1 of the first sheet
- you may change the key identifiert according to your needs (e.g.
DEFAULTSTART, MYLOCATION, etc.)
- saves the file afterward

Private Sub Workbook_Open()
Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Const MYFNAME As String = "Invoices_"
Dim regValue As Long

With ThisWorkbook.Sheets(1).Range(MYLOCATION)
If .Text <> "" Then Exit Sub
regValue = GetSetting(MYAPPLICATION, MYSECTION, _
MYKEY, DEFAULTSTART)
.Value = Format(date,"YY") & "-" & format(regValue,"00000")
SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
ThisWorkbook.Saves MYFNAME & .value & ".xls"
End With

End Sub

 

[kaplonk]

hmm... (excel beginner!!)

Put
the following code in the workbook module (not in a standard module
of
your template:

I'm sorry - what does this mean

 

[Frank Kabel]

Hi
try the following:
- open your template (*.xlt file)
- open the VBA editor with ALT+F11
- locate the entry 'ThisWorkbook' in the left explorer view
- double click on it and insert the code in the appearing code editor
window
- close the VBA editor, save the template, close it
- now create a new workboom based on this template

for event procedurese see: http://www.cpearson.com/excel/events.htm

 

[Max]

Try this:

Right-click on the Excel icon,
(which is just to the left of "File" on the standard menu bar)
then choose "View code"

This will bring you direct to the This Workbook module in VBE

Copy and paste Frank's code into the whitespace on the right
(Paste everything between the dotted lines below)

-------begin vba---
Private Sub Workbook_Open()
Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Const MYFNAME As String = "Invoices_"
Dim regValue As Long

With ThisWorkbook.Sheets(1).Range(MYLOCATION)
If .Text <> "" Then Exit Sub
regValue = GetSetting(MYAPPLICATION, MYSECTION, _
MYKEY, DEFAULTSTART)
.Value = Format(date,"YY") & "-" & format(regValue,"00000")
SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
ThisWorkbook.Saves MYFNAME & .value & ".xls"
End With

End Sub
-------end vba---

Press Alt + Q to get back to Excel