Deactivating a sub menu

[klmsurf]

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

 

[RoryA]

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?

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]

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.