Customize standard toolbar for specific files

Posted by Jeff on February 16, 2001 2:43 PM

I would like to add a new command on the standard toolbar in MS Excel. However, I only want that command to be available when a certain file is opened. Is there any way to do this?

When I add a macro button or menu item (text) it always stays on my standard toolbar - it doesn't go away when I close the file that I want it to be associated with.

Thanks!

Posted by Dave Hawley on February 17, 2001 1:24 AM

Hi Jeff

You will need code for this, so here some!

Right click on the sheet picture, top left next to "File" and select "View Code". Paste this code over the top of what you see.

Dim MyCon As CommandBarButton
Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars _
("Worksheet Menu Bar").Controls("Push Me").Delete
Set MyCon = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With MyCon
.Caption = "Push Me"
.Style = msoButtonCaption
.OnAction = "My Macro"
End With
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars _
("Worksheet Menu Bar").Controls("Push Me").Delete
End Sub

Change the Caption "Push Me" and "My Macro" to the macro you want it top run. Push Alt+Q and return to Excel.

Dave

OzGrid Business Applications