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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,813
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,530
Messages
5,837,899
Members
430,520
Latest member
VenkateshRajaganesan

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