Add button to commandbar to all workbooks within xla

F

Franck

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

Thks for help
 
D

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
 
D

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
 
F

Franck

Works great ! Thks ^^

Dave said:
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
Click to expand...
 
Top