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 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)

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Thanks and cheers,
Nap

[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



"Nap" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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 to all the other stuff, and there have been mistakes in the past.
This leads to confusion etc when following up payments.
I would like to have it done in the background without having to worry about it.

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...=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...=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 MY personal.xls, NOT the customer's. Nor am I sending my personal.xls to the customer.

If you look at my code, you will see that I am updating Row 14, Col 11, in the ActiveWorkbook. (The code has a bug in it at the moment in that as it stands, it will put the invoice number into R14,C11 of the 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 no 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.

--
Cheers
Nigel



"Nap" <[email protected]> wrote in message
news:[email protected]...
>
> 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 to
> 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.
>
>
> --
> Nap
> ------------------------------------------------------------------------
> Nap's Profile:
> http://www.excelforum.com/member.php...o&userid=32722
> View this thread: http://www.excelforum.com/showthread...hreadid=525580
>

[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...=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...

Broro183,

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

If you look at my code, you will see that I am updating Row 14, Col 11, in the ActiveWorkbook. (The code has a bug in it at the moment in that as it stands, it will put the invoice number into R14,C11 of the 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 no macros in it.

Cheers,
Nap.