Creating sub menu in Command bar

[Anders]

Hi all,

I would like to create a command bar that contains a menu with sub menues.
Do anyone know how to do this?

Menu
-------
Menu Item1
Menu Item2 -> Sub menu item -> Sub sub menu item

Regards
Anders

[Kaak]

Function CreateMenu()

Dim HelpMenu As CommandBarControl
Dim MenuItem As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim SubMenuItem As CommandBarButton

Call DeleteMenu("TemplateMenuBar")

Set HelpMenu = CommandBars(1).FindControl(ID:=30010)

If HelpMenu Is Nothing Then

Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, temporary:=True)

Else

Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=HelpMenu.Index, temporary:=True)

End If

NewMenu.Caption = "TemplateMenuBar"

'------------------------------------------------------------------------------------------------------------------------

Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)

With MenuItem

.Caption = "MenuItem1"
.OnAction = "Macro1"

End With

'------------------------------------------------------------------------------------------------------------------------

Set MenuItem = NewMenu.Controls.Add(Type:=msoControlPopup)

With MenuItem

.Caption = "MenuItem2"
.BeginGroup = True

End With

'------------------------------------------------------------------------------------------------------------------------

Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)

With SubMenuItem

.Caption = "MenuItem2a"
.OnAction = "Macro2a"

End With

'------------------------------------------------------------------------------------------------------------------------

Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)

With SubMenuItem

.Caption = "MenuItem2b"
.OnAction = "Macro2b"

End With

End Function

[Roman]

Hi Anders,
try this one:

Sub makemenewbar()
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
..Caption = "Name of new bar"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Button1"
.OnAction = "macro1"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Submenu1"
'.OnAction = "sheets_startuf"
End With

Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Button2 name"
.OnAction = "macro2"
End With

Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Button3 name"
.OnAction = "macro3"
End With
End Sub

[Anders]

Excellent example!!!
Thanks a lot!

/Anders


"Roman" wrote:

> Hi Anders,
> try this one:
>
> Sub makemenewbar()
> Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
> Temporary:=True)
> With mynewbar
> ..Caption = "Name of new bar"
> End With
>
> Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
> With button1
> .Caption = "Button1"
> .OnAction = "macro1"
> End With
>
> Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
> With mysubmenu
> .Caption = "Submenu1"
> '.OnAction = "sheets_startuf"
> End With
>
> Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
> With button2
> .Caption = "Button2 name"
> .OnAction = "macro2"
> End With
>
> Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
> With button3
> .Caption = "Button3 name"
> .OnAction = "macro3"
> End With
> End Sub
>
>

[Bob Phillips]

Anders,

Here is an example

Sub PartsMenu()
Dim HelpMenu As CommandBarControl
Dim MainMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton
' Deletes menu if it exits
Call DeleteMenu
' Find the help menu
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
If HelpMenu Is Nothing Then
' Add the menu to the end
Set MainMenu = CommandBars(1).Controls. _
Add(Type:=msoControlPopup, temporary:=True)
Else
' Add menu before help
Set MainMenu = CommandBars(1).Controls. _
Add(Type:=msoControlPopup, before:=HelpMenu.Index, _
temporary:=True)
End If
' Add caption
MainMenu.Caption = "&Parts Utility"
' Searching for parts
Set MenuItem = MainMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Search Parts..."
.FaceId = 48
.ShortcutText = "Ctrl+Shift+S"
.OnAction = "SetupSearch"
End With
' LO / Remaining printout
Set MenuItem = MainMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Generate Parts Review..."
.FaceId = 285
.ShortcutText = "Ctrl+Shift+D"
.OnAction = "LORemaining"
End With
' View summary sheet
Set MenuItem = MainMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Sub menu"
End With

Set Submenuitem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With Submenuitem
.Caption = "&View Summary..."
.FaceId = 592
.OnAction = "Summary"
End With
' Print summary sheet
Set Submenuitem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With Submenuitem
.Caption = "Print Summary"
' .Application = 364
.OnAction = "PrintSummary"
End With
End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars(1).Controls("&Parts Utility").Delete
On Error GoTo 0
End Sub

--
HTH

Bob Phillips

