I have a custom menu bar ("DTSheet") with three popups each of whihc ha
a number of (sub)menu items. Each of the three menu bar popups has a
index which I can find using:

For Each cntrl In CommandBars("DTSheet").Controls
If cntrl.Visible = True Then MsgBox (cntrl.Index)
Next cntrl

However, this only finds the main menu bar controls, it does not fin
the (sub)menu item controls. I had hoped to beat it by using th
following:

For Each cntrl In CommandBars("DTSheet").Controls(1)
If cntrl.Visible = True Then MsgBox (cntrl.Index)
Next cntrl

to find the controls in each menu bar popup in turn. But this return
an "Object does not support this proprty or method" error.

Any help much appreciated

Mic

--
micklloy
-----------------------------------------------------------------------
micklloyd's Profile: http://www.excelforum.com/member.php...fo&userid=3069
View this thread: http://www.excelforum.com/showthread.php?threadid=50483

I think a For Each loop will work.
This is a code fragment I have for a popup menubar in a userform, but I
think it will work in your situation:

Dim oCBButton1 As CommandBarControl
Dim oCBButton2 As CommandBarControl
Dim oCBButton3 As CommandBarControl
Dim lButtonCount As Long

For Each oCBButton1 In cb2.Controls
'top level buttons
'-----------------
If oCBButton1.Type = msoControlButton Then
lButtonCount = lButtonCount + 1
Else 'If oCBButton1.Type = msoControlButton
For Each oCBButton2 In oCBButton1.Controls
'second level buttons
'--------------------
If oCBButton2.Type = msoControlButton Then
lButtonCount = lButtonCount + 1
Else
For Each oCBButton3 In oCBButton2.Controls
'third level buttons
'-------------------
If oCBButton3.Type = msoControlButton Then
lButtonCount = lButtonCount + 1
End If
Next 'For Each oCBButton3 In oCBButton2.Controls
End If 'If oCBButton2.Type = msoControlButton
Next 'For Each oCBButton2 In oCBButton1.Controls
End If 'If oCBButton1.Type = msoControlButton
Next 'For Each oCBButton1 In cb2.Controls

RBS

"micklloyd" wrote
in message ...

I have a custom menu bar ("DTSheet") with three popups each of whihc has
a number of (sub)menu items. Each of the three menu bar popups has an
index which I can find using:

For Each cntrl In CommandBars("DTSheet").Controls
If cntrl.Visible = True Then MsgBox (cntrl.Index)
Next cntrl

However, this only finds the main menu bar controls, it does not find
the (sub)menu item controls. I had hoped to beat it by using the
following:

For Each cntrl In CommandBars("DTSheet").Controls(1)
If cntrl.Visible = True Then MsgBox (cntrl.Index)
Next cntrl

to find the controls in each menu bar popup in turn. But this returns
an "Object does not support this proprty or method" error.

Any help much appreciated

Mick


--
micklloyd
------------------------------------------------------------------------
micklloyd's Profile:
http://www.excelforum.com/member.php...o&userid=30696
View this thread: http://www.excelforum.com/showthread...hreadid=504835

Thanks RBS

Enough here to enable me to get what I want


--
micklloyd
------------------------------------------------------------------------
micklloyd's Profile: http://www.excelforum.com/member.php...o&userid=30696
View this thread: http://www.excelforum.com/showthread...hreadid=504835