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.

 

[Oaktree]

Hey ya, Greg.

http://office.microsoft.com/en-us/excel/HA010346281033.aspx may help.

Key stuff:

Recording an Autpen macro has a number of limitations:

Many actions you may want to perform cannot be recorded.
If the workbook in which you save the Autpen macro already contains a VBA procedure in its Open event, the VBA procedure for the Open event will override all actions in the Autpen macro.
An Autpen macro is ignored when a workbook is opened programmatically by using the Open method.
An Autpen macro runs before any other workbooks are opened. Therefore, if you record actions you want Excel to perform on the default Book1 workbook or a workbook loaded from the XLStart folder, the Autpen macro will fail when you restart Excel because it runs before the default and start-up workbooks are opened.

 

[Greg Truby]

Much obliged, Matt. Will print that out and read it straight away.

 

[VoG]

An interesting thread that I will be watching

Having started with VBE with Excel 5.0 (last century) I was a fan of Autpen and used it a lot. However this board showed me the error of my ways and I 'graduated' to using Workbook_Open().

However, try this in a version of Excel pre-2007

Private Sub Workbook_Open()
Dim cBar As CommandBar
For Each cBar In CommandBars
    cBar.Enabled = False
Next cBar
Application.DisplayFormulaBar = False
End Sub

In my experience this will fail on

For Each cBar In CommandBars - Run time error '424' object required

suggesting to me that at the time the code runs CommandBars have not been initialized.

However, the following in a regular module runs just fine

Private Sub Auto_Open()
Dim cBar As CommandBar
For Each cBar In CommandBars
    cBar.Enabled = False
Next cBar
Application.DisplayFormulaBar = False
End Sub

N.B. Before running the above, it is a good idea to have the reverse script to hand

Sub ShowCommandbars()
Dim cBar As CommandBar
For Each cBar In CommandBars
    cBar.Enabled = True
Next cBar
Application.DisplayFormulaBar = True
End Sub

 

[Greg Truby]

FYI - simply changing that to qualify the parent for the commandbars collection fixes that bug:

Private Sub Workbook_Open()
    Dim cb As CommandBar
    
    For Each cb In Application.CommandBars
        Debug.Print "wb_open", cb.Name
        cb.Enabled = False
    Next cb 
End Sub

 

[VoG]

Doh! And I thought I'd discovered something interesting

 

[Greg Truby]

Doh! And I thought I'd discovered something interesting

Well, you may have. It is interesting if this now works in XL2007. Why the different behavior?

 

[VoG]

Well, you may have. It is interesting if this now works in XL2007. Why the different behavior?

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.

 

[Greg Truby]

...I'll stop hijacking your thread...

There are - perhaps - some that would call such a 'jack "karma".

 

[yytsunamiyy]

Can I be the hijacker now?

Please?

This code works lovely for removing the commandbars from excel pre-2007 - do you know of something similiar to remove the ribbon(s) of XL 2007?

Private Sub Workbook_Open()
    Dim cb As CommandBar
    
    For Each cb In Application.CommandBars
        Debug.Print "wb_open", cb.Name
        cb.Enabled = False
    Next cb 
End Sub

Basically I want to stop the User from doing anything but formatting Fonts, colors and stuff like that - I only want him to save using my save-routine and so on...

I was trying with

application.fullscreen = true

but that only hides the ribbon(s) - when you resize the window / click on Excel the ribbon is unhidden and fullscreen mode is false...

plus I have, at one point, to set the windowstate to xlnormal to allow the user to simultaniously view information on a website and enter it in my userform.