Excel Error 91: Object variable not set

I am new to VBA. I recently entered code that keeps generating Error #91 when I enter the code in "ThisWorkbook". Yet, the code seems to run fine in other modules.

Here's the code:
Private Sub Workbook_SheetActivate(ByVal Sh as Object)
If Sh.Name = "XXXXXX" then
Dim TBar as CommandBar
Set TBar = CommandBars.Add
End if
End Sub

The error occurs on the line "Set TBar = CommandBars.Add"

Why? (Incidently, what I am ultimately trying to do is to create a unique toolbar for each worksheet that is created when the worksheet is activated.

Please advise.

Thanks!

Change Set TBar = CommandBars.Add
to
Set TBar = Application.CommandBars.Add

Better if you define the commandbar eg

Set TBar = Application.CommandBars.Add (Name:="Custom", Position:=msoBarTop, _
Temporary:=True)

Look @ online help

HTH

Ivan

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Error 91: Object variable not set

Re: Error 91: Object variable not set