"Anders" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I would like to create a command bar that contains a menu with sub menues.
> Do anyone know how to do this?
>
> Menu
> -------
> Menu Item1
> Menu Item2 -> Sub menu item -> Sub sub menu item
>
> Regards
> Anders
>

[Greg Wilson]

For the sake of brevity, I only show how to:
1) Create a new toolbar
2) Add a popup type control (that supports menu items) to the toolbar
2) Add menu items to the popup control (menu control)
3) Add a popup control (that supports submenu items) to the first popup
control
4) Add submenu items to the second popup control
5) Use arrays and loops to drastically cut down on the code requirement.

You can continue the same branching process indefinately to my knowledge.

Regards,
Greg

Sub MakeNewMenu()
Dim CB As CommandBar
Dim NewMenu As CommandBarControl
Dim MenuItm As CommandBarControl
Dim SubMenuItm As CommandBarControl
Dim Arr1 As Variant, Arr2 As Variant
Dim Arr3 As Variant
Dim i As Integer

With Application
.ScreenUpdating = False
'Menu item caption list
Arr1 = Array("Caption 1", "Caption 2", _
"Caption 3", "Caption 4", "Caption 5")
'Menu item macro list
Arr2 = Array("Menu macro 1", "Menu macro 2", _
"Menu macro 3", "Menu macro 4", "Menu macro 5")
'Menu item FaceId list
Arr3 = Array(100, 101, 102, 103, 104)
Set CB = .CommandBars.Add("Data Analysis", Temporary:=True)
Set NewMenu = CB.Controls.Add(msoControlPopup)
NewMenu.Caption = "Menu items"
'ToolTipText supported on this level only
NewMenu.TooltipText = "Select from my macros"

'When referencing elements in an array, the first
'element has an index value of zero (hence 0 to 4)
For i = 0 To 4
Set MenuItm = NewMenu.Controls.Add
With MenuItm
.Caption = Arr1(i)
.Style = msoButtonIconAndCaption
.OnAction = Arr2(i)
.FaceId = Arr3(i)
End With
Next
'Submenu item caption list
Arr1 = Array("Caption 6", "Caption 7", "Caption 8", _
"Caption 9", "Caption 10")
'Submenu item macro list
Arr2 = Array("Submenu macro 1", "Submenu macro 2", _
"Submenu macro 3", "Submenu macro 4", "Submenu macro 5")
'Submenu item FaceId list
Arr3 = Array(200, 201, 202, 203, 204)
'Now add popup type control to support the submenus
Set MenuItm = NewMenu.Controls.Add(msoControlPopup)
MenuItm.Caption = "Sub menu items"
For i = 0 To 4
Set SubMenuItm = MenuItm.Controls.Add
With SubMenuItm
.Caption = Arr1(i)
.Style = msoButtonIconAndCaption
.OnAction = Arr2(i)
.FaceId = Arr3(i)
End With
Next
CB.Visible = True
DoEvents
.ScreenUpdating = True
End With
End Sub

"Anders" wrote:

> Hi all,
>
> I would like to create a command bar that contains a menu with sub menues.
> Do anyone know how to do this?
>
> Menu
> -------
> Menu Item1
> Menu Item2 -> Sub menu item -> Sub sub menu item
>
> Regards
> Anders
>

[Troispieds]

Hi All,

it is possible to add an image (icon) to the msoControlPopup, like it is the
case for the msoControlButton ?For answers thanks in advance.

Cheers,

Florian

"Greg Wilson" wrote:

