Adding / Deleting from toolbars

[Steven Revell]

Hi,

I was wondering if it was possible to add a command to a
toolbar on workbook open. i need to give it a name in case
someone moves it. I then want to delete it on workbook
close.

Thanks in advance for any help,

Steven

 

[Ron de Bruin]

Try something like this Steven

Sub MenuBar_Item()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0

With Application.CommandBars(1)
.Controls.Add(Type:=msoControlPopup, before:=1).Caption = "&Hi"
.Controls("Hi").OnAction = ThisWorkbook.Name & "!TestMacro"
End With
End Sub

Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub

 

[Tom Ogilvy]

Use the workbook_Open event and the Workbook_BeforeClose events to add and
remove the control.


Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;166755
File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R)
Excel 97
File Name: WE1183.EXE


http://support.microsoft.com/default.aspx?scid=kb;en-us;213220&Product=xlw
http://support.microsoft.com/default.aspx?scid=kb;EN-US;291294
http://support.microsoft.com/default.aspx?scid=kb;EN-US;161761
XL2000: Summary of Workbook and Worksheet Application Events
XL2002: Summary of Workbook and Worksheet Application Events
XL97: Summary of Workbook and Worksheet Application Events

http://www.cpearson.com/excel/events.htm
Event Procedures
In Microsoft® Excel97 (pertinent to xl97 and later)

 

[Ron de Bruin]

See in Tom's reply the events you must use ( I forgot to tell you)

 

[Bernie Deitrick]

Steven,

The first two sub below go into the ThisWorkbook object's codemodule, the last two into a regular module. The code will add a
smiley button to the standard toolbar to fire the macro named MacroName (which should be in Module1). The button will be added when
the file is opened, and removed when it is closed.

HTH,
Bernie
Excel MVP

Private Sub Workbook_Open()
AddButton
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteButton
End Sub

'Code below in standard module Module1
Public myBar As CommandBar
Public myButton As CommandBarButton

Sub AddButton()
DeleteButton
'Create the new button
Set myButton = myBar.Controls.Add(Type:=msoControlButton, ID:=2950)

'Set the properties of the new button
With myButton
.Caption = "Run my Macro"
.OnAction = ThisWorkbook.Name & "!Module1.MacroName"
End With

End Sub

Sub DeleteButton()
Set myBar = Application.CommandBars("Standard")
On Error Resume Next
myBar.Controls("Run my Macro").Delete
End Sub