Trouble with Workbook_Open()
 

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

Trouble with Workbook_Open()
 

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/

Trouble with Workbook_Open()
 

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/

Trouble with Workbook_Open()
 

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/

Trouble with Workbook_Open()
 

Thank you for all the feedback.

All these solutions appear to work.

Regards,

-Sila

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