Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding / Deleting from toolbars
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding / Deleting from toolbars
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, befo=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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Steven Revell" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding / Deleting from toolbars
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...b;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...20&Product=xlw http://support.microsoft.com/default...b;EN-US;291294 http://support.microsoft.com/default...b;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) -- Regards, Tom Ogilvy "Steven Revell" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding / Deleting from toolbars
See in Tom's reply the events you must use ( I forgot to tell you)
-- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Ron de Bruin" wrote in message ... 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, befo=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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Steven Revell" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding / Deleting from toolbars
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 "Steven Revell" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consecutive No'ing WHEN ADDING/DELETING ROWS | Excel Discussion (Misc queries) | |||
Adding macros to toolbars | Excel Discussion (Misc queries) | |||
Adding and deleting rows with formulas ****Need Help**** | Excel Discussion (Misc queries) | |||
Sequential number - adding and deleting rows | Excel Discussion (Misc queries) | |||
how to protect sheet from adding/deleting | Excel Discussion (Misc queries) |