View Full Version : Advice: Custom Menu Woes

06-10-2004, 12:13 PM
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.

Anne Troy
06-10-2004, 12:20 PM

See what the VBA tags do to your code? Cool, huh?
See: www.vbaexpress.com/vbatags.htm (http://www.vbaexpress.com/vbatags.htm) for instructions.

And, right off the bat...
I see two "end with"s and no "with"s?

Zack Barresse
06-10-2004, 12:45 PM
Hello kreeves,

This is a native functionality in Excel. If you notice something, your macros should also copy over (if you're doing a SaveAs type command in your macro) all macros, to be readily accessible in your new book. BUT, your buttons/objects are still linked to their previous macros. One way (probably a better way out there, but an option) would be to re-assign the macros every time the book is saved new.

An example may look like this:

Sub trnsfrMcrs()
ActiveSheet.Shapes("Button 1").Select 'Whatever yours is
Selection.OnAction = "blahblah" 'This is the macro name, copied over
'from the original sheet
End Sub

Sub blahblah() 'just an oddball testing macro
Dim x As String
Dim y As Integer
x = "Hello!"
y = 0
MsgBox x
x = x & " Again!" 'We really want them to get it... ;)
y = y + 1
Loop Until y = 3
MsgBox ":)" 'Just a test :)
End Sub

Sub saveNew() 'testing the addition of a date
ActiveWorkbook.SaveAs Filename:="blah " & _
Format(Date, "mm.dd.yyyy") & ".xls"
End Sub

The first macro will change the 'Assigned Macro' to that specific shape. The rest were just for testing purposes.

Does this help any?

Anne Troy
06-11-2004, 10:15 AM
There was mention of this thread being cross-posted at MrExcel. Because we are VBA only, and because there was no one working toward a solution over at MrExcel, we don't consider this to be a cross-post.

06-11-2004, 10:28 AM
Ok, just to clear some things up:

This was also posted at MrExcel, and the only response it had was from me.

I simply suggested he have a look at J-Walk's menu maker ( http://j-walk.com/ss/excel/tips/tip53.htm ).

This morning, I had a PM from kreeves @ MrExcel stating that he went with the Menu Maker route.

06-11-2004, 10:54 AM
Thank you everyone for your help. I did end up going with the menumaker route simply because it seemed to be the easiest (both for me and anyone who might tackle modifying the workbook after I am gone), mostly because it was largely a copy and paste routine. However, firefytr's technique printed out and filed at work, just in case the menumaker fails me. Again, thank you all for your quick help. I was really stressing out about trying to get the workbook working again since it is used by everyone in the management team, and now, thanks to you all, I can rest easy.


Anne Troy
06-11-2004, 10:59 AM
Okay. I changed our title here to have "Advice:"

That way, we know it's resolved, but the answer isn't really right here in the posts. :)
Thanks for clarification, Kev!