Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Visible Controls in CommandBars("Worksheet Menu Bar")
I would like to hide all controls in the Worksheet Menu Bar except the File
Menu. This is the code I plan to use but it doesn't work. I am getting an error: Type Mismatch. Can someone make sense of this? Sub ListUserControls() Dim ctrl As Control Dim rw As Long rw = 2 Error For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If ctrl.Visible = True Then Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 End If Next ctrl End Sub. Question #2: In the immediate window I returned a Control.Caption = A&ction. I do not see it in the Worksheet Menu Bar, so I set its Visible property to True and I still couldn't see it. Anyone have any ideas of what this is and what it is used for? -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Visible Controls in CommandBars("Worksheet Menu Bar")
Excel choked on the declaration of ctrl "As Control". I changed that to "As
CommandBarControl", and it worked. Sub ListUserControls() Dim ctrl As CommandBarControl Dim rw As Long rw = 2 For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If ctrl.Visible = True Then Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 End If Next ctrl End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RyanH" wrote in message ... I would like to hide all controls in the Worksheet Menu Bar except the File Menu. This is the code I plan to use but it doesn't work. I am getting an error: Type Mismatch. Can someone make sense of this? Sub ListUserControls() Dim ctrl As Control Dim rw As Long rw = 2 Error For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If ctrl.Visible = True Then Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 End If Next ctrl End Sub. Question #2: In the immediate window I returned a Control.Caption = A&ction. I do not see it in the Worksheet Menu Bar, so I set its Visible property to True and I still couldn't see it. Anyone have any ideas of what this is and what it is used for? -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Visible Controls in CommandBars("Worksheet Menu Bar")
Yep that was it! Do you have any idea on Question #2?
This isn't making sense to me: In the immediate window: ?Application.CommandBars("Worksheet Menu Bar").Controls.Count = 12 But when I use this code below it only lists 11 controls, why? Sub ListUserControls() Dim ctrl As CommandBarControl Dim rw As Long rw = 2 For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 Next ctrl End Sub Here is the list below: &File &Edit &View &Insert F&ormat &Tools &Data A&ction <<==What is this? &Window Macola Enterprise Suite &Help Ado&be PDF I tried this line below in the immediate window and the A&ction will not show, do you know what it is? Application.CommandBars("Worksheet Menu Bar").Controls(8).Visible = True -- Cheers, Ryan "Jon Peltier" wrote: Excel choked on the declaration of ctrl "As Control". I changed that to "As CommandBarControl", and it worked. Sub ListUserControls() Dim ctrl As CommandBarControl Dim rw As Long rw = 2 For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If ctrl.Visible = True Then Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 End If Next ctrl End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RyanH" wrote in message ... I would like to hide all controls in the Worksheet Menu Bar except the File Menu. This is the code I plan to use but it doesn't work. I am getting an error: Type Mismatch. Can someone make sense of this? Sub ListUserControls() Dim ctrl As Control Dim rw As Long rw = 2 Error For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If ctrl.Visible = True Then Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 End If Next ctrl End Sub. Question #2: In the immediate window I returned a Control.Caption = A&ction. I do not see it in the Worksheet Menu Bar, so I set its Visible property to True and I still couldn't see it. Anyone have any ideas of what this is and what it is used for? -- Cheers, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Visible Controls in CommandBars("Worksheet Menu Bar")
I have heard of this menu, and I used to know something about it. But it's
rather obscure, and it's been a long while. If it helps, this menu has no menu items. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RyanH" wrote in message ... Yep that was it! Do you have any idea on Question #2? This isn't making sense to me: In the immediate window: ?Application.CommandBars("Worksheet Menu Bar").Controls.Count = 12 But when I use this code below it only lists 11 controls, why? Sub ListUserControls() Dim ctrl As CommandBarControl Dim rw As Long rw = 2 For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 Next ctrl End Sub Here is the list below: &File &Edit &View &Insert F&ormat &Tools &Data A&ction <<==What is this? &Window Macola Enterprise Suite &Help Ado&be PDF I tried this line below in the immediate window and the A&ction will not show, do you know what it is? Application.CommandBars("Worksheet Menu Bar").Controls(8).Visible = True -- Cheers, Ryan "Jon Peltier" wrote: Excel choked on the declaration of ctrl "As Control". I changed that to "As CommandBarControl", and it worked. Sub ListUserControls() Dim ctrl As CommandBarControl Dim rw As Long rw = 2 For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If ctrl.Visible = True Then Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 End If Next ctrl End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RyanH" wrote in message ... I would like to hide all controls in the Worksheet Menu Bar except the File Menu. This is the code I plan to use but it doesn't work. I am getting an error: Type Mismatch. Can someone make sense of this? Sub ListUserControls() Dim ctrl As Control Dim rw As Long rw = 2 Error For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If ctrl.Visible = True Then Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption rw = rw + 1 End If Next ctrl End Sub. Question #2: In the immediate window I returned a Control.Caption = A&ction. I do not see it in the Worksheet Menu Bar, so I set its Visible property to True and I still couldn't see it. Anyone have any ideas of what this is and what it is used for? -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel "text to columns needs" a menu-visible on/off switch | Setting up and Configuration of Excel | |||
It's not CommandBars("Cell"), right-click menu! | Excel Programming | |||
CommandBars("Worksheet Menu Bar").Controls("Tools").Enabled = Fals | Excel Programming | |||
Can't Change Commandbars("Worksheet Menu Bar") | Excel Programming |