Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    New menus! (XL97/WinNT4)

    This is driving me nuts - how do you create a new menu bar item on the main menu bar that is attached to a spreadsheet?!

    I've created it fine and set it up as required, but when I close the spreadsheet the new menu is still there; if I then remove it (with the s/s closed) and reopen the s/s it's still gone! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Alternatively, I thought of creating the menu programmatically on opening the spreadsheet (and deleting on closing it, of course) but now I can't work out how to assign a macro to a commandbar control programmatically!

    Someone please point me in the right direction!!

    Ta every so muchly
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New menus! (XL97/WinNT4)

    It is probably best to create the item when the workbook is opened and delete it when the workbook is closed. You can assign a macro to a custom menu or toolbar item by setting the OnAction property to the name of the macro:

    Application.CommandBars("File").Controls("My Item").OnAction = "MyMacro"

    With a custom toolbar it's easier: you can set it up interactively, then store it in a particular workbook. It will automatically be displayed when the workbook is opened, you only need to delete it in code when the workbook is closed. See <!post=Distributing an Excel application with toolbars (5/95/97/2000/2002),200526>Distributing an Excel application with toolbars (5/95/97/2000/2002)<!/post>.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: New menus! (XL97/WinNT4)

    Hi Hans, thanks again!

    It was that OnAction bit that had got me - I just couldn't spot it as the equivalent of 'assign macro'!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: New menus! (XL97/WinNT4)

    Hi Hans, I set it up and it works fine for me, and for the person who usually uses it, but when another user had to cover and use it the sub that set up the menu item crashed every time. With a little investigation I found the problem is as follows. The sub I set up is thus:

    <pre>Sub NewMenu()
    Application.CommandBars("Worksheet Menu Bar") _
    .Controls.Add Type:=msoControlPopup, Before:=8
    Application.CommandBars("Worksheet Menu Bar") _
    .Controls(8).Caption = "&Macros"
    Application.CommandBars("Custom Popup 1").Controls _
    .Add Type:=msoControlButton, Id:=2949, Before:=1
    With Application.CommandBars("Custom Popup 1").Controls(1)
    .OnAction = "ThisWorkbook.Daily_Input"
    .Caption = "Dail&y Input"
    End With
    Application.CommandBars("Custom Popup 1").Controls.Add _
    Type:=msoControlButton, Id:=2949, Before:=2
    With Application.CommandBars("Custom Popup 1").Controls(2)
    .OnAction = "ThisWorkbook.Quarter_End"
    .Caption = "&Quarter End"
    End With
    End Sub</pre>


    You'll note that it automatically calls the new menu item "Custom Popup 1". However, when I re-recorded the basic macro on the new user's machine, it called it "Custom Popup 225".

    So, I can see that what I need to do is to specify my own name for the popup as it creates it, one I'm sure will be unique - and I cannot track down how, no matter what I try!

    Please help!! <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>
    Beryl M


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New menus! (XL97/WinNT4)

    Here is a variant that avoids the use of names:

    Sub NewMenu()
    With Application.CommandBars("Worksheet Menu Bar") _
    .Controls.Add(Type:=msoControlPopup, Before:=8)
    .Caption = "&Macros"
    With .Controls.Add(Type:=msoControlButton, ID:=2949, Before:=1)
    .OnAction = "ThisWorkbook.Daily_Input"
    .Caption = "Dail&y Input"
    End With
    With .Controls.Add(Type:=msoControlButton, ID:=2949, Before:=2)
    .OnAction = "ThisWorkbook.Quarter_End"
    .Caption = "&Quarter End"
    End With
    End With
    End Sub

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: New menus! (XL97/WinNT4)

    Thanks, Hans, I think that might do the trick! I'm off work for the next two weeks (not going anywhere except home, though, so I'll probably pop up here on occasion!) so I've put your suggestion in and sent it to the user to try, but I won't know the outcome until Tuesday 31 August!

    Many thanks anyway!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


Posting Permissions

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