Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through items in a submenu
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(befo=CustomMenu.Control s("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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through items in a submenu
Sorry for double post.
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through items in a submenu
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" wrote in message oups.com... 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(befo=CustomMenu.Control s("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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through items in a submenu
Thanks, worked great. And for everyone, I needed to add .controls on
the end of the for each ctrl2 line. Bob Phillips wrote: 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" wrote in message oups.com... 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(befo=CustomMenu.Control s("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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add a submenu to a submenu? | New Users to Excel | |||
Looping through items in a submenu | Excel Programming | |||
looping through items in a dictionary | Excel Programming | |||
XML submenu | Excel Discussion (Misc queries) | |||
Add submenu Items | Excel Programming |