Auto_xxxx() versus event handlers

[Greg Truby]

By the time I started using a lot of VBA, the Autpen() and Auto_Close() methods' use was waning, supplanted by the _Open() and _BeforeClose() event handlers for the workbook object. So I never really got into the habit of using them.

However, I've been reading Bullen, Bovey & Green's book Professional Excel Development<SUP>1</SUP> and they use Autpen() quite a bit. So I've been plinkin' around a bit trying to suss out the differences. Do I have this more or less correct?

If I put in both an Autpen() in a standard module and a Workbook_Open()in the WB's code module both execute. But if I were to have Application.EventsEnabled set to FALSE; the Autpen() still executes upon opening the workbook whereas the Workbook_Open() will not?² And that since the open event handler runs before the Autpen() subroutine, using the Autpen() to insure that events are enabled does not cause the WB_Open() event handler to fire.

Auto_Close() runs even if EnableEvent = FALSE and (of course) WB_BeforeClose(...) does not fire. HOWEVER, just to make things interesting: if EnableEvents = TRUE and I hold down the SHIFT key while opening, neither of the open procedures fires. Whereas if I hold don the SHIFT key when closing, the _BeforeClose() event still gets raised; but the Auto_Close() does not run.

Does that about sum up the differences? Or are there more differences that I should be aware of?





<HR>
1. If you have developed a few complex projects and more-or-less can run back through Walkenbach's PowerProgramming with VBA book without seeing too much that you've forgotten; then this is the book to take you to the next level.

2. Both scenarios assume that macros are enabled.

 

[Greg Truby]

• Love the avatar!
• What you are describing is normally referred to as a "dictator app". And you need to be a pretty darned good programmer to consider toppling the existing user interface and replacing it with your own. There are books on the subject of creating dictator applications, but they are pretty advanced reading.
• I would suggest that you take a good hard look at the options available to you through using Excel's built-in worksheet protection tools before going to the trouble of trying to create a dictator app.

 

[yytsunamiyy]

re a) - thanks
re b) - not being a pretty darned good programmer (just having started a few months ago) I might indeed not want to do that

re c) - all that is in place, I even have a rudimentary - and totally unsecure but sufficient for the ppl here- User login / accessrights administration , sheets the the user shouldnt touch at all are very hidden and protected and so on. My biggest beef is that ppl might save a copy on their local machine rather than in the serverdirectory dictated in my save macro. - there are two different ones, because depending on the type of changes made I have to save copies of the file in different directories. is there any way other than the workbook_beforesave event calling my macro(s) directly to capture userinteraction with the Save / Save as buttons and assign my macro to this event?

 

[Zack Barresse]

Greg

If you haven't worked with XL 2007 (and I guess you haven't much since you still seem to be sane ) then the CommandBars collection appears on the Add-Ins tab (or not if you run my code above). So maybe I have spotted an anomaly since it does work seamlessly in XL 2007 and I'll now have to get 'em back

There's a lot more that's strange with XL 2007 (try recording a macro with charts or shapes for example) but I'll stop hijacking your thread and watch from the sidelines.

Charts were more or less broken in 2007.

The CommandBars collection is still exposed as it was in 97, the change is in the UI, not the OM. So while we still have programmability to the CommandBars collection, they will show up (as per the Excel built-in functionality in 2007) on the Add-Ins tab. Just do not get confused that the Add-Ins tab is a parent collection [of sorts] the the CommandBars object. We do not have programmatic access to the [Ribbon] UI in 2007 (through VBA that is). Unfortunately that is.