> For the sake of brevity, I only show how to:
> 1) Create a new toolbar
> 2) Add a popup type control (that supports menu items) to the toolbar
> 2) Add menu items to the popup control (menu control)
> 3) Add a popup control (that supports submenu items) to the first popup
> control
> 4) Add submenu items to the second popup control
> 5) Use arrays and loops to drastically cut down on the code requirement.
>
> You can continue the same branching process indefinately to my knowledge.
>
> Regards,
> Greg
>
> Sub MakeNewMenu()
> Dim CB As CommandBar
> Dim NewMenu As CommandBarControl
> Dim MenuItm As CommandBarControl
> Dim SubMenuItm As CommandBarControl
> Dim Arr1 As Variant, Arr2 As Variant
> Dim Arr3 As Variant
> Dim i As Integer
>
> With Application
> .ScreenUpdating = False
> 'Menu item caption list
> Arr1 = Array("Caption 1", "Caption 2", _
> "Caption 3", "Caption 4", "Caption 5")
> 'Menu item macro list
> Arr2 = Array("Menu macro 1", "Menu macro 2", _
> "Menu macro 3", "Menu macro 4", "Menu macro 5")
> 'Menu item FaceId list
> Arr3 = Array(100, 101, 102, 103, 104)
> Set CB = .CommandBars.Add("Data Analysis", Temporary:=True)
> Set NewMenu = CB.Controls.Add(msoControlPopup)
> NewMenu.Caption = "Menu items"
> 'ToolTipText supported on this level only
> NewMenu.TooltipText = "Select from my macros"
>
> 'When referencing elements in an array, the first
> 'element has an index value of zero (hence 0 to 4)
> For i = 0 To 4
> Set MenuItm = NewMenu.Controls.Add
> With MenuItm
> .Caption = Arr1(i)
> .Style = msoButtonIconAndCaption
> .OnAction = Arr2(i)
> .FaceId = Arr3(i)
> End With
> Next
> 'Submenu item caption list
> Arr1 = Array("Caption 6", "Caption 7", "Caption 8", _
> "Caption 9", "Caption 10")
> 'Submenu item macro list
> Arr2 = Array("Submenu macro 1", "Submenu macro 2", _
> "Submenu macro 3", "Submenu macro 4", "Submenu macro 5")
> 'Submenu item FaceId list
> Arr3 = Array(200, 201, 202, 203, 204)
> 'Now add popup type control to support the submenus
> Set MenuItm = NewMenu.Controls.Add(msoControlPopup)
> MenuItm.Caption = "Sub menu items"
> For i = 0 To 4
> Set SubMenuItm = MenuItm.Controls.Add
> With SubMenuItm
> .Caption = Arr1(i)
> .Style = msoButtonIconAndCaption
> .OnAction = Arr2(i)
> .FaceId = Arr3(i)
> End With
> Next
> CB.Visible = True
> DoEvents
> .ScreenUpdating = True
> End With
> End Sub
>
> "Anders" wrote:
>
> > Hi all,
> >
> > I would like to create a command bar that contains a menu with sub menues.
> > Do anyone know how to do this?
> >
> > Menu
> > -------
> > Menu Item1
> > Menu Item2 -> Sub menu item -> Sub sub menu item
> >
> > Regards
> > Anders
> >

[Greg Wilson]

The answer is no. However, instead of using a msoControlPopup, you can create
a popup type toolbar and call it using a regular msoControlButton (which
supports a FaceId). The affect is about the same.

Note that this method is sometimes advantagious because the macro can be
used to change the status of controls on the popup toolbar (e.g. enabled
property etc.) before showing the toolbar; whereas the click event of the
msoControlPopup cannot be accessed before the dropdown (or popup toolbar ?)
is displayed. You would have to use other, less convenient, events to
initiate change of the control properties.

Regards,
Greg

"Troispieds" wrote:

