Consulting

Results 1 to 4 of 4

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

  1. #1
    VBAX Regular
    Joined
    Dec 2011
    Posts
    58
    Location

    Issue with custom Menu - how to have it only in one Excel file?

    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
    [vba]
    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

    [/vba]
    2. VBAProject --> Worksheet
    [vba]
    Option Explicit

    Private Sub Workbook_Open()
    Call Menu_Insert
    End Sub
    [/vba]
    3. VBAProject --> ThisWorkbook
    [vba]
    Private Sub Workbook_BeforeClose(Cancel As Boolean)

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

    End Sub
    [/vba]
    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Create and delete it in the workbook activate and deactivate events.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Dec 2011
    Posts
    58
    Location
    Hi xld,

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

    VBAProject --> Worksheet
    [VBA]
    Option Explicit

    Private Sub Workbook_Activate()

    Call Menu_Insert

    End Sub
    [/VBA]

    VBAProject --> ThisWorkbook
    [VBA]
    Private Sub Workbook_Deactivate()

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

    End Sub
    [/VBA]

    but no result...

  4. #4
    VBAX Regular
    Joined
    Dec 2011
    Posts
    58
    Location
    I got another idea: I changed the sub Before Close to:

    [VBA]
    Private Sub Workbook_BeforeClose(Cancel As Boolean)

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

    Call Menu_Delete

    End Sub
    [/VBA]

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •