Add button to commandbar to all workbooks within xla

[Franck]

Hi,
Is it possible, within my xla, to create a new button in an existing
commandbar whenever Excel is open ?

Thks for help

 

[Dave Peterson]

One way:

Option Explicit
Sub auto_open()

Dim myCMDBar As CommandBar
Dim myNewCtrl As CommandBarControl

Set myCMDBar = Application.CommandBars("formatting")

On Error Resume Next
myCMDBar.Controls("my New Item").Delete
On Error GoTo 0

Set myNewCtrl = Application.CommandBars("formatting").Controls.Add _
(Type:=msoControlButton, temporary:=True)

With myNewCtrl
.Caption = "my New Item"
.OnAction = ThisWorkbook.Name & "!" & "mynewitemmacro"
.Visible = True
.BeginGroup = True
.FaceId = 232
End With

End Sub
Sub auto_close()
On Error Resume Next
myCMDBar.Controls("my New Item").Delete
On Error GoTo 0
End Sub
Sub myNewItemMacro()
MsgBox "hi from my new item"
End Sub

If you're looking for nice icons:

John Walkenbach has a FaceId identifier program at:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech has one at:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech

 

[Dave Peterson]

Change the auto_close code to:


Sub auto_close()
Dim myCMDBar As CommandBar
Set myCMDBar = Application.CommandBars("formatting")
On Error Resume Next
myCMDBar.Controls("my New Item").Delete

 

[Franck]

Works great ! Thks ^^

Change the auto_close code to:


Sub auto_close()
Dim myCMDBar As CommandBar
Set myCMDBar = Application.CommandBars("formatting")
On Error Resume Next
myCMDBar.Controls("my New Item").Delete