Deactivating a sub menu

klmsurf

New Member
Joined
Jun 13, 2008
Messages
3
Using existing code for making menus, I generated a menu called 'File' with a sub menu called 'SubFile'. This works fine. I then tried to deactivate 'Sub File' using

Application.CommandBars("File").Controls("Sub File").Enabled = False

but I constantly got an error. Here is the code with only the essentials. Could someone point out the problem, thanks.


Sub check()

Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("File").Delete
On Error GoTo 0

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")

iHelpMenu = cbMainMenuBar.Controls("Help").index

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)

cbcCutomMenu.Caption = "File"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "SubFile"
.OnAction = "MyMacro1"
End With

'\This results in an error
Application.CommandBars("File").Controls("SubFile").Enabled = False

End Sub
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
File is a Control, not a Commandbar. Why not just disable the control as you add it, or use the parent control reference you already have?
Code:
Sub check()
   Dim cbMainMenuBar As CommandBar
   Dim iHelpMenu As Integer
   Dim cbcCustomMenu As CommandBarControl
   
   Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
   
   On Error Resume Next
   Application.CommandBars("Worksheet Menu Bar").Controls("MyFile").Delete
   On Error GoTo 0
   
   iHelpMenu = cbMainMenuBar.Controls("Help").Index
   
   Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
   
   cbcCustomMenu.Caption = "MyFile"
   
   With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
      .Caption = "SubFile"
      .OnAction = "MyMacro1"
' could just use this
'      .Enabled = False
   End With
   
   ' or use the reference to the parent control
   cbcCustomMenu.Controls("SubFile").Enabled = False
   
End Sub
 

klmsurf

New Member
Joined
Jun 13, 2008
Messages
3
Thank you rorya. I've been using VBA for about one month now so I'm still a bit unsure on how to do stuff, but I'm learning. Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top