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

Set Pensions = Application.CommandBars.Add


--
HTH

Bob Phillips

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

"Phil" wrote in message
ups.com...
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

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



On Oct 20, 1:05 pm, "Bob Phillips" wrote:
Set Pensions = Application.CommandBars.Add

--
HTH

Bob Phillips

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

"Phil" wrote in oglegroups.com...



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- Hide quoted text -- Show quoted text -