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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 11:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com