?
Solved

VBA Events in Excel Workbooks

Posted on 1999-12-03
18
Medium Priority
?
1,057 Views
Last Modified: 2008-02-20
I am currently writing an application in which I need for any workbook opened in Excel to trigger the "Workbook_Open" Event in VBA.  In Word, I simply put the code in "Normal.dot", and all documents that use "Normal.dot" as their template would have a reference to it in which the Event is triggered.  However, I am having trouble doing the same thing in Excel.  Thanks for your help.
0
Comment
Question by:ccbailey
  • 5
  • 5
  • 4
  • +3
18 Comments
 
LVL 23

Expert Comment

by:ahammar
ID: 2253836
This doesn't solve your problem, but Personal.Xls or any other workbook that is saved in the XlStart folder will run everytime you open Excel. The path to my XlStart folder is:
C:\Program Files\Microsoft Office\Office\XLStart

The only way I know right now for every workbook that is openned to run a macro at start up, is to put the macro in the WorkBooks_Open event or call a macro that is in the Personal.xls file from the Open event.

I do know a way to make everyWorkbook run a macro when it is openned, but it is using not that long, but quite complicated code. If you are interested in that, let me know and I will tell you as soon as I get home.

You might get a better answer before than

Cheers!
ahammar
0
 
LVL 1

Expert Comment

by:mixa
ID: 2253841
I think you can do same in Excel - you may place book.xlt file in the \Templates folder of MS Office installation. This template will be used for all new workbooks (if you don't choose other template in an explicit way).

In this template you can handle the workbook_open event.
0
 

Author Comment

by:ccbailey
ID: 2253907
Mixa,
If I am understanding you correctly, this does not seem to work.  I tried making a new template named book.xlt and saving it to the Templates folder.  I tried opening it and placing my code there.  But the code will not run for other workbooks (even ones that I create from scratch without explicitly choosing a different template).

ahammar,
The first thing I tried was putting the code in the Personal.xls file, but I soon found out that this only runs when Excel is opened, and not for each workbook that is opened (as you stated).  I would be interested in your code to run a macro every time a workbook is opened.  I will also need to access the Workbook_BeforeSave and Workbook_BeforeClose events.  Will your solution work for these also?

Thanks for your help,
Christy
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 1

Expert Comment

by:mixa
ID: 2253995
:))

I was not exact, sorry. I have reviewed the MS documentation - it should be placed in the Office\XLSTART directory.

Note that templates are not same in XL as in Word - you will have the code separate in each new workbook. In word each documents is linked to one instance of normal.dot.

So it is not ideal solution. But you asked :)
0
 

Author Comment

by:ccbailey
ID: 2254045
Yes, this is what I thought.  But I am trying to get around this.  I want to track all workbooks that are created in certain directories (who last updated it and the size of the file when they did) by putting this information into a database when the user opens and closes the workbook.  Thus I would not be able to re-enter the code into each workbook (other people will be creating these workbooks).  I was hoping someone would have a way around this.

Thanks anyway,

Christy
0
 
LVL 23

Expert Comment

by:ahammar
ID: 2254061
I am going home now, but the solution I have will run any macro in the Personal.xls folder I know for sure, and I think any macro in any workbook, but I'm not sure about that one yet.
I'm not sure I know what you want with the Before_Save and Before_Close events? Those will already run when you save or close the workbook. Do you want them to run as soon as you open the workbook, along with whatever other macro you will run.

Cheers!
ahammar
0
 

Author Comment

by:ccbailey
ID: 2254095
What I want is this:

When someone (anywhere in our office) opens a workbook file, the Workbook_Open event is triggered (in this event, a database will be initialized and preliminary information such as file name, path, and name of the user that opened it will be stored in this database).  When the user saves the workbook, I want it to trigger the WorkBook_BeforeSave Event (here the date and time of the save, as well as the fact that it was saved in this session, will be added to the record in the database).  And when the user closes the workbook, I want it to trigger the Workbook_BeforeClose event (here the amount of time the user spent in the workbook will be calculated and saved to the database).  All of this information will then be available to users in a separate application I am writing in Visual Basic (I have done similar things in AutoCAD and Word and was hoping to do the same in Excel).  I hope this makes a little more sense to you - it is kind of confusing to explain without writing an essay on my program!!

Hope this helps,
Christy
0
 
LVL 23

Expert Comment

by:ahammar
ID: 2254228
Hi Christy,

