Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro attached to New Menu item on toolbar
I custom created a New Menu on the toolbar. It has an Assigned Macro. The
issue is if the user opens the file with the toolbar Menu item and then saves the file somewhere else then the Assigned Macro follows it to the newly saved file and I want to hold the macro at the original file. Thank you for your help, Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro attached to New Menu item on toolbar
Steven,
My customary advice it to create and destroy the commandbar through code using the workbook's open and close events. See below. HTH, Bernie MS Excel MVP In the workbook's Thisworkbook object code module, place the following code: Private Sub Workbook_BeforeClose(Cancel As Boolean) DeleteCommandbar End Sub Private Sub Workbook_Open() CreateCommandbar End Sub In a regular code module, place the following: Dim myBar As CommandBar Dim myButton As CommandBarButton Sub CreateCommandbar() On Error Resume Next DeleteCommandBar Set myBar = Application.CommandBars.Add("My Bar") With myBar .Position = msoBarTop .Visible = True .Enabled = True Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23) With myButton .Caption = "Hello" .Style = msoButtonIcon .FaceId = 137 .Enabled = True .OnAction = "SayHello" End With End With End Sub Sub DeleteCommandBar() 'Delete the commandbar if it already exists On Error Resume Next Application.CommandBars("My Bar").Delete End Sub Sub SayHello() MsgBox "Hello there" End Sub You can add as many buttons or other menu items as you like, and assign whatever macros you want. "Steven" wrote in message ... I custom created a New Menu on the toolbar. It has an Assigned Macro. The issue is if the user opens the file with the toolbar Menu item and then saves the file somewhere else then the Assigned Macro follows it to the newly saved file and I want to hold the macro at the original file. Thank you for your help, Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create menu/toolbar item with picture? | Excel Programming | |||
New menu item OnAction can't see macro? | Excel Programming | |||
re-enabling macro under menu item | Excel Programming | |||
Macro to display a menu item - possible? | Excel Programming | |||
MACRO ATTACHED TO A TOOLBAR BUTTON | Excel Programming |