Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add a menu bar when document opens
Hoe do I get a macro to auto open and auto remove? I have
the macros below that I want to add a menu item when I open the workbook its in and remove it when I close the workbook. I have been playing with it and can't get it to work. Can anyone help? Todd Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&OpenSupportingDocs" .OnAction = ThisWorkbook.Name & "OpenSupportingDocs" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl (Tag:="MenuItemTag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add a menu bar when document opens
Thank you!
-----Original Message----- Hi, Put those codes into the module ThisWorkbook for the event Activate to create the bar and inDeactivate to delete it. -- JP http://www.solutionsvba.com "Todd" wrote in message ... Hoe do I get a macro to auto open and auto remove? I have the macros below that I want to add a menu item when I open the workbook its in and remove it when I close the workbook. I have been playing with it and can't get it to work. Can anyone help? Todd Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl (, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&OpenSupportingDocs" .OnAction = ThisWorkbook.Name & "OpenSupportingDocs" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl (Tag:="MenuItemTag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add a menu bar when document opens
I am not doing this right yet! I trust the codes because
I have them curtesy of this news group. It always seems I miss something very basic! 1. Another menu bar is added each time I open the workbook. How do I stop that? 2. The button added doesn't run my macro. It says it can't find it? The name seems correct and I have tried pasting it into every module plus ThisWorkbook. 3. The auto close isn't working yet. I tried the suggested code and Private Sub App_WorkbookDeactivate (ByVal Wb As Workbook)as well. But it must be something else. Thanks, Todd -----Original Message----- One way: Put these in the ThisWorkbook code module: Private Sub Workbook_Open() MenuBar_Item_Item End Sub Private Sub Workbook_BeforeClose() MenuBar_Item_Item_Delete End Sub In article , "Todd" wrote: Hoe do I get a macro to auto open and auto remove? I have the macros below that I want to add a menu item when I open the workbook its in and remove it when I close the workbook. I have been playing with it and can't get it to work. Can anyone help? Todd Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl (, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&OpenSupportingDocs" .OnAction = ThisWorkbook.Name & "OpenSupportingDocs" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl (Tag:="MenuItemTag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
add a menu bar when document opens
The first problem is a direct result of the third - make sure you
use the correct syntax in the BeforeClose event (I didn't): Private Sub Workbook_BeforeClose(Cancel As Boolean) MenuBar_Item_Item_Delete End Sub The second is because your line .OnAction = ThisWorkbook.Name & "OpenSupportingDocs" is missing an exclamation point: .OnAction = ThisWorkbook.Name & "!OpenSupportingDocs" I've posted a workbook that adds and deletes the menubar he ftp://ftp.mcgimpsey.com/excel/Todd_demo.xls In article , "Todd" wrote: 1. Another menu bar is added each time I open the workbook. How do I stop that? 2. The button added doesn't run my macro. It says it can't find it? The name seems correct and I have tried pasting it into every module plus ThisWorkbook. 3. The auto close isn't working yet. I tried the suggested code and Private Sub App_WorkbookDeactivate (ByVal Wb As Workbook)as well. But it must be something else. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
add a menu bar when document opens
Todd,
The code to create should be in the open or activate macro, and the code to delete should be in the close or deactivate macro. All of these should be in the ThisWorkBook module. Pick form these... Private Sub Workbook_Open() * create code* End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) *delete code* End Sub Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window) *create code* End Sub Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window) *delete code* End Sub Be sure that your create code starts out by deleting the bar and than build it. Your on-action code should be in a standard module, NOT in a sheet module. Or in the ThisWorkBook module. Also make sure that you do not have duplicate macros of any kind with the same name... -- sb "Todd" wrote in message ... I am not doing this right yet! I trust the codes because I have them curtesy of this news group. It always seems I miss something very basic! 1. Another menu bar is added each time I open the workbook. How do I stop that? 2. The button added doesn't run my macro. It says it can't find it? The name seems correct and I have tried pasting it into every module plus ThisWorkbook. 3. The auto close isn't working yet. I tried the suggested code and Private Sub App_WorkbookDeactivate (ByVal Wb As Workbook)as well. But it must be something else. Thanks, Todd -----Original Message----- One way: Put these in the ThisWorkbook code module: Private Sub Workbook_Open() MenuBar_Item_Item End Sub Private Sub Workbook_BeforeClose() MenuBar_Item_Item_Delete End Sub In article , "Todd" wrote: Hoe do I get a macro to auto open and auto remove? I have the macros below that I want to add a menu item when I open the workbook its in and remove it when I close the workbook. I have been playing with it and can't get it to work. Can anyone help? Todd Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl (, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&OpenSupportingDocs" .OnAction = ThisWorkbook.Name & "OpenSupportingDocs" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl (Tag:="MenuItemTag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
add a menu bar when document opens
This file might be a help:
http://www.bygsoftware.com/examples/...tiveWbDemo.zip It's in the "Menu Routines" section on page: http://www.bygsoftware.com/examples/examples.htm It contains VBA code that will activate a menu only when the workbook it is in is active. The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Todd" wrote in message ... Hoe do I get a macro to auto open and auto remove? I have the macros below that I want to add a menu item when I open the workbook its in and remove it when I close the workbook. I have been playing with it and can't get it to work. Can anyone help? Todd Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&OpenSupportingDocs" .OnAction = ThisWorkbook.Name & "OpenSupportingDocs" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl (Tag:="MenuItemTag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alt E no longer opens Edit menu | Excel Discussion (Misc queries) | |||
Alt+E No Longer Opens Edit menu | Excel Discussion (Misc queries) | |||
Excel opens, but document doesn't | Setting up and Configuration of Excel | |||
Excel Document opens twice | Setting up and Configuration of Excel | |||
My document opens but all I see is a grey screen. | Excel Discussion (Misc queries) |