> Hi All,
>
> it is possible to add an image (icon) to the msoControlPopup, like it is the
> case for the msoControlButton ?For answers thanks in advance.
>
> Cheers,
>
> Florian
>
> "Greg Wilson" wrote:
>
> > For the sake of brevity, I only show how to:
> > 1) Create a new toolbar
> > 2) Add a popup type control (that supports menu items) to the toolbar
> > 2) Add menu items to the popup control (menu control)
> > 3) Add a popup control (that supports submenu items) to the first popup
> > control
> > 4) Add submenu items to the second popup control
> > 5) Use arrays and loops to drastically cut down on the code requirement.
> >
> > You can continue the same branching process indefinately to my knowledge.
> >
> > Regards,
> > Greg
> >
> > Sub MakeNewMenu()
> > Dim CB As CommandBar
> > Dim NewMenu As CommandBarControl
> > Dim MenuItm As CommandBarControl
> > Dim SubMenuItm As CommandBarControl
> > Dim Arr1 As Variant, Arr2 As Variant
> > Dim Arr3 As Variant
> > Dim i As Integer
> >
> > With Application
> > .ScreenUpdating = False
> > 'Menu item caption list
> > Arr1 = Array("Caption 1", "Caption 2", _
> > "Caption 3", "Caption 4", "Caption 5")
> > 'Menu item macro list
> > Arr2 = Array("Menu macro 1", "Menu macro 2", _
> > "Menu macro 3", "Menu macro 4", "Menu macro 5")
> > 'Menu item FaceId list
> > Arr3 = Array(100, 101, 102, 103, 104)
> > Set CB = .CommandBars.Add("Data Analysis", Temporary:=True)
> > Set NewMenu = CB.Controls.Add(msoControlPopup)
> > NewMenu.Caption = "Menu items"
> > 'ToolTipText supported on this level only
> > NewMenu.TooltipText = "Select from my macros"
> >
> > 'When referencing elements in an array, the first
> > 'element has an index value of zero (hence 0 to 4)
> > For i = 0 To 4
> > Set MenuItm = NewMenu.Controls.Add
> > With MenuItm
> > .Caption = Arr1(i)
> > .Style = msoButtonIconAndCaption
> > .OnAction = Arr2(i)
> > .FaceId = Arr3(i)
> > End With
> > Next
> > 'Submenu item caption list
> > Arr1 = Array("Caption 6", "Caption 7", "Caption 8", _
> > "Caption 9", "Caption 10")
> > 'Submenu item macro list
> > Arr2 = Array("Submenu macro 1", "Submenu macro 2", _
> > "Submenu macro 3", "Submenu macro 4", "Submenu macro 5")
> > 'Submenu item FaceId list
> > Arr3 = Array(200, 201, 202, 203, 204)
> > 'Now add popup type control to support the submenus
> > Set MenuItm = NewMenu.Controls.Add(msoControlPopup)
> > MenuItm.Caption = "Sub menu items"
> > For i = 0 To 4
> > Set SubMenuItm = MenuItm.Controls.Add
> > With SubMenuItm
> > .Caption = Arr1(i)
> > .Style = msoButtonIconAndCaption
> > .OnAction = Arr2(i)
> > .FaceId = Arr3(i)
> > End With
> > Next
> > CB.Visible = True
> > DoEvents
> > .ScreenUpdating = True
> > End With
> > End Sub
> >
> > "Anders" wrote:
> >
> > > Hi all,
> > >
> > > I would like to create a command bar that contains a menu with sub menues.
> > > Do anyone know how to do this?
> > >
> > > Menu
> > > -------
> > > Menu Item1
> > > Menu Item2 -> Sub menu item -> Sub sub menu item
> > >
> > > Regards
> > > Anders
> > >

[Troispieds]

Thanks for your quick response. At the moment I don't know how I can manage
your suggestion. Therefore I added my source coed below. Perhaps you might
help me again. Again thanks in advance.

Cheers,

Florian


Sub add_toolbar()

Dim Cbar As CommandBar

Dim cbpop As CommandBarControl
Dim cbctl As CommandBarControl
Dim cbsub As CommandBarControl

Set Cbar = CommandBars("myToolbar")
Cbar.Visible = True

Set cbpop = Cbar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
cbpop.Caption = "my_first_Popup"
cbpop.Visible = True


Set cbsub = cbpop.Controls.Add(Type:=msoControlPopup)
cbsub.Visible = True
cbsub.BeginGroup = True
cbsub.Caption = "my_Second_Popup"


Set cbctl = cbsub.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
cbctl.Style = msoButtonCaption
cbctl.Caption = "my_firt_button_in_the_second_popup"
cbctl.OnAction = ""


End Sub




"Greg Wilson" wrote:

