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".

Install this add-in
http://www.erlandsendata.no/english/...oadcommandbars




--

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




"LABKHAND" 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".

The name is
CommandBar Tools



--

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




"Ron de Bruin" wrote in message ...

Install this add-in
http://www.erlandsendata.no/english/...oadcommandbars




--

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




"LABKHAND" 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".

Ron,

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

"Ron de Bruin" wrote:


Install this add-in
http://www.erlandsendata.no/english/...oadcommandbars




--

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




"LABKHAND" 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".

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" wrote:

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".

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.


"Mike H" wrote:

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" wrote:

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".

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!



"Mike H" wrote:

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" wrote:

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".

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





"LABKHAND"

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".

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

"LABKHAND" wrote:

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".