PDA

View Full Version : Issue with custom Menu - how to have it only in one Excel file?



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

Bob Phillips
01-30-2013, 02:24 AM
Create and delete it in the workbook activate and deactivate events.

lionne
01-30-2013, 03:08 AM
Hi xld,

thanks for the ultra-fast reply. However, it didn't work. I changed to:

VBAProject --> Worksheet

Option Explicit

Private Sub Workbook_Activate()

Call Menu_Insert

End Sub


VBAProject --> ThisWorkbook

Private Sub Workbook_Deactivate()

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Modify plan").Delete

End Sub


but no result...

lionne
01-30-2013, 03:15 AM
I got another idea: I changed the sub Before Close to:


Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Modify plan").Delete

Call Menu_Delete

End Sub


and it did the magic. This doesn't seem to me to be a perfect solution, though...