Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded

[Nap]

Hi everyone,

Before I explain my problem, here is some background info:
- I use a formatted excel spreadsheet as an invoice which I send t
customers via email.
- The invoice number in the spreadsheet is the same as the name o
the file,
- To date, I've been performing all operations manually (Invoic
number and spreadsheet name),
- I've decided that I want to automatically fill in the Invoic
number in the spreadsheet from the name of the file, and have worke
out a macro that will do it using the WINDOW_OPEN event.
So everything works fine *except* that my customer must click 'enabl
macros' when they open my invoice. My goal is therefore to eliminat
this requirement.

I moved the code I developed to achieve the above into Personal.xl
(which is hidden) and thought it might work. Well, it didn't. Th
problem is that when I double click the invoice spreadsheet in Window
Explorer, Excel opens the Personal.xls file first, and then opens th
file I actually want to edit.

Therefore my code runs when the WINDOW_OPEN event is fired upon loadin
of Personal.xls, but since my target file is not yet loaded, I don't ge
what I want. Thus I need to fire the event a second time after th
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 fil
to:
1) Execute automatically after the target spreadsheet is loaded,
2) Make the required alterations in the target spreadsheet (no
Personal.xls). [This I can workout myself]


This is the code (in Personal.xls) that checks if the opene
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 th
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 Su
-------------------


I have search the google groups, but have not been able to find a
answer as yet. I have added a class module to Personal.xls hoping t
trap the OPEN event but don't know what I should code to process th
event correctly.
I have used the following line to expose the events but don't kno
what's nex
Code
-------------------
Private WithEvents XL As Excel.Workboo
-------------------



If anyone can help me, I would greatly appreciate it.

Thanks and cheers,
Na

 

[Nigel]

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

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]

I simply copy the previous invoice, change the billing details etc.
It's a hassle to remember to change the invoice number in addition t
all the other stuff, and there have been mistakes in the past.
This leads to confusion etc when following up payments.

Cheers,
Nap

 

[Nap]

I'm sorry for your reply, so I will put as much thought into my answer.

Why not write it out using paper and pen, and send it in the mail?

To answer your question;
I simply copy the previous invoice, change the billing details etc.
It's a hassle to remember to change the invoice number in addition t
all the other stuff, and there have been mistakes in the past.
This leads to confusion etc when following up payments.

If you can't offer a solution, please don't polute the thread.

Cheers,
Nap

 

[broro183]

Hi Nap,

Just curious, but how will you get your personal.xls file into your
customer's Excel startup directory?

We can't prevent the need for pressing the enable macros button as
explained by Jim:
http://excelforum.com/showthread.php?t=524914&highlight=enable+macros

A possible approach is to "force" your customer's to click the "enable
macros" button before they can create an invoice, see Bob Phillips'
post:
http://excelforum.com/showthread.php?t=516487&highlight=enable+macros

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Nap]

Broro183,

I am putting the macro that updates the Invoice number into M
personal.xls, NOT the customer's. Nor am I sending my personal.xls t
the customer.

If you look at my code, you will see that I am updating Row 14, Co
11, in the ActiveWorkbook. (The code has a bug in it at the moment i
that as it stands, it will put the invoice number into R14,C11 of th
personal.xls.

Once I get an answer to my question, I will fix this bug.

So, in the end, the customer will get a clean spreadsheet with n
macros in it.

Cheers,
Nap

 

[Nigel]

Since you clearly cannot keep a civil tongue and that you do not recognise a
suggestion other than an solution to a flawed question I'll not proffer any
more. However for those following this thread YOU do not need to run the
code every time the workbook is opened, since your original post did not
contain any information about incrementing the invoice number from the
previous invoice I will as you suggest not pollute this thread anymore. My
working solution is just fine. Good luck in your search for one.

 

[broro183]

Hi Nap,

Sorry, I misinterpreted your intent in your post. It looks like Dave P
& yourself are figuring out your problem re the "work book open"
issue.
http://excelforum.com/showthread.php?t=525723&highlight=workbook+open


I'm guessing that the above will also provide a solution to the issue
of
"updating Row 14, Col 11, in the ActiveWorkbook"
b/c if still needed you can change the relevant line
"Application.ActiveWorkbook.ActiveSheet.Cells(14, 11) = InvoiceName" to
refer to the appropriate workbook.

Good luck,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...