Macros showing up in wrong WB

[mikecox39]

I have 2 workbooks, Personal and Office and they are not interchangeable. I can't seem to isolate the macro I created for each of them.


Each unique macros should only be accessible from it's relevant WB, but I'm doing something wrong because this is what I have this: https://app.box.com/s/z5z0rcp4xgd4vwfe1wgn

Also, I have lost macro code, I created a macro, renamed it but later, when I opened the WP found the macro name but an empty code pane. There doesn't appear to be a save option, I decided to click the save (disk) icon on the toolbar.

 

[My Aswer Is This]

I assume you do know some Macros are stored in Workbook Modules.
And some Macros are stored in Workbook "Sheets"

For example if you create a macro in a ActiveX control the Macro is stored in that sheet.
But if you created a Macro using Vba Editor the Macro will be stored in a Workbook Module.
If you created the macro in a Activex control to see the macro you need to right click on sheet tab then select view code. But if you create a macro using Vba editor you have to open Vba editor and then look in a module.

I have on occasion created a Macro accidently in the wrong workbook when I have more then one workbook open. If I were to do that and then not save the proper workbook I would loose my Macro.

If you delete a sheet in your workbook that has Activex macros those macros will be deleted.

 

[jkpieterse]

Macros are stored workbooks and if you edit a macro, the workbook containing the macro must be saved.
If the active workbook is NOT the workbook containing the macro, you won't see the macro in the list. You also wont see any macros in modules which contain the line "Option Private Module".

 

[mikecox39]

I'm not familiar with the ActiveX control options, as they apply to XL macros, yet, but I'm sure it will come up somewhere soon in VBA AND MACROS, but I've only gotten to Chap 2 /-:

So my macros are being created into VBA editor, with "this workbook" selected, which I thought meant the macro would only show up and be accessible the workbook where I created it.

 

[mikecox39]

Thanks for your response.

Let me rephrase the question.

I have two macros, with unique names, in two different WBs but neither of these names appears in the Alt 8 Macro name: field and I don't have a clue why this is.


When I open my Personal.xlms WB and press Alt F8 the only Macro name: listed is "BackupBudget", it remains the only name regardless of which option I select in the Macros in: field, which includes the name of the "Personal.xlms" WB.


But when I go to Alt F11 the name of the macro in the "Personal.xlsm" project is "BU2_LTorDT".

1. Why is the name in the Alt F8 list different from the name in the Editor?

When I open my Office.xlms WB and press Alt F8 there are two listings: "BackupBudget" and "Sheet5.BackupBudget". Those names remain regardless of which option I select in the Macros in: field, which includes the name of the "Office.xlms" WB.


But when I go to Alt F11 the name of the macro in the "Office.xlsm" project is "Save2HD".

1. Why is the name in the Alt F8 list different from the name in the Editor?

 

[jkpieterse]

I think you are confusing some things here.

If you look at the project explorer in the VBA editor, each workbook that is open has its own "VBAProject". You recognise them because the filename is shown next to each project (between brackets).
So Personal.xlsb shows up as VBAProject (Personal.xlsb). If you expand the branch of a project, you should normally see various categories.
One is always there: "Microsoft Excel Objects". This branch contains children. One for the entire workbook, called "ThisWorkbook" and one for each worksheet, showing up with the names of the worksheets between brackets.
As soon as you record a macro, a new category is added called Modules. And in the newly inserted Module, your macros are written. So macros you want to call from for example a button on the QAT or from alt+F8 are supposed to go in one or more Modules.
Since you can have more than one module you will have to look in all modules if you want to find a macro. SO double-click on each module and scroll through its content to find macros. Macros start with the word "Sub" followed by the name of the macro.

Macros in modules will normally show up in the alt+F8 dialog with just their routine name. Only if there is another sub with the same name will Excel prepend the name with the name of the module in which the sub resides. This is why you get both "BackupBudget" and "Sheet5.BackupBudget". Removing (or moving) the one from the Sheet5 module to a normal module should resolve that.

 

[mikecox39]

Sorry for the delay, I wanted to wait to respond after I reread your post, with my workbooks open; one at a time, to make certain I understood it all.


I clearly understand the first 2 paragraphs.


Starting in the last paragraph, Alt-F8 does list, by name, the macro in my Personl.xlsb workbook. That macro is also listed in the Customize the QAT list from "Macros".


However, when I open my Office.xlsb workbook and check the Module in VBA Explorer I discovered that the macro Mod is named "Save2HD' but the macro name is "BackupBudget". I changed the name to match the macro's name. But that didn't correct the confusion. I'm guessing it makes no difference what I name the child, as it were.


There is only 1 Mod in my Office workbook, that contains 1 Sub, but in the Alt-F8 dialog box there are two listings: "Sheet5.BackupBudget and "Office.xlsm"!BackupBudger.BackiupBudget". There is no "BackupBudget" macro listed.


I just realized that there was actually a macro in sheet 5; which has no children, though I have no idea how it got there and no idea that is was possible to do. I'm sure I will learn more about putting macros in worksheets once I get past Chapter 2! But I have deleted it and now the only listing is: "Sheet5.BackupBudget and "Office.xlsm"!BackupBudger.BackiupBudget".


Why is my "BackupBudget" macro not listed in this workbook, like it is in my Personal workbook? Could there a conflict between by two workbooks, that contain a macro with the same name?

 

[jkpieterse]

The fact that it still shows Sheet5.BackupBudget means that you didn't actually get rid of the sub in Sheet5. Open the VBA editor again and remove it (double-click on Sheet5, select all code between Sub BackupBudget up to and including its "End Sub" and press delete). Now go back to Excel and press alt+F8.

 

[mikecox39]

That macro is no longer listed in my work file so I guess I did get rid of it.


However, there are 2 macros listed in my Work workbook, with one sub.

1. BackupBudget2 and
2. 'Master Budger....xlsm'!BackupBudget.BackupBudget

But there is only one macro, "BackupBudget1", listed in the Module of my personlal workbook, with only one Sub


The only thing that is different in that the Sub is called "BackupBudget" but the name in the Modules folder is "BU2_LTorDT" and the "(Name)" in the Properties window is that same. Can that create issues?

 

[jkpieterse]

Can you please upload that file somewhere and link to it here? You can remove all content from the front (the worksheets) as we only need the VBA to look at.