Menu problem in Excel

[Amit]

Hello group,

I'm trying to hide a user-defined menu item in Menu bar during
following event:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

Application.Commandbar("My Menu").Visible =
False
End Sub

Which causes run time error '91' or "Object variable or With block
variable not set"


Does anybody know how can fix this?

Your help will be appreciated.

Thanks,
akohan

 

[Ron de Bruin]

Hi Amit

If it is the Worksheet Menu Bar then this will not work
Look for a code examples on this page
http://www.rondebruin.nl/menuid.htm

 

[Amit]

Hello Ron,

Thank you for your response. I followed your suggestion however, I'm
using

ThisWorkbook.Commandbars.FindControl(ID:=300002).Enabled = False just
to see how it works but I get error message:


Object variable or With block variable not set - error code 91

Any advice?

Thank you.
amit

 

[Ron de Bruin]

Hi Amit

To many zero's
Use 30002 to test

But you must use the name

Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Enabled = False

 

[Amit]

I already had used the name but it didn't work! one thing is that I'm
trying to hide or disable the "My Menu" not sub-items.

 

[Ron de Bruin]

Where is "My Menu" ?
In the same row as File, Edit, ..................

 

[Amit]

No, it is a made up menu by my code (VBA). It is located next to Help
menu. between Help and Window menus.

 

[Ron de Bruin]

Hi

Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Enabled = False

Then this will work
Be sure that the name is correct

I use 1 it this example but it the same as Worksheet Menu Bar
This example add a item and delete it

Sub MenuBar_Item()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
With Application.CommandBars(1)
With .Controls.Add(Type:=msoControlButton, before:=1)
.Style = msoButtonCaption
.Caption = "&Hi"
.OnAction = ThisWorkbook.Name & "!TestMacro"
End With
End With
End Sub

Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub