Why do you need to add the invoice number every the invoice workbook is
opened?
If the workbook is unique for each invoice why not populate the invoice
number into the workbook before you save it then there is no need for code?
Unless I am missing something you could modify your invoice create process
to insert the number at that time.
--
Cheers
Nigel
"Nap" wrote in message
...
Hi everyone,
Before I explain my problem, here is some background info:
- I use a formatted excel spreadsheet as an invoice which I send to
customers via email.
- The invoice number in the spreadsheet is the same as the name of
the file,
- To date, I've been performing all operations manually (Invoice
number and spreadsheet name),
- I've decided that I want to automatically fill in the Invoice
number in the spreadsheet from the name of the file, and have worked
out a macro that will do it using the WINDOW_OPEN event.
So everything works fine *except* that my customer must click 'enable
macros' when they open my invoice. My goal is therefore to eliminate
this requirement.
I moved the code I developed to achieve the above into Personal.xls
(which is hidden) and thought it might work. Well, it didn't. The
problem is that when I double click the invoice spreadsheet in Windows
Explorer, Excel opens the Personal.xls file first, and then opens the
file I actually want to edit.
Therefore my code runs when the WINDOW_OPEN event is fired upon loading
of Personal.xls, but since my target file is not yet loaded, I don't get
what I want. Thus I need to fire the event a second time after the
Invoice is loaded, but I don't know how to achieve this.
So my question is, how do I setup my macro in the Personal.xls file
to:
1) Execute automatically after the target spreadsheet is loaded,
2) Make the required alterations in the target spreadsheet (not
Personal.xls). [This I can workout myself]
This is the code (in Personal.xls) that checks if the opened
spreadsheet is an invoice, and if it is, updates the invoice number:
(I know that I will need to index through the opened workbooks once the
Invoice is loaded)
Code:
--------------------
Private Sub Workbook_Open()
Dim InvoiceName As String
If Application.ActiveWindow Is Nothing Then
' do nothing
Else
InvoiceName = Application.ActiveWorkbook.Name
If Left(InvoiceName, 3) = "INV" Then
InvoiceName = Mid(InvoiceName, 4, Len(InvoiceName) - 7)
Application.ActiveWorkbook.ActiveSheet.Cells(14, 11) = InvoiceName
End If
End If
End Sub
--------------------
I have search the google groups, but have not been able to find an
answer as yet. I have added a class module to Personal.xls hoping to
trap the OPEN event but don't know what I should code to process the
event correctly.
I have used the following line to expose the events but don't know
what's next
Code:
--------------------
Private WithEvents XL As Excel.Workbook
--------------------
If anyone can help me, I would greatly appreciate it.
Thanks and cheers,
Nap
--
Nap
------------------------------------------------------------------------
Nap's Profile:
http://www.excelforum.com/member.php...o&userid=32722
View this thread: http://www.excelforum.com/showthread...hreadid=525580