Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Have a workbook that creates a custom menu when the file is opened; if a user
opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Hi Paige,
Haven't tested this but instead of With ActiveWorkbook try With ThisWorkbook. It is possible that the wrong workbook is the active one at the time the macro runs. -- Regards, OssieMac "Paige" wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
This may not work for you, but I think it's best to separate the toolbar (and
its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Thanks, Ossie. Tried this and it still doesn't work. If you have any other
ideas, would appreciate them! "OssieMac" wrote: Hi Paige, Haven't tested this but instead of With ActiveWorkbook try With ThisWorkbook. It is possible that the wrong workbook is the active one at the time the macro runs. -- Regards, OssieMac "Paige" wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Sorry Paige but I haven't got any other ideas.
-- Regards, OssieMac "Paige" wrote: Thanks, Ossie. Tried this and it still doesn't work. If you have any other ideas, would appreciate them! "OssieMac" wrote: Hi Paige, Haven't tested this but instead of With ActiveWorkbook try With ThisWorkbook. It is possible that the wrong workbook is the active one at the time the macro runs. -- Regards, OssieMac "Paige" wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Thanks, Dave. At least I know I'm not crazy! I tried moving the
deletion/creation of the toolbar to workbook_activate, which worked, but I don't think that is the most esthetically pleasing solution, or efficient. So based upon your input, have decided to put it into the Personal.xls workbook. That should work. Thanks again to everyone! "Dave Peterson" wrote: This may not work for you, but I think it's best to separate the toolbar (and its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
For something like this -- a dedicated set of macros, I'd use a dedicated
workbook or addin. I wouldn't clutter up my personal workbook with this. It would make it easier to share with others, too. Paige wrote: Thanks, Dave. At least I know I'm not crazy! I tried moving the deletion/creation of the toolbar to workbook_activate, which worked, but I don't think that is the most esthetically pleasing solution, or efficient. So based upon your input, have decided to put it into the Personal.xls workbook. That should work. Thanks again to everyone! "Dave Peterson" wrote: This may not work for you, but I think it's best to separate the toolbar (and its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Dave, if you use a dedicated workbook, do you have it automatically open when
the original workbook is opened, and just hide it? "Dave Peterson" wrote: For something like this -- a dedicated set of macros, I'd use a dedicated workbook or addin. I wouldn't clutter up my personal workbook with this. It would make it easier to share with others, too. Paige wrote: Thanks, Dave. At least I know I'm not crazy! I tried moving the deletion/creation of the toolbar to workbook_activate, which worked, but I don't think that is the most esthetically pleasing solution, or efficient. So based upon your input, have decided to put it into the Personal.xls workbook. That should work. Thanks again to everyone! "Dave Peterson" wrote: This may not work for you, but I think it's best to separate the toolbar (and its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Save the dedicated workbook as an add-in as Dave suggests.
Load it through ToolsAdd-ins and it will be available and hidden. Gord Dibben MS Excel MVP On Sun, 9 Dec 2007 16:16:01 -0800, Paige wrote: Dave, if you use a dedicated workbook, do you have it automatically open when the original workbook is opened, and just hide it? "Dave Peterson" wrote: For something like this -- a dedicated set of macros, I'd use a dedicated workbook or addin. I wouldn't clutter up my personal workbook with this. It would make it easier to share with others, too. Paige wrote: Thanks, Dave. At least I know I'm not crazy! I tried moving the deletion/creation of the toolbar to workbook_activate, which worked, but I don't think that is the most esthetically pleasing solution, or efficient. So based upon your input, have decided to put it into the Personal.xls workbook. That should work. Thanks again to everyone! "Dave Peterson" wrote: This may not work for you, but I think it's best to separate the toolbar (and its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Thanks, Gord!
"Gord Dibben" wrote: Save the dedicated workbook as an add-in as Dave suggests. Load it through ToolsAdd-ins and it will be available and hidden. Gord Dibben MS Excel MVP On Sun, 9 Dec 2007 16:16:01 -0800, Paige wrote: Dave, if you use a dedicated workbook, do you have it automatically open when the original workbook is opened, and just hide it? "Dave Peterson" wrote: For something like this -- a dedicated set of macros, I'd use a dedicated workbook or addin. I wouldn't clutter up my personal workbook with this. It would make it easier to share with others, too. Paige wrote: Thanks, Dave. At least I know I'm not crazy! I tried moving the deletion/creation of the toolbar to workbook_activate, which worked, but I don't think that is the most esthetically pleasing solution, or efficient. So based upon your input, have decided to put it into the Personal.xls workbook. That should work. Thanks again to everyone! "Dave Peterson" wrote: This may not work for you, but I think it's best to separate the toolbar (and its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Just to add to Gord's reply...
I'd use Tools|Addins and only load it when I wanted or I'd just double click on the filename in windows explorer to load it when I needed it. or I'd put the addin in my XLStart folder if I really wanted it available whenever I opened excel. ====== For me, I'd use the middle option--but that extra step wouldn't bother me. It might bother you. Paige wrote: Thanks, Gord! "Gord Dibben" wrote: Save the dedicated workbook as an add-in as Dave suggests. Load it through ToolsAdd-ins and it will be available and hidden. Gord Dibben MS Excel MVP On Sun, 9 Dec 2007 16:16:01 -0800, Paige wrote: Dave, if you use a dedicated workbook, do you have it automatically open when the original workbook is opened, and just hide it? "Dave Peterson" wrote: For something like this -- a dedicated set of macros, I'd use a dedicated workbook or addin. I wouldn't clutter up my personal workbook with this. It would make it easier to share with others, too. Paige wrote: Thanks, Dave. At least I know I'm not crazy! I tried moving the deletion/creation of the toolbar to workbook_activate, which worked, but I don't think that is the most esthetically pleasing solution, or efficient. So based upon your input, have decided to put it into the Personal.xls workbook. That should work. Thanks again to everyone! "Dave Peterson" wrote: This may not work for you, but I think it's best to separate the toolbar (and its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menu
Great.....sounds like a good idea, think I'll go with that also. Thanks!!!
"Dave Peterson" wrote: Just to add to Gord's reply... I'd use Tools|Addins and only load it when I wanted or I'd just double click on the filename in windows explorer to load it when I needed it. or I'd put the addin in my XLStart folder if I really wanted it available whenever I opened excel. ====== For me, I'd use the middle option--but that extra step wouldn't bother me. It might bother you. Paige wrote: Thanks, Gord! "Gord Dibben" wrote: Save the dedicated workbook as an add-in as Dave suggests. Load it through ToolsAdd-ins and it will be available and hidden. Gord Dibben MS Excel MVP On Sun, 9 Dec 2007 16:16:01 -0800, Paige wrote: Dave, if you use a dedicated workbook, do you have it automatically open when the original workbook is opened, and just hide it? "Dave Peterson" wrote: For something like this -- a dedicated set of macros, I'd use a dedicated workbook or addin. I wouldn't clutter up my personal workbook with this. It would make it easier to share with others, too. Paige wrote: Thanks, Dave. At least I know I'm not crazy! I tried moving the deletion/creation of the toolbar to workbook_activate, which worked, but I don't think that is the most esthetically pleasing solution, or efficient. So based upon your input, have decided to put it into the Personal.xls workbook. That should work. Thanks again to everyone! "Dave Peterson" wrote: This may not work for you, but I think it's best to separate the toolbar (and its macros) into a different workbook (or addin). Then just one version of the code will be necessary (making updates lots easier). And each workbook won't try to modify the toolbar again (and again ....) Paige wrote: Have a workbook that creates a custom menu when the file is opened; if a user opens another version of this same workbook, it deletes the menu already there and recreates it again, to ensure the menu is only on the toolbar once regardless of the # of workbooks open. Problem is that if I open File#1 and then File#2, then close File#1, when I use the custom menu to do something, it re-opens File#1, as if Excel thinks the macros called by the menu still reside in File#1. I need it to look for the macros in the active workbook. Have tried numerous things to fix this, but to no avail. The following sub is called when the workbook is opened: Sub CreateScheduleMenu() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl Application.EnableEvents = True Application.ScreenUpdating = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Schedules").Delete On Error GoTo 0 With ActiveWorkbook Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmbControl .Caption = "Schedules" With .Controls.Add(msoControlPopup) .Caption = "For NCC/Pricer Use Only" With .Controls.Add(msoControlPopup) With .Controls.Add(Type:=msoControlButton) .Caption = "Import from MMS Serv Form" .OnAction = "GetDataFromMMSForm" .FaceId = 301 End With End With End With End With End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying A custom menu as a popup menu | Excel Programming | |||
Custom Menu and Menu Items | Excel Programming | |||
Adding Sub Menu Item to Current Custom Menu | Excel Programming | |||
VBA - Disappearing custom menu and custom toolbar | Excel Programming | |||
Custom Menu return to Excel Menu upon Closing | Excel Programming |