Adding a Macro button to the MenuBare upon opening up a file

[mach3]

i need a button (with macro attached) to show up on a user's menubar upon open/close of the worksheet.

i once wrote similar code where a new menu item showed up on the worksheet menu bar upon open/close. i remember that the code had to be written in the 'ThisWorkbook' section of VB (NOT in a module) and that the code involved a CALL. however, beyond that, i forgot the code.

could someone please offer a quick macro that applies a macro button (w/ macro attached) to a user's menu bar upon Open and deletes the button upon Close? thanks.



mach3

 

[AJ]

Hiya,

Here's a spot of code from one of my workbooks that adds and removes a button called Select Sample, which in turn runs a macro called StartApp. Hope this does the trick...

Private Sub Workbook_Open()
Dim myButton As CommandBarButton
Set myButton = Application.CommandBars("Worksheet Menu Bar").Controls.Add
myButton.Caption = "&Select Sample"
myButton.Style = msoButtonCaption
myButton.BeginGroup = True
myButton.OnAction = "StartApp"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Select Sample").Delete
End Sub


P.S. You're right. Put it in Thisworkbook, not the module.

 

[mach3]

bravo, thanks.

mach3