Looping through items in a submenu

[John Fuller]

So I'm trying to loop through sub menu items and check to see if they
have a certain tag, and if they do disable them.

Code to create the menu (I've taken some of it out):
Sub Create_Menu()
Dim MainMenuBar As CommandBar
Dim CustomMenu As CommandBarPopup
Dim CustomMenuItem As CommandBarControl
Dim CustomMenuSub As CommandBarPopup

Call Delete_Menu

' setup toggle on normal file menu
Set CustomMenu = Application.CommandBars(1).Controls("File")
CustomMenu.Controls("Exit").BeginGroup = True
Set CustomMenuItem =
CustomMenu.Controls.Add(before:=CustomMenu.Controls("Exit").Index)
With CustomMenuItem
.Caption = "&Toggle TP Menu"
.OnAction = "Toggle_TP_Menu"
.BeginGroup = True
End With

Application.CommandBars.Add Name:="Throughput Model",
temporary:=False, Position:=msoBarTop, MenuBar:=True
Application.CommandBars("Throughput Model").Visible = True

Set MainMenuBar = Application.CommandBars("Throughput Model")

'Throughput Model Menu
Set CustomMenu = MainMenuBar.Controls.Add(Type:=msoControlPopup)
CustomMenu.Caption = "&File"

'Open Sub Menu
Set CustomMenuSub = CustomMenu.Controls.Add(Type:=msoControlPopup)
CustomMenuSub.Caption = "&Open"

Set CustomMenuItem = CustomMenuSub.Controls.Add
With CustomMenuItem
.Caption = "Foundry"
.OnAction = "'Open_File 2, ""Please Select the Foundry
File.""'"
.Tag = "Foundry.xls"
End With

Set CustomMenuItem = CustomMenuSub.Controls.Add
With CustomMenuItem
.Caption = "Wax CMM"
.OnAction = "'Open_File 3, ""Please Select the Wax CMM
File.""'"
.Tag = "Wax_CMM.xls"
End With

'Switch to Sub Menu
Set CustomMenuSub = CustomMenu.Controls.Add(Type:=msoControlPopup)
CustomMenuSub.Caption = "S&witch To"

Set CustomMenuItem = CustomMenuSub.Controls.Add
With CustomMenuItem
.Caption = "&Throughput File"
.OnAction = "Activate_This"
End With

Set CustomMenuItem = CustomMenuSub.Controls.Add
With CustomMenuItem
.Caption = "&Foundry"
.OnAction = "'Switch_To,
ThisWorkbook.Worksheets(""Resources"").Cells(2, 2).Value"
.Tag = "Foundry.xls"
.Enabled = False
.BeginGroup = True
End With

Set CustomMenuItem = CustomMenuSub.Controls.Add
With CustomMenuItem
.Caption = "&Wax CMM"
.OnAction = "'Switch_To,
ThisWorkbook.Worksheets(""Resources"").Cells(3, 2).Value"
.Tag = "Wax_CMM.xls"
.Enabled = False
End With
End Sub

As you can see there is an Open sub menu and a Switch To sub menu
(normally there are several more sub menus).

I'm trying to write a sub to go through and enable everything (which
works), and then look in the open sub menu and disable the menu items
in there with the myTag tag. Here's the code I have so far, but I
can't get the loop through the open sub menu. I've tried ctrl2 as a
comandbarcontrol as well, but that doesn't seem to work either. Any
help is appreciated.

Sub Enable_On_Open(myTag)
'enable all commands
Dim Ctrl As CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(Tag:=myTag)
Ctrl.Enabled = True
Next Ctrl

'disable in the open submenu
Dim Ctrl2 As CommandBarButton
For Each Ctrl2 In Application.CommandBars("Throughput
Model").Controls("File").Controls("Open")
If Ctrl2.Tag = myTag Then
Ctrl2.Enabled = False
End If
Next Ctrl2
End Sub

Thanks,
John

 

[John Fuller]

Sorry for double post.

 

[Bob Phillips]

Sub Enable_On_Open(myTag)
'enable all commands
Dim Ctrl As CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(Tag:=myTag)
Ctrl.Enabled = True
Next Ctrl

'disable in the open submenu
Dim Ctrl2 As CommandBarButton
For Each Ctrl2 In Application.CommandBars("Throughput
Model").Controls("File").Controls("Open").Controls
If Ctrl2.Tag = myTag Then
Ctrl2.Enabled = False
End If
Next Ctrl2
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[John Fuller]

Thanks, worked great. And for everyone, I needed to add .controls on
the end of the for each ctrl2 line.