> The answer is no. However, instead of using a msoControlPopup, you can create
> a popup type toolbar and call it using a regular msoControlButton (which
> supports a FaceId). The affect is about the same.
>
> Note that this method is sometimes advantagious because the macro can be
> used to change the status of controls on the popup toolbar (e.g. enabled
> property etc.) before showing the toolbar; whereas the click event of the
> msoControlPopup cannot be accessed before the dropdown (or popup toolbar ?)
> is displayed. You would have to use other, less convenient, events to
> initiate change of the control properties.
>
> Regards,
> Greg
>
> "Troispieds" wrote:
>
> > Hi All,
> >
> > it is possible to add an image (icon) to the msoControlPopup, like it is the
> > case for the msoControlButton ?For answers thanks in advance.
> >
> > Cheers,
> >
> > Florian
> >
> > "Greg Wilson" wrote:
> >
> > > For the sake of brevity, I only show how to:
> > > 1) Create a new toolbar
> > > 2) Add a popup type control (that supports menu items) to the toolbar
> > > 2) Add menu items to the popup control (menu control)
> > > 3) Add a popup control (that supports submenu items) to the first popup
> > > control
> > > 4) Add submenu items to the second popup control
> > > 5) Use arrays and loops to drastically cut down on the code requirement.
> > >
> > > You can continue the same branching process indefinately to my knowledge.
> > >
> > > Regards,
> > > Greg
> > >
> > > Sub MakeNewMenu()
> > > Dim CB As CommandBar
> > > Dim NewMenu As CommandBarControl
> > > Dim MenuItm As CommandBarControl
> > > Dim SubMenuItm As CommandBarControl
> > > Dim Arr1 As Variant, Arr2 As Variant
> > > Dim Arr3 As Variant
> > > Dim i As Integer
> > >
> > > With Application
> > > .ScreenUpdating = False
> > > 'Menu item caption list
> > > Arr1 = Array("Caption 1", "Caption 2", _
> > > "Caption 3", "Caption 4", "Caption 5")
> > > 'Menu item macro list
> > > Arr2 = Array("Menu macro 1", "Menu macro 2", _
> > > "Menu macro 3", "Menu macro 4", "Menu macro 5")
> > > 'Menu item FaceId list
> > > Arr3 = Array(100, 101, 102, 103, 104)
> > > Set CB = .CommandBars.Add("Data Analysis", Temporary:=True)
> > > Set NewMenu = CB.Controls.Add(msoControlPopup)
> > > NewMenu.Caption = "Menu items"
> > > 'ToolTipText supported on this level only
> > > NewMenu.TooltipText = "Select from my macros"
> > >
> > > 'When referencing elements in an array, the first
> > > 'element has an index value of zero (hence 0 to 4)
> > > For i = 0 To 4
> > > Set MenuItm = NewMenu.Controls.Add
> > > With MenuItm
> > > .Caption = Arr1(i)
> > > .Style = msoButtonIconAndCaption
> > > .OnAction = Arr2(i)
> > > .FaceId = Arr3(i)
> > > End With
> > > Next
> > > 'Submenu item caption list
> > > Arr1 = Array("Caption 6", "Caption 7", "Caption 8", _
> > > "Caption 9", "Caption 10")
> > > 'Submenu item macro list
> > > Arr2 = Array("Submenu macro 1", "Submenu macro 2", _
> > > "Submenu macro 3", "Submenu macro 4", "Submenu macro 5")
> > > 'Submenu item FaceId list
> > > Arr3 = Array(200, 201, 202, 203, 204)
> > > 'Now add popup type control to support the submenus
> > > Set MenuItm = NewMenu.Controls.Add(msoControlPopup)
> > > MenuItm.Caption = "Sub menu items"
> > > For i = 0 To 4
> > > Set SubMenuItm = MenuItm.Controls.Add
> > > With SubMenuItm
> > > .Caption = Arr1(i)
> > > .Style = msoButtonIconAndCaption
> > > .OnAction = Arr2(i)
> > > .FaceId = Arr3(i)
> > > End With
> > > Next
> > > CB.Visible = True
> > > DoEvents
> > > .ScreenUpdating = True
> > > End With
> > > End Sub
> > >
> > > "Anders" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I would like to create a command bar that contains a menu with sub menues.
> > > > Do anyone know how to do this?
> > > >
> > > > Menu
> > > > -------
> > > > Menu Item1
> > > > Menu Item2 -> Sub menu item -> Sub sub menu item
> > > >
> > > > Regards
> > > > Anders
> > > >

