Hello,

I seem to be having trouble with the following code:

Sub Workbook_Open()

Dim CustomMenu As Object

Set CustomMenu = CommandBars("Worksheet Men
Bar").Controls.Add(Type:=msoControlPopup, Befo=10)
With CustomMenu
.Caption = "CustomMenu"
End With

End Sub

Which keeps giving me:

Run-time error '91':
Object or with variable not set

for some reason. However, this code works just fine when saved in it
own separate module in a workbook.

I am new to Excel programming, but this seems strange. Could someon
tell me what I'm doing wrong?

Thanks in advance

--
Message posted from http://www.ExcelForum.com

Hi Silas

Use this

Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("CustomMenu").Delete
On Error GoTo 0
With Application.CommandBars("Worksheet Menu Bar")
With .Controls.Add(Type:=msoControlPopup, Befo=10)
.Caption = "CustomMenu"
.OnAction = ThisWorkbook.Name & "!TestMacro"
End With
End With
End Sub


Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("CustomMenu").Delete
On Error GoTo 0
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Silas Mercer " wrote in message ...
Hello,

I seem to be having trouble with the following code:

Sub Workbook_Open()

Dim CustomMenu As Object

Set CustomMenu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Befo=10)
With CustomMenu
Caption = "CustomMenu"
End With

End Sub

Which keeps giving me:

Run-time error '91':
Object or with variable not set

for some reason. However, this code works just fine when saved in its
own separate module in a workbook.

I am new to Excel programming, but this seems strange. Could someone
tell me what I'm doing wrong?

Thanks in advance.


---
Message posted from http://www.ExcelForum.com/

You need to qualify Commandbars with application when used in the
ThisWorkbook module.

Sub Workbook_Open()

Dim CustomMenu As Object

Set CustomMenu = Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Befo=10)
With CustomMenu
Caption = "CustomMenu"
End With

End Sub

--
Regards,
Tom Ogilvy


"Silas Mercer " wrote in
message ...
Hello,

I seem to be having trouble with the following code:

Sub Workbook_Open()

Dim CustomMenu As Object

Set CustomMenu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Befo=10)
With CustomMenu
Caption = "CustomMenu"
End With

End Sub

Which keeps giving me:

Run-time error '91':
Object or with variable not set

for some reason. However, this code works just fine when saved in its
own separate module in a workbook.

I am new to Excel programming, but this seems strange. Could someone
tell me what I'm doing wrong?

Thanks in advance.


---
Message posted from http://www.ExcelForum.com/

Change to:

Application.CommandBars(...

It appears that the next level object is the Workbook object (which
does make sense). Therefore, what excel is interpreting is:

Workbook.CommandBars

which is not valid.

Try running this

Msgbox Name

from the workbook code section and you get the workbook name. From the
worksheet code you get the sheet name and from a module you get
nothing...

K


---
Message posted from http://www.ExcelForum.com/

Thank you for all the feedback.

All these solutions appear to work.

Regards,

-Sila

--
Message posted from http://www.ExcelForum.com