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

[Daniel Cremin]

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.

[Anne Troy]

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

[Daniel Cremin]

can anyone help me out?

[Daniel Cremin]

anyone know?

[Daniel Cremin]

ivan dyou know how?

[Daniel Cremin]

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.

[Ivan F Moala]

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

[Daniel Cremin]

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).

[Dave Hawley]

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.

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 ]