lionne
01-30-2013, 02:19 AM
Hi guys
I've got the following issue:
I have created a custom menu in one workbook. I'd like to have this menu only in this workbook, but it happens to appear in every Excel file I open.
I thinked I messed up with the code locations. Here is the code for my menu:
1. In VBAProject --> Modules --> Module1
Option Explicit
Const Menu_name = "&Modify plan"
Const Command_SelectLanguage = "&Select language"
Const Command_SelectDuration = "&Select duration"
Sub Auto_Open()
Dim MB As Object
Dim Menu As Object
Dim Command As Object
Dim wb As Workbook
Dim ht As Worksheet
Set wb = Excel.ActiveWorkbook
Set ht = wb.Worksheets("Hilfstabelle")
Call Menu_Delete
Set MB = CommandBars.ActiveMenuBar
Set Menu = MB.Controls.Add(Type:=msoControlPopup, temporary:=False)
Menu.Caption = Menu_name
End Sub
Sub Menu_Delete()
On Error Resume Next
CommandBars.ActiveMenuBar.Controls("&adaco: Modify plan").Delete
End Sub
2. VBAProject --> Worksheet
Option Explicit
Private Sub Workbook_Open()
Call Menu_Insert
End Sub
3. VBAProject --> ThisWorkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("&Modify plan").Delete
End Sub
So the goal is to have this menu only in the workbook the code is written, and not in every excel I open.
Any help would be greatly appreciated,
Lionna
I've got the following issue:
I have created a custom menu in one workbook. I'd like to have this menu only in this workbook, but it happens to appear in every Excel file I open.
I thinked I messed up with the code locations. Here is the code for my menu:
1. In VBAProject --> Modules --> Module1
Option Explicit
Const Menu_name = "&Modify plan"
Const Command_SelectLanguage = "&Select language"
Const Command_SelectDuration = "&Select duration"
Sub Auto_Open()
Dim MB As Object
Dim Menu As Object
Dim Command As Object
Dim wb As Workbook
Dim ht As Worksheet
Set wb = Excel.ActiveWorkbook
Set ht = wb.Worksheets("Hilfstabelle")
Call Menu_Delete
Set MB = CommandBars.ActiveMenuBar
Set Menu = MB.Controls.Add(Type:=msoControlPopup, temporary:=False)
Menu.Caption = Menu_name
End Sub
Sub Menu_Delete()
On Error Resume Next
CommandBars.ActiveMenuBar.Controls("&adaco: Modify plan").Delete
End Sub
2. VBAProject --> Worksheet
Option Explicit
Private Sub Workbook_Open()
Call Menu_Insert
End Sub
3. VBAProject --> ThisWorkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("&Modify plan").Delete
End Sub
So the goal is to have this menu only in the workbook the code is written, and not in every excel I open.
Any help would be greatly appreciated,
Lionna