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

Franck wrote:
>
> 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

[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
On Error GoTo 0
End Sub



Dave Peterson wrote:
>
> 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
>
> Franck wrote:
> >
> > 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

--

Dave Peterson

[Franck]

Works great ! Thks ^^

Dave Peterson wrote:
> 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
> On Error GoTo 0
> End Sub
>
>
>
> Dave Peterson wrote:
> >
> > 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
> >
> > Franck wrote:
> > >
> > > 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
>
> --
>
> Dave Peterson