Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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 -- Na ----------------------------------------------------------------------- Nap's Profile: http://www.excelforum.com/member.php...fo&userid=3272 View this thread: http://www.excelforum.com/showthread.php?threadid=52558 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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 -- Na ----------------------------------------------------------------------- Nap's Profile: http://www.excelforum.com/member.php...fo&userid=3272 View this thread: http://www.excelforum.com/showthread.php?threadid=52558 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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 -- Na ----------------------------------------------------------------------- Nap's Profile: http://www.excelforum.com/member.php...fo&userid=3272 View this thread: http://www.excelforum.com/showthread.php?threadid=52558 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=525580 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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 -- Na ----------------------------------------------------------------------- Nap's Profile: http://www.excelforum.com/member.php...fo&userid=3272 View this thread: http://www.excelforum.com/showthread.php?threadid=52558 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded
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... Nap Wrote: 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. -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=525580 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
personal.xlsb not loaded when excel is called by 3rd party applica | Excel Discussion (Misc queries) | |||
Workbook.Activate / Window.Activate problem | Excel Programming | |||
On activate event | Excel Programming | |||
Pasting after Workbook Activate event | Excel Programming | |||
event to know after data has loaded | Excel Programming |