Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: I need a button to be added by an auto_open and removed by a

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi i need some code that can add a custom button to the "Worksheet Menu Bar" of excel (file, edit, view) that just says the words "Year 10 Main Menu" and runs a macro called "ReturntoYear10Menu". The thing is i want it to dissapear when the file closes and so i need a macro that can accomplish this, does ne one know how to do this - i dont want a custom toolbar or menu i just want a single button after Help.

    Thanks very much in advance.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, Help is a menu button, so you NEED a menu button.

    You'll want to use the VBA method at:

    http://www.cpearson.com/excel/menus.htm#vba
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    can anyone help me out?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    anyone know?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ivan dyou know how?

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it cant be that hard to have a button be added to the right of the last button on the worksheet menu bar when my workbook opens can it? the only prb i could see is if i had my second version of the system (for the next year of students) as this would also need to have an auto_open macro that brings up a button saying "Year 11 Menu" and would have a pre-written macro assigned to it. Id need an auto_close that could remove it from the menu.

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Did you have a look @ the recomended site ??

    Try this;

    Thisworkbook object;


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CreateMenuBtn
    End Sub

    Private Sub Workbook_Open()
    DeleteMenuBtn
    End Sub


    Std Module;



    Sub CreateMenuBtn()
    Dim MenuObject
    Dim MenuLevel As Integer, Position, Divider As Boolean, FaceId As Double
    Dim Macros As String, Caption As String

    Call DeleteMenuBtn

    MenuLevel = 1
    Caption = "Year 10 Main Menu" 'This is your caption
    Position = 11
    Divider = True
    FaceId = 1994 'Change FaceID as required
    Macros = "MyMacro" 'Change to your macro name

    'Add to The Top-Level Menu To The Worksheet CommandBar
    Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlButton, _
    Before:=Position, temporary:=True)
    MenuObject.Style = msoButtonIconAndCaption
    MenuObject.Caption = Caption
    MenuObject.OnAction = Macros
    MenuObject.FaceId = FaceId
    End Sub

    Sub DeleteMenuBtn()
    On Error Resume Next
    Application.CommandBars(1).Controls("Year 10 Main Menu").Delete
    On Error GoTo 0
    End Sub


    Sub MyMacro()
    MsgBox "I'm running!"
    End Sub

    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yes i did but i couldnt really find a 'simple' solution(awful word i know but my project is marked by very unsophisticsted ppl who fown at anything above a Do Until loop).

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Daniel

    The best approach is via the Activate/Decativate Events.

    Right click on the Excel Icon, top left next to "File", select "View Code" and paste in this.

    Code:
    Private Sub Workbook_Activate()
    Dim cControl As CommandBarButton
    
    On Error Resume Next
        With Application.CommandBars("Worksheet menu Bar")
               .Controls("Year 10 Main Menu").Delete
    On Error GoTo 0
            Set cControl = .Controls.Add()
        End With
        
        With cControl
            .Caption = "Year 10 Main Menu"
            .Style = msoButtonCaption
            .OnAction = "ReturntoYear10Menu"
        End With
        
    End Sub
    
    Private Sub Workbook_Deactivate()
    
    On Error Resume Next
        With Application.CommandBars("Worksheet menu Bar")
               .Controls("Year 10 Main Menu").Delete
    On Error GoTo 0
       End With
    
    End Sub

    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-04-05 00:07 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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