[Greg Wilson]

This was done very quickly and tested briefly. I'm short on time today.

It shows that you can use a standard commandbar button to display a popup
type toolbar (Position:=msoBarPopup) in the place of a standard menu type
control (msoControlPopup). I use a loop to populate the popup toolbar and use
arrays to apply Caption, OnAction and FaceId properties. I also show that
this method has the advantage that you can change the status (e.g. Enabled
property) of controls before displaying the popup.

I repeat, this was done quickly. My tests indicate that it's OK. Best of
luck !!!

Regards,
Greg


Sub XYZ()
Dim CBar As CommandBar, Popup As CommandBar
Dim ctrl As CommandBarControl
Dim CaptArr As Variant, MacroArr As Variant
Dim FaceIdArr As Variant
Dim i As Long

With Application
.ScreenUpdating = False
Set CBar = .CommandBars("myToolbar")
Set ctrl = CBar.Controls.Add(Temporary:=True)
With ctrl
.Caption = "Options"
.TooltipText = "Show Option list"
.OnAction = "ShowPopup"
.FaceId = 300
.Style = msoButtonIconAndCaption
End With
On Error Resume Next
.CommandBars("Options List").Delete
On Error GoTo 0
Set Popup = .CommandBars.Add(Position:=msoBarPopup, Temporary:=True)
Popup.Name = "Options List"
CaptArr = Array("Run Macro 1", "Run Macro 2", "Run Macro 3")
MacroArr = Array("Macro1", "Macro2", "Macro3")
FaceIdArr = Array(100, 101, 102)

For i = 0 To 2
Set ctrl = Popup.Controls.Add
With ctrl
.Caption = CaptArr(i)
.OnAction = MacroArr(i)
.FaceId = FaceIdArr(i)
End With
Next
CBar.Visible = True
.ScreenUpdating = True
End With
End Sub

Private Sub ShowPopup()
Dim i As Long
'This demo toggles the enabled status of the
'second button before showing the popup commandbar
With Application.CommandBars("Options List")
.Controls(2).Enabled = Not .Controls(2).Enabled
.ShowPopup
End With
End Sub

Sub Macro1()
MsgBox "You called Macro1"
End Sub

Sub Macro2()
MsgBox "You called Macro2"
End Sub

Sub Macro3()
MsgBox "You called Macro3"
End Sub

[micklloyd]

Hi - first post so not sure if replying to a related thread is the best way to post this question. Anyway:

On a related issue, I have set up menus with menu items (buotons, popups, dropdowns,etc). I want to reference these controls elsewhere (in different modules) and the variable names created with Set appear to be lost (out of context). I can however refer to the controls index number. Problem is I can only get index numbers (using FindControl) for the controls at the menu level. It does not pickup menu items. Any way around this? Thanks

[Tom Ogilvy]

If using xl2000 or later, use FindControls with an "s"

see Office VBA help for details.

--
Regards,
Tom Ogilvy

"micklloyd" <[email protected]> wrote
in message news:[email protected]...
>
> Hi - first post so not sure if replying to a related thread is the best
> way to post this question. Anyway:
>
> On a related issue, I have set up menus with menu items (buotons,
> popups, dropdowns,etc). I want to reference these controls elsewhere
> (in different modules) and the variable names created with Set appear
> to be lost (out of context). I can however refer to the controls index
> number. Problem is I can only get index numbers (using FindControl) for
> the controls at the menu level. It does not pickup menu items. Any way
> around this? Thanks
>
>
> --
> micklloyd
> ------------------------------------------------------------------------
> micklloyd's Profile:
http://www.excelforum.com/member.php...o&userid=30696
> View this thread: http://www.excelforum.com/showthread...hreadid=383809
>

[micklloyd]

Thanks Tom

FindControls only seems to work for all commandbars as follows (Office VBA help as suggested):

"Set cntrls = CommandBars.FindControls(Type:=msoControlDropdown)"

but not on an individual commandbar such as:

"Set cntrls = CommandBars("DTSheet").FindControls(Type:=msoControlDropdown)"

It replies with a "Method or data member not found".

Regards
Mick