MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom Button shows up every time I run Excel


Posted by TomH on December 04, 2001 7:48 AM

I have created a custom button on the same menu as File, Edit, View ... and assigned a macro to it. My problem is that is shows up everytime I run Excel. How can I limit it to ONLY show up in the workbook that has the macro?

Thanks in advance.


Posted by Lucky on December 04, 2001 8:32 AM

If you want the button on the worksheet...

View-Toolbars-Forms, then click and drag the 'Button' Icon to the appropriate worksheet and assign the macro

Posted by Jerid on December 07, 2001 2:10 PM

Then you need to use VBA to build a tool bar and add a button to it dynamically each time you open that workbook, and delete it when you close it.


Heres an example. You will need to change the OnAction property to the name of your macro.

Private Sub Workbook_Open()
Dim objToolBar As CommandBar

'Create new tool bar
Set objToolBar = CommandBars.Add(Name:="NewToolBar", MenuBar:=True, temporary:=True)

'Adds buttons to the Menu Bar
With application.CommandBars("NewToolBar")

'Create toolbar buttons'
With .Controls
'Create Print Button'
With .Add(msoControlButton)
.Caption = "Print"
.FaceId = 4
.OnAction = "PrintPage"
.BeginGroup = True
End With

'Create Print Preview Button'
With .Add(msoControlButton)
.Caption = "Print Preview"
.FaceId = 109
.OnAction = "PrintPreviewRoutine"
End With
End With
End With

objToolBar.Visible = True
end sub


Public Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars("NewToolBar").Delete
End Sub


Jerid