I recently re-vamped/re-designed the cash-counting workbook we use at work. As part of my re-design, I added several macros to make things easier. Everything seemed fine (aside from a few easily corrected glitches) until I moved the macros to a manually-created custom menu on the toolbar after Help.

Every day, we save our master copy of the workbook as a new workbook based on the date, say 05.29.04. Everything is fine until the next day. When the master is next opened, we enable macros for the master.xls, but when we try to use a macro from the custom menu (either on the master or after the workbook is "saved as" that day's date), the previous day's workbook opens, and we are once again promted to enable macros.

Once the macros in the menus stopped without opening the previous day's workbook, I checked to see that the macro assigned to the menu item was still correct by going into Customize mode, clicking my way through the menu system, right-clicking a particular menu item, and then clicking "Assign Macro..." The macro assigned was "C:\Windows\Desktop\New Cash Handler Excel\05.29.04.xls"!MacroPrintThisSheet. Because it was May 30, using this macro was forcefully opening the May 29 workbook.

So I went back to the master, removed its read-only status, changed all the macros back to their original !MacroThisIsMyMacro, and everything was fine until I used the macros in a different workbook. Once again, the macro names were changed to reflect the dated workbook and would force the opening of previous workbooks. Strangely, or perhaps not so strangely, this did not happen with my macros that were assigned to buttons on the worksheet.

I've tried to create a menu with VBA just to see if that would be successful, and while adding a menu item to the tools menu was, adding a new menu altogether (which I need) was not. Here's the code I used: 

Sub AddNewMenuItem() 
Dim CmdBar As CommandBar 
Dim CmdBarMenu As CommandBarControl 
Dim CmdBarMenuItem As CommandBarControl 
Set CmdBarMenu = CmdBar.Controls.Add 
.Caption = "New Menu" 
End With 
    Set CmdBarMenuItem = CmdBarMenu.Controls.Add 
    .Caption = "New Item" 
    .OnAction = "'" & ThisWorkbook.Name & "'!MyMacro" 
    .Tag = SomeString 
End With 
End Sub
Any ideas? How can I keep Excel from forcefully opening previous days' workbooks? Thanks in advance.