Results 1 to 6 of 6
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new menu item subgroup (Excel 97/SR2)

    Can you tell us what you mean by "add a subgroup?" Do you want to LoadSickTime to become a pop up menu with four commands? Do you want to put four commands into the group on the menu that currently includes LoadSickTime? Something else?
    Legare Coleman

  2. #2
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new menu item subgroup (Excel 97/SR2)

    Legare, I guess I just don't have the semantics down yet. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I am looking to have a new menu added to the menu bar at the top of excel (just like File, Edit, View, etc...). The new menu bar will be called 'Attendance'. On the Attendance menu bar will be several items to choose from (Add New Employee, Load Sick Time, and Corrective Action). When the user clicks on Attendance|Load Sick Time, I want another menu to 'pop-up' (correct term?) to the right of the opened Attendance menu bar. The new menu would then have several items to choose from (item1, item2, and item3). Hopefully this makes better sense.
    <hr>WindowIndex = CommandBars(1).Controls("Venster").Index<hr>
    Hans, I understand the concept of what you have listed but what is "Venster"? I tried the code that you listed and nothing happened. When I step through (F8) the code and look at the variables, the above line of code shows "invalid procedure call or argument".

    ideas? <img src=/S/help.gif border=0 alt=help width=23 height=15>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new menu item subgroup (Excel 97/SR2)

    Sorry Steve, I did it again. 'Venster' is Dutch for 'Window'. I needed to change that to test the code in my Dutch Excel version. If you replace 'Venster' by 'Window' then it should work

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new menu item subgroup (Excel 97/SR2)

    And it did work. Thanks so much for the timely replies. <img src=/S/salute.gif border=0 alt=salute width=15 height=20> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    new menu item subgroup (Excel 97/SR2)

    <P ID="edit"><FONT SIZE=-1>Edited by gwhitfield on 19-Jul-01 06:26.</FONT></P>I inherited an ".xla" file form the person that was previously in my position. This add-in contained the following code which added a menu-bar called 'Attendance'. This works good for our current applications. I would like to expand the functionality of this menu-bar. Can anyone explain or show me how to add a subgroup to the 'Attendance' menu-bar?
    <pre>Sub Auto_Open()
    On Error Resume Next
    windowindex = CommandBars(1).Controls("Window").Index

    'Set up new menu
    Set newmenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, _
    Before:=windowindex, temporary:=True)
    newmenu.Caption = "&Attendance"

    'set up new menu items
    Set Item1 = CommandBars(1).Controls("Attendance").Controls.Add
    Item1.Caption = "&Add New Employee"
    Item1.OnAction = "StartAdd"
    Item1.BeginGroup = True
    Set Item2 = CommandBars(1).Controls("Attendance").Controls.Add
    Item2.Caption = "&Load Sick Time"
    Item2.OnAction = "SickStart"
    Item2.BeginGroup = True
    Set Item3 = CommandBars(1).Controls("Attendance").Controls.Add
    Item3.Caption = "&Corrective Action"
    Item3.OnAction = "StartCA"
    Item3.BeginGroup = True
    End Sub</pre>


    For example the code above will create a menu-bar called 'Attendance' with three entries underneath (Add New Employee, Load Sick Time, and Corrective Action). How can I add a subgroup to the 'Load Sick Time' choice. Within the subgroup I need to have 4 entries. <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new menu item subgroup (Excel 97/SR2)

    I am not sure if I understand what you want, but here are some adjustments to your code that add 3 subitems to the second menuitem:

    <pre>Sub Auto_Open()
    Dim WindowIndex As Integer
    Dim NewMenu As CommandBarPopup
    Dim Item1 As CommandBarControl
    Dim Item2 As CommandBarPopup
    Dim Item3 As CommandBarControl
    Dim SubItem1 As CommandBarControl
    Dim SubItem2 As CommandBarControl
    Dim SubItem3 As CommandBarControl
    On Error Resume Next
    CommandBars(1).Controls("Attendance").Delete
    WindowIndex = CommandBars(1).Controls("Venster").Index
    'Set up new menu
    Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, _
    Before:=WindowIndex, temporary:=True)
    NewMenu.Caption = "&Attendance"
    'set up new menu items
    Set Item1 = NewMenu.Controls.Add
    With Item1
    .Caption = "&Add New Employee"
    .OnAction = "StartAdd"
    .BeginGroup = True
    End With
    Set Item2 = NewMenu.Controls.Add(Type:=msoControlPopup)
    With Item2
    .Caption = "&Load Sick Time"
    .BeginGroup = True
    End With
    Set Item3 = NewMenu.Controls.Add
    With Item3
    .Caption = "&Corrective Action"
    .OnAction = "StartCA"
    .BeginGroup = True
    End With
    Set SubItem1 = Item2.Controls.Add(Type:=msoControlButton)
    With SubItem1
    .Caption = "Subitem1"
    .OnAction = "SubItem1_subname"
    End With
    Set SubItem2 = Item2.Controls.Add(Type:=msoControlButton)
    With SubItem2
    .Caption = "Subitem2"
    .OnAction = "SubItem2_subname"
    End With
    Set SubItem3 = Item2.Controls.Add(Type:=msoControlButton)
    With SubItem3
    .Caption = "Subitem3"
    .OnAction = "SubItem3_subname"
    End With
    End Sub
    </pre>


    Pay attention to the declaration section. Here you see the differences between the menuitems.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •