ThisWorkbook Macros showing up in External Excel Spreadsheets

[lnagel]

I think I have an idea on what the issue is - If someone could verify - Much Appreciated as I dont want to break anything by changing stuff that I'm not sure on.

I have created quite a bit of code for a Project using the default Work Book Name of "ThisWorkbook"

The issue I am am seeing is that when I have both a Project Workbook open and a completely separate External Workbook open - I am seeing my Project code show up in the non related External Workbook.

This is the code in question :

Private Sub Workbook_Open()
Dim mymenubar As CommandBar
Dim newmenu As CommandBarPopup
Dim ctrl1, ctrl2, ctrl3, ctrl4, ctrl5 As CommandBarButton
   
Set mymenubar = Application.CommandBars("Worksheet menu Bar")
Set newmenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True, before:=2)
newmenu.caption = "SetBilder-1955 Topps"
mymenubar.Visible = True
' Main Menu Options______________________________________________________________________________________________
Set ctrl1 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl1
        .caption = "Save This Set As...."
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!saveas"
    End With
Set ctrl2 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl2
        .caption = "Print Set - All Values"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!printall"
    End With
Set ctrl3 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl3
        .caption = "Print Set - No Cost"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!printnocost"
    End With
Set ctrl4 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl4
        .caption = "Import - Update Price Guide"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!importprice"
    End With
Set ctrl5 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
     With ctrl5
        .caption = "Import - Update Setbilder Version"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!importset"
    End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
On Error Resume Next
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("SetBuilder")
CmdBarMenu.Delete
Set CmdBarMenu = CmdBar.Controls("SetBilder")
CmdBarMenu.Delete
End Sub

When I have both a Project Workbook open and an External Wookbook open - I am seeing the Custom Menu (code above) show up in the External Workbook also.

I suspect that I either need to (either now OR should have done in the beginning of the project) Change the Default Name in Workbook Properties to something unique.

If you can verify that this is correct - would you know whether or not I will break things by changing it now.

Many thanks for any/all suggestions on this.

 

[JLGWhiz]

the vb editor opens in the Excel application window and not the specific workbook window. If you note in the upper left portion of the editor window there is a small pane with 'VBA Project' which will then list your open workbook names. Each Workbook is considered a project and under the workbook will be any objects that might contain code such as ThisWorkbook, sheets, userforms and modules. The class modules, if any, will be named and the public modules may be named or numbered subject to user discretion. So you can see the code from any open workbook when the editor is open, but you need to look at the project (workbook) name that is selected to determine where the code is actually residing. You can double click on any of the items in the project window to change the code pane to that item and view the code in that particular code module. It will not change your code to navigate the different items in the project pane, it is just toggling from one code module to another. Unless you change the actual code and save the file, you will not harm any existing code.

 

[lnagel]

JLGwhiz - So I'm still not clear on how to proceed in order to resolve the issue? Sorry - have read your response a couple of times but its still not clear. Would it help to know that in the Project that I am having issues with I have Sheet1(sheetname), ThisWorkbook, and Module1?

 

[JLGWhiz]

The vb editor allows you to view ALL code for ALL open workbooks. But the code which is visible in the code pane only applies to the item identitified in the project pane with a highlight (or shaded). Refer to the project pane to determine which workbook that the code you see in th editor is actually holds the code. The editor is not tied to one specific workbook if more than one is open.

In the project pane you will see something like this.
VBA Project(myWorkbook.xlsm)
Module1
Sheet1
Sheet2
Sheet3
ThisWorkbook
VBA Project(myOtherWorkbook.xlsx)
Sheet1
Sheet2
ThisWorkbook

One of these items will be either shaded or highlighted, and that is the one that the code that you see will apply to.

You do not need to change your code.

 

[lnagel]

When I look at the Projects Pane (2 separate unrelated projects 1 with code and the other without code) - the unrelated Workbook shows no code. Yet when I have both spreadsheets open, at the same time, the custom Addins Menu appears in both toolbars. This happens on every spreadsheet that I open - If I also have the spreadsheet with the "Addins" code open.

[/URL]*

[/URL]*

 

[lnagel]

Also - If I do not have the Workbook with the custom Addins Menu open - the all other unrelated spreadsheets are fine

 

[JLGWhiz]

Also - If I do not have the Workbook with the custom Addins Menu open - the all other unrelated spreadsheets are fine

I guess I just don't understqand the problem, sorry.
Regards, JLG

 

[lnagel]

I guess I just don't understqand the problem, sorry.
Regards, JLG

The problem is that coding that I have created for a particular project (ie the addins menu) is showing up in totally unrelated spreadsheets that actually have no coding associated with them.

 

[RoryA]

You are modifying the application's menu system, which will apply to all workbooks. Assuming you are using a version more recent than 2003, you should really be using the CustomUI part of the workbook to make Ribbon changes, as that will only apply to the specific workbook.

 

[lnagel]

AHH - Thank you - That makes sense..... Not familiar with Custom UI - Any pointers on getting started with that?