VB - Macro to list all controls in the Commandbar.

[LABKHAND]

Hi All,

Through VB code, I have added a new custom menu to the Commandbar("Worksheet
Menu Bar"). This custom menu has several controls (menus/submenus). I am
trying to write a VB macro that gets me name of each control (menu/submenus)
in this custom menu item. Any help would be appreciated.

Thx


I am trying to write a vb code to list name of all Excel commandbars and
their associated controls (menus/submenus). For example I have created a
custom menu in Excel with several sub-menus in it. I need to get name of
each control in this custom menu item in the "WorkSheet Menu Bar".

 

[Ron de Bruin]

Install this add-in
http://www.erlandsendata.no/english/index.php?d=endownloadcommandbars

 

[Ron de Bruin]

The name is
CommandBar Tools



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Mike H]

Hi,

Try this

Sub MenuOptions()
Dim ctrl As CommandBarControl
Dim MenOption As CommandBarControl
Dim rw As Long
rw = 2
For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls
Sheets("Command Bars").Cells(rw, "B") = _
WorksheetFunction.Substitute(ctrl.Caption, "&", "")
rw = rw + 1
For Each MenOption In ctrl.Controls
Cells(rw, "B").HorizontalAlignment = xlRight
Sheets("Command Bars").Cells(rw, "B") = _
WorksheetFunction.Substitute(MenOption.Caption, "&", "")
rw = rw + 1
Next
Next ctrl
End Sub

Mike

 

[LABKHAND]

Mike,

Thanks for your help. I need to do further processing and data manupulation
after I get names of all controls under my custom menu. so if my custom
menu on the "Worksheet Menu Bar" is called "A" and this menu has a button
called "B" and a submenu called "C". furthe rmore there are more controls
on the submenu "C"...
so as you see this linkage can be very long and I need to trace this linkage
all the way in order to have all the control names.

So your code does not help me. I need to trace the controls linkage in my
cutom menu to get all names.

 

[LABKHAND]

Ron,

Can not use any ADD-ins since i need to further manipulate and write my own
code...

 

[LABKHAND]

So that it is easier to see what i am doing...the following code gets me to
the level 2 of the menus:

Dim cbarMenu As CommandBar
Dim cbarControl As CommandBarControl
Dim aa As CommandBarControl
Dim controlname As String

controlname = "Format"
Set cbarMenu = CommandBars("Worksheet Menu Bar")

MsgBox cbarMenu.Controls(controlname).Controls.Count

For Each cbarControl In cbarMenu.Controls(controlname).Controls
MsgBox cbarControl.Caption

Set aa = CommandBarsControl(cbarControl.Caption)
Next


I need to change this code so that it is recursive and goes through all
levels of controls under my custom menu. I am having a tough time getting
that info!

 

[Jim Cone]

Download my "Custom Menu Items Only" workbook from...
http://excelusergroup.org/media/
The project is unlocked.
--
Jim Cone
Portland, Oregon USA





"LABKHAND"
<[email protected]>
wrote in message
Hi All,

Through VB code, I have added a new custom menu to the Commandbar("Worksheet
Menu Bar"). This custom menu has several controls (menus/submenus). I am
trying to write a VB macro that gets me name of each control (menu/submenus)
in this custom menu item. Any help would be appreciated.
Thx


I am trying to write a vb code to list name of all Excel commandbars and
their associated controls (menus/submenus). For example I have created a
custom menu in Excel with several sub-menus in it. I need to get name of
each control in this custom menu item in the "WorkSheet Menu Bar".

 

[LABKHAND]

Thanks all for your quick response....I figured it out some other way. Good
day.