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

Daniel Cremin

Board Regular
Joined
Feb 23, 2002
Messages
64
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
Did you have a look @ the recomended site ??

Try this;

Thisworkbook object;

</pre>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
CreateMenuBtn
End Sub

Private Sub Workbook_Open()
DeleteMenuBtn
End Sub
<pre/>

Std Module;

</pre>

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
<pre/>
 
Upvote 0
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).
 
Upvote 0
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
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-04-05 00:07
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top