No need for an essay, I understand now. I think this can be done, but it will take me a little while.

I have one more question. Unless you have someway to monitor everyone at once, then for this will work, all the users that this will apply to, will have to have the macro in their personal.xls workbook (or atleast some workbook that is in the XlStart folder, but I will use Personal.xls when explaining).

Here is my idea:
When the user starts Excel, the macro in the personal.xls Workbook Open event will run. This macro will start monitoring every other workbook that gets open. This will have to be coded as a timer, so it will continuosly run. Everytime it sees a new one open, it will add it to a list and then run whatever code you want it to run.
Everytime it notices one dissapear from the list, it will remove it from the list, and again run whatever code you want it to run.

I don't think there is any way to make it know when a workbook is being saved. No I take that back, there would be a way using API's, but that is a bit more complicated and would take me quite a while, but I will do it if you want me too.

Issues that may or may not be of concern:
The personal.xls file will have to stay open all the time.(Normally it does anyway). If the user closes it, it will not monitor any more. I can put code in it, (or you can) that will prevent the user from being able to close it if you want, but there is no way you will be able to prevent the user from removing the Personal.xls file from the XlStart folder. If none of this is a problem, or you know how to deal with it, then I think I can do it. (Unless you get an answer before then.)

Here is an idea just in case it might interest you:
I could do the same thing in VB I think, then it would be an executable file that you could put in the computers startup folder. It could also be made so that you could not shut it off in any way, including the task manager (Ctrl + Alt + Del). It could be made invisible in every way also.

Just let me know the extreme you want to go to, and I will get started.

See, I wrote an essay instead......:-)

Cheers!
ahammar



0
 
LVL 13

Expert Comment

by:cri
ID: 2254972
ahammar, I think is better to make the workbooks to 'report' these changes 'themselves' as ccbailey wants it, than to make a monitoring applet and trying to detect save commands etc.

ccbailley: Are these files on the network ? If yes: Is it an option to monitor these files with the server tools ?
0
 
LVL 4

Accepted Solution

by:
Noggy earned 600 total points
ID: 2255032
ahammar - You've got the right idea but the best way is to use a WithEvents Class Module. See below.

ccbailey - This is what you need to do:
1. Create a new workbook.
2. Open your VB Editor (Alt+F11)
3. Create a Class Module (NOT a plain Module)
4. Name this Class Module "clsAppEvents" using the Properties window.
5. Paste in this code:
Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
    If Wb.FullName <> ThisWorkbook.FullName Then
        MsgBox "You are closing " & Wb.FullName
    End If
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
    If Wb.FullName <> ThisWorkbook.FullName Then
        MsgBox "You have just opened " & Wb.FullName
    End If
End Sub

6. I have only put in two Events here but you can use the Procedure drop-down listboxes at the top of the module to include other Events off the Application object (like your BeforeSave Event).
7. In the ThisWorkbook Class Module, enter the following code:
Option Explicit

Dim X As New clsAppEvents

Private Sub Workbook_Open()
    Set X.App = Application
End Sub

8. Compile the code.
9. Put protection on the VBA Project (Tools-VBA Project Properties...) to stop prying eyes from seeing your code.
10. Save the workbook as an XLS in a directory that only you can access.
11. Now save the workbook as an XLA.
12. Now for the dodgy part:
    a. You will either need to copy the XLA to everyone's XLStart folder (as detailed quite extensively by the other experts above)
    b. Change everyone's profile (if you have rights to do this) so that their Alternate StartUp Path (Tools-Options-General tab) points to a common directory on the network. This will be better than a. as any later modifications can be applied easily to all users.

13. When the users start Excel, the XLA will automatically be loaded and the events will take effect immediately.
14. (I can't really end on a 13) Whenever the user opens another workbook (or closes it or whatever), the code in your Class Module in the XLA will be executed.

This isn't guaranteed 100% effective if users have access to their StartUp path directories etc. However, it'll be the best that you can get.

Note that the users won't be able to stop the addin from being loaded and executed when launching Excel with Virus Protection turned on etc, as Virus Protection can't/doesn't apply to files in the StartUp directories.

If you need any more help (or fancy awarding more points), don't hesitate to get in touch.

0
 
LVL 23

Expert Comment

by:ahammar
ID: 2255128
Noggy:
Although I have never used a class module before (but I think I am going to play around with your code a little), that sounds like a good idea. If you want, you can take this one over.

I only have one small thing to add:
I think if the users having access to the startup directories will cause a problem, and you really wanted to go to extremes, then createing an executable file would be the best you could get.
If you really, really wanted to go to extremes, the executable could be made completely invisible in every way, and even be made so that upon openning it would delete the shortcut to itself in the startup windows folder, and recreate it again just before windows shut down. That way it is entirely impossible to not have it running all the time. The user would never find a trace of it anywhere. The only way for anyone, including the creater, to shut if off would be to stop windows from booting with the F8 key or whatever it is, and delete the shortcut from dos, before windows openned.
I played a prank on a co-worker this way once, and I might add he knew all the tricks, but he was never able to figure it out.

So if your ever up to playing a prank on someone, try that one.
Just create an executable that can't be seen or terminated that does something odd like pop up a message box every so often or something and bury it somewhere (a network drive works good).
Put a shortcut to it in the startup folder.
Since the only way to terminate the executable would be to shut down windows, you could have it delete the shortcut to itself right after it opens (when windows starts), and put it back again when it closed,(which wouldn't be until windows shut down), that way there wouldn't even be a shortcut to ever be seen. No trace anywhere, (especially if the executable is on a network drive)!
 If by chance the user was able to find where you buried the executable, (chances are he wouldn't because he wouldn't even know one existed), he wouldn't be able to delete or move it anyway, because it would be running.

It will work, trust me and you'll get a good laugh!
Only thing is, it is very difficult to make an executable create a shortcut in Windows startup folder. The entire process takes a lot of code.

I guess that didn't turn out to be so small.

Anyway, Sorry to get off the subject.

As your idea is still probably the most appropriate here, feel free to take over, and grab the points. I've got plenty other things to do.


Thank goodness there is not a chapter 2.........:-)

Cheers!
ahammar
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2255156
ahammar - Thanks for the invite :-) to take over . I feel a bit guilty though after your "War and Peace" entries. Are you trying to get EE to invest in bigger servers by filling their current ones up :-) ?

Code modules are good things. I've been using them for a while and are good containers for related functions etc - e.g. I have one that splits up/combines filenames and paths, as well as reporting whether the file exists and what the short (DOS) filename would be.

All you do then is import it into the workbooks etc. where you want to include it. Also, if you write them well, you can even import and use them in other VB apps (e.g. Access, Word) without having to change any of the code.

As to your EXE idea, I've never tried anything like that myself but it does sound a bit overboard - though undoubtedly it'll work.

And as to your prank, you mischievous little devil :-) . You obviously have too much time on your hands (when they're not creating novels on EE).
0
 
LVL 23

Expert Comment

by:ahammar
ID: 2255184
Noggy:
When you say code modules, I assume you mean Class modules. I've used lots and lots of code modules, and the exporting and importing, (and yes is that ever handy)  jut not Class modules.

Don't feel at all guilty, I don't mind at all. I have so many things to do, I will never get them all done. In fact, I was hoping you would take over.

Cheers!
ahammar
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2256021
ahammar - Oops, yeah, I did mean Class Modules :-) Slippery keyboard again.
0
 

Author Comment

by:ccbailey
ID: 2258548
Wow - you guys are great!  I'll try out Noggy's method - it looks like it would be much easier than the other.  I've actually used class modules before - I used them to capture the save event in Word.  It may take me a little while to write all the code, but hopefully you'll hear back from me with a grade sometime today.

Thanks again!
Christy
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2258598
I take it that you mean you need to write all the code for the properties you are reporting? Otherwise, to check that it works, you will not need to write any more code - I put in all the code that you will need.

Either way, later on today is fine :-) . Happy coding.
0
 
LVL 2

Expert Comment

by:Inspector
ID: 2259321
Well because there is so much information here, I didn't read all of the other comments. The Laroux (& other variants) virus managed to pass itself to new files. By using the Auto_Open routine to set the OnSheetActivate event to run code. Perhaps you could accomplish the same sort of thing, but store which files are open & which ones are closed. to define whether or not to run the code.

Sub Auto_Open()
    Application.OnSheetActivate "CheckFiles"
End Sub

Sub CheckFiles()
    'If file doesn't exist then...

End sub
0
 

Author Comment

by:ccbailey
ID: 2259359
This seems to work just as you say.  Thanks for your help.

Christy
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Are you looking to start a business? Do you own and operate a small company? If so, here are some courses you need to take before you hire a full-time IT staff.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question