MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Error 91: Object variable not set


Posted by P Lewis on January 13, 2002 8:47 PM

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!


Posted by Ivan F Moala on January 14, 2002 3:28 AM

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