Macro doesn't work when used as Workbook Open event

[Phil]

Hi all,

I'm getting very frustrated by a piece of code I'm using that creates a
command bar when a worbook is opened, adds buttons to it, and then
deletes it on closing. (I admit I filched the code from here!)

It works perfectly when run as a standard macro, but when run as a
Workbook _Open event it fails at the first command.

Here's what it looks like (as far as the first line where it fails.....

Object Explicit
Private Sub Workbook_Open()
'Creates a toolbar named "Pensions"
Dim Pensions As CommandBar
Dim Button As CommandBarButton


' Create the toolbar
Set Pensions = CommandBars.Add

The debugger indicates that it's failing at the Set Pensions and the
mesage is
"Object variable or With block variable not set"

I thought I'd done that with Dim Pensions As CommandBar

Anyway, as I said, this code works perfectly if I run it as a normal
macro, and ONLY fails if I change it to a Workbook Open event!

Any help would be greatly appreciated and might just save my sanity!!!!

Thanks in advance

Phil

 

[Bob Phillips]

Set Pensions = Application.CommandBars.Add


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Phil]

That worked! Thanks Bob. Still can't understand why it was OK as a
manually